Forum Discussion

jdunmall's avatar
jdunmall
Advocate I
9 years ago
Solved

SQL Azure Import Data Refresh

Hi all,   I am trying to setup automatic refresh connecting to SQL Azure DB. The online service is prompting me to install a personal gateway. If I switch to DirectQuery, I am prompted for credenti...
  • jdunmall's avatar
    jdunmall
    9 years ago

    I finally figured it out: the connection FQDN included the word "secure".

     

    This won't allow server-side refresh: <database name>.database.secure.windows.net.

    This will allow servers-side refresh: <database name>.database.windows.net

     

    Both FQDNs seem to work otherwise. The 'secure' setting was used to enable auditing, but I don't think it is required anymore.

     

    Problem solved. Thanks everyone.

     

    -Jeff

jdunmall's avatar
jdunmall
Advocate I
9 years ago

Hi Lydia,

 

Yes - exactly that. What a mystery! Perhaps I should try a different machine?

Version: 2.40.4554.463 64-bit (October, 2016)

 

Here is my data source settings in the same nearly empty PBIX file I gave you the source for earlier, which looks just like yours.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi jdunmall,


Would you please install personal gateway in other machine and test the scenario?

Thanks,
Lydia Zhang

  • jdunmall's avatar
    jdunmall
    Advocate I
    9 years ago

    I finally figured it out: the connection FQDN included the word "secure".

     

    This won't allow server-side refresh: <database name>.database.secure.windows.net.

    This will allow servers-side refresh: <database name>.database.windows.net

     

    Both FQDNs seem to work otherwise. The 'secure' setting was used to enable auditing, but I don't think it is required anymore.

     

    Problem solved. Thanks everyone.

     

    -Jeff

  • jdunmall's avatar
    jdunmall
    Advocate I
    9 years ago

    Hi Lydia, I can confirm that the personal gateway works. But I cannot as your screenshot indicates. I am going to try a brand clean install on another machine, and connect to Azure SQL with a minimal PBIX file.

     

    This is what I see with the personal gateway installed and working:

     

  • jdunmall's avatar
    jdunmall
    Advocate I
    9 years ago

    I've tested the scenario on a new machine with a fresh copy of PowerBI and see the same result: the only way I can connect to SQL Azure is via a personal gateway. The only variable I haven't tested is the instance of SQL Azure.

     

    Ah!

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi jdunmall,


    Based on your desktop,  you combine Azure SQL database data source and web data source in a dataset, when you remove the data source from the dataset, do you require gateway? In addition, I don't use Windows credential, I use database credential.

    Thanks,
    Lydia Zhang

  • jdunmall's avatar
    jdunmall
    Advocate I
    9 years ago

    Yes Lydia, I've tried a basic PBIX with only one Azure SQL database connection. Same problem.

     

    I have since gone through the process of creating a brand new Azure SQL database, and it works! I can refresh directly through PowerBI. Now I need to track down why it doesn't work with the "real" Azure SQL DB. But some progress.

     

    I'll post back when I work out what is special about the one that isn't working.

     

    Thank you again for your help.

  • jdunmall's avatar
    jdunmall
    Advocate I
    9 years ago

    Update: I have confirmed Power BI requires a personal gateway in our main azure subscription with a brand new SQL database and test database. In a separate test subscription with exactly the same Azure SQL setup, a personal gateway is not required.

     

    There is something specific about our subscription that is causing this problem. I'm at the end of my ideas to troubleshoot, and so we are opening a Microsoft ticket.