cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

Connecting to Azure SQL Database via Dataflows - authentication not working.

We have an Azure SQL Database to connect to. 

 

  • I can connect via SQL Server Management Studio fine:  Authentication = Azure Active Directory -Universal with MFA
  • I can connect via Power BI Desktop fine:  Authentication = Microsoft Account
  • I can't connect when using Dataflows.  It gives 2 authentication options. Neither work. 
    • Basic - returns Invalid credentials. 
    • Or, if I select our local Gateway - Windows - also returns Invalid credentials. 

 

Any insight? 

 

I think, maybe I need a Microsoft Account option for authentication (but that doesn't seem to be available in Dataflows?) or to setup some junk-level non-MFA login in the Azure SQL Database itself?  Not really sure. 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Post Patron
Post Patron

Re: Connecting to Azure SQL Database via Dataflows - authentication not working.

Go it, so the issue was: 

 

I was using my own login for authentication in Dataflows - but, my login used Azure Active Directory for authenticaion, and Azure SQL Database doesn't support Azure Active Directory authentication. 


Solution:  Setup a new SQL Server user in Azure SQL Database, and connect via that login, using the 'Basic' Authentication option in Power Query. 

 

 

View solution in original post

8 REPLIES 8
Highlighted
Community Support
Community Support

Re: Connecting to Azure SQL Database via Dataflows - authentication not working.

Hi @mmace1 ,

 

Please check if you ensure firewall rules for the database are configured to "Allow access to Azure services".

manage-server-firewall.png

 

 

Best Regards,
Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Post Patron
Post Patron

Re: Connecting to Azure SQL Database via Dataflows - authentication not working.

Thanks.  We did have [Allow Access to Azure Services]  on already.

Do we need to specify the IP range that Dataflows uses?  (Though isn't it completley dynamic / unpredictable?)

 

Untitled.png

 

 

Highlighted
Community Support
Community Support

Re: Connecting to Azure SQL Database via Dataflows - authentication not working.

Hi @mmace1 ,

 

Just try it. You can refer to this document: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-server-level-firewall-rule.

 

 

Best Regards,
Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Post Patron
Post Patron

Re: Connecting to Azure SQL Database via Dataflows - authentication not working.

Try what, adding the Dataflows IP address as a server-level firewall rule in the Azure SQL Database? 

Isn't the Dataflows IP address dynamic? 

Highlighted
Community Support
Community Support

Re: Connecting to Azure SQL Database via Dataflows - authentication not working.

Hi @mmace1 ,

 

You can try to enable the access of Power BI not dataflow to Azure SQL via whitelisting the IP addresses, for your data region, in your firewall. However, the IP range of Power BI server is updated weekly.  Moreover, it could be different each time within each region. So, you have to monitor and update the IP range on time to ensure Power BI can always fetch data successfully.

 

Reference: https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-communication#ports.

 

If the issue is still there and you are a pro user, I suggest you to create a support ticket to get further help.

 

Support Ticket.gif

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Post Patron
Post Patron

Re: Connecting to Azure SQL Database via Dataflows - authentication not working.

Thanks for confirming the IP addresses vary.  That cofirm it woudln't be (to my way of thinking) a very solid solution then. 

 

It seems as if Azure SQL Database doesn't support Azure Active Directory.  At least, Azure SQL Datawarehouse doesn't, and I'm guessing maybe SQL Database is the same. 

 

https://docs.microsoft.com/en-us/power-bi/service-dataflows-create-use

 

I'm going to try to get someone with the necessary permissions, to setup a SQL Server login, and see if that works for Dataflows.  

 

Highlighted
Community Support
Community Support

Re: Connecting to Azure SQL Database via Dataflows - authentication not working.

Hi @mmace1 ,

 

Oh, I didn't notice this. It turned out that I was in the wrong direction. Anyway, wish you success.

 

 

Best Regards,

Icey

Highlighted
Post Patron
Post Patron

Re: Connecting to Azure SQL Database via Dataflows - authentication not working.

Go it, so the issue was: 

 

I was using my own login for authentication in Dataflows - but, my login used Azure Active Directory for authenticaion, and Azure SQL Database doesn't support Azure Active Directory authentication. 


Solution:  Setup a new SQL Server user in Azure SQL Database, and connect via that login, using the 'Basic' Authentication option in Power Query. 

 

 

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.