Cannot connect to SQL Data Warehouse database using contained database users

[AdamOrmond]

I recently created a SQL Data Warehouse instance in my Azure subscription and granted an Azure Active DIrectory group access to connect and read all tables within the warehouse. I verified the configuration by connecting and browsing the data warehouse database using SQL Server Management Studio with a user in the AAD group. However, I am unable to connect to the same data source using Power BI Desktop with that same user. I am able to using PBI Desktop to connect to the database server with the AAD administrator.

How do I resolve this issue? What am I doing wrong?

[v-danhe-msft]

Hi @AdamOrmond,

Based on my research, you could refer to below link that may solve your problem:

https://community.powerbi.com/t5/Integrations-with-Files-and/Connaot-connect-to-Azure-SQL-Data-Wareh...

Regards,

Daniel He

[AdamOrmond]

Hi @v-danhe-msft,

I reviewed the linked community post and the issue described there appears similar to the one I am having. However, I do not have an issue connecting to Azure SQL Data Warehouse via SQL Authentication or the Azure Active Directory (AAD) administrator account configured for the Azure SQL Server hosting the Azure SQL Data Warehouse. I am able to use both of these connection mechanisms in SSMS and Power BI Desktop.

What I cannot do is use a Contained Database User to authenticate with Power BI Desktop. There are no proxies in the way, nor do I believe there is a firewall rule preventing this interaction. I am performing all of this work in an Azure-hosted Windows 10 Virtual Machine. Below, in red, is the error I receive when I connect to the server and specify the database in Power BI:

"Unable to connect

Details: "Microsoft SQL: Cannot open server "<NOT THE SERVER I REQUESTED>" requested by the login. The login failed"

I do not understand where Power BI Desktop is finding the server name to pass. The name that shows up in the above error message is not the server name I specified in the preceding connection dialog. If I use a AAD Administrator account and specify the database, I get the same error.

If I use an AAD Administrator account and DO NOT specify the database, it works. If I attempt to connect using the database contained user and likewise DO NOT specify the database, it appears to work, but the Navigator dialog in Power BI shows the below error, in red, for the database in the navigation tree:

"DataSource.Error: Microsoft SQL: Cannot open server "<NOT THE SERVER I REQUESTED>" requested by the login. The login failed.
Details:
DataSourceKind=SQL
DataSourcePath=dsphase2poc.database.windows.net;AdvWorks
Message=Cannot open server "<NOT THE SERVER I REQUESTED>" requested by the login. The login failed.
Number=40532
Class=20"

The linked community post does not appear to have identified a solution for the original poster. @niko159 appears to still have the issue, or at least did as of 4/11/2018 when they last posted.