Error connecting PowerBI with Azure SQL Database with Azure Active Directory

[rodneyjoyce]

I have an Azure SQL Database that has Azure AD Authentication set to true and an Azure AD user is the owner of the database. I have added another Azure AD user as a login to the database. I have an Azure VM running SQL Server Management Studio and Power BI Desktop. I want to connect SSMS and PowerBI to the SQL Azure Database with the Azure AD user.

I can connect SSMS to the database with my AD user and see the tables that only the Azure AD user has access to. However, when I try to connect PowerBI it asks me to sign in (Which I do so correctly, so it is not an issue with the AD Authentication) and then when I click on the "Connect button" it says "We couldn't authenticate with the credentials provided". I have checked the Windows event logs and there are no errors.

If I change to the SQL Login instead of AAD then I can connect to the database, so I believe that rules out the SQL firewall. To be safe, I added the IP address to the SQL server firewall and set "Allow access to Azure services" to true.

How can I debug this issue further to find out why other clients can log in to SQL with Azure AD but not Power BI Desktop?

https://screencast.com/t/JI2lmCquVD

Note: The AD Users are AD B2B Guest users in the Active Directory. As mentioned, this works fine for SSMS.

I am on Power BI Desktop Version: 2.59.5135.421 64-bit (June 2018)

[v-yuezhe-msft]

@rodneyjoyce,

I am unable to reproduce this issue in the latest version of Power BI Desktop(2.59.5135.601), connection works well when I use external AD user to connect to the Azure SQL database.

In my scenario, I add the external AD user as guest user in my domain following the guide in this article. Then I set  Azure AD Authentication for my Azure SQL Server, the external AD user as server admin of my Azure SQL server.


Could you please use latest version of Power BI Desktop and check the connection? Also install Power BI desktop into the same machine that installing SSMS, then check the connection.



Regards,

Lydia

[rodneyjoyce]

Hi Lydia,

Thanks for the reply. I am using the latest version of all tools. PowerBI Desktop IS on the same machines as SSIS.

I'll start from scratch and try it all again if it works for you, thanks.

[tonmcg]

@rodneyjoyceHow did you fix this issue? I'm having the same issue and can't find a resolution. I'm grateful for any insight you can offer.