DIrect Query Report with error when open in Service

[gavin007]

Hi, i encounter issue when open the direct query report in power bi service. 

Data is connected to Azure SQL DB. it was working before. The same dataset with import mode is working fine. 

I tried to find out is it issue with Powerbi or Azure SQL db. the crendential in the connection is working fine without error. 

Login failed for user '<token-identified principal>'.. The exception was raised by the IDbConnection interface.

Please try again later or contact support. If you contact support, please provide these details.

Activity ID: fb69bac0-8030-4267-af2c-53c064de97a0
Request ID: d5d6d38e-1463-42cf-995b-e215ec74e3e6
Correlation ID: 3518d22c-69b4-b43e-4dc5-a669812a94b8
Time: Mon Feb 14 2022 11:32:48 GMT+1100 (Australian Eastern Daylight Time)
Service version: 13.0.17618.45
Client version: 2201.5.09798-train
Cluster URI: https://wabi-australia-east-a-primary-redirect.analysis.windows.net/

[gavin007]

I found out it has something to do with the encrepyted connection in the datasource setting in desktop. Once I turn off the encrypted connection, it works. I just don't understand why it work. 

I did some research and the following doc says the The direct connector for Azure SQL Database in the Power BI service is now deprecated. I am not sure what is this mean. But looks like DQ to Azure SQL DB is required to set up in desktop first. 

https://docs.microsoft.com/en-us/power-bi/connect-data/service-azure-sql-database-with-direct-connec...

[v-zhangti · Accepted solution]

Hi, @gavin007 

For the best experience to connect to your Azure Synapse Analytics data source, use Power BI Desktop. Once you've built your model and report, you can publish it to the Power BI service. The previously available direct connector for Azure Synapse Analytics in the Power BI service is no longer available.

This is the relevant document, hope to help you:

https://docs.microsoft.com/power-bi/connect-data/service-azure-sql-data-warehouse-with-direct-connec... 

Best Regards,

Community Support Team _Charlotte

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