Forum Discussion
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.
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.
8 Replies
Hi mmace1 ,
Please check if you ensure firewall rules for the database are configured to "Allow access to Azure services".
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.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?)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.
