We couldn’t connect to the Analysis Services server

Power BI Reports Fails Using Direct Query Mode In PBIRS: If you old or new build of report server we can see this issue when using DQ in any of the pbix report.

An unexpected error occurred
An unexpected error occurred. Please try again later.
ErrorDialogHelperText
We couldn't connect to the Analysis Services server. Make sure you've entered the connection string correctly:
Request ID: 34a4H35-9a8U-b9c1-c5423-88eb07asfa55332
Time: Tue Jun 11 2024 00:50:35 GMT+0530 (India Standard Time)
Service version: /powerbi/libs

Summary

One of my users reported that they are unable to run PBIX report using direct query in the May 2024 PBIRS. This issue started after upgrading to the May 2024, before that it was working as expected.

Error from CX statement

We get the following error: “An unexpected error occurred. Please try again later.
ErrorDialogHelperText

We couldn't connect to the Analysis Services server. Make sure you've entered the connection string correctly.
Request ID:4e9097bc-9a53-afaa-8976-9f45dbad46db
TimeFri Jun 07 2024 15:59:06 GMT-0500 (Central Daylight Time)
Service version/powerbi/libs"

Repro

1. Started with May 2023 PBIRS:
2. PBIX report using SQL server as data source direct query.
3. It was working as expected no issue.

Later when We Upgraded to the latest version May 2024 PBIRS.
Not working, but data source test connection is successful and also you can see the problem below.

Issue with Both existing reports and new reports with Direct query.

Error

An unexpected error occurred
An unexpected error occurred. Please try again later.
ErrorDialogHelperText
We couldn't connect to the Analysis Services server. Make sure you've entered the connection string correctly.:
Request ID: 34af7f05-9a9d-b9c1-c739-88eb07e6f682
Time: Tue Jun 11 2024 00:24:35 GMT+0530 (India Standard Time)
Service version: /powerbi/libs

If we edit in the power bi desktop it is working. Only in the PBIRS it’s not working. The Import mode is not impacted, it works as expected.

Fix?

The issue with the latest version of the Power BI report server May 2024, it’s a security change for the Power BI direct query. Please find the information below: https://learn.microsoft.com/en-us/power-bi/report-server/changelog#may-2024

Action plan for the security change is to add the SQL servers in the Environmental variable. Please check the below link for better understanding: https://learn.microsoft.com/en-us/power-query/connectors/sql-server#sql-server-certificate-isnt-trusted-on-the-client-power-bi-desktop-or-on-premises-data-gateway

I tested this action plan, and it is working as expected. In the environmental variable create a new variable PBI_SQL_TRUSTED_SERVERS and in the value add the SQL server name or Machine name with wildcard entry (*).

Note: Kindly make the changes in the Power Bi report server machine. Hope this article was helpful in resolving the issue. Please keep your inputs and feedback.

Please add the variables in both System and Administrator, same as the image. In the above image, I have used my machine name with wildcard entry.

Then restart the machine and this will probably resolve the issue and work as expected.

TSG Links:

1. Kerberos Issue: NT AuthorityAnonymous Login Failed -Resolve (kingfishertechtips.in)
2. Port Number For Analysis Services – Find Ports For SSAS? (kingfishertechtips.in)
3. RS.exe Utility: Migrate RDL Reports To Another SSRS/PBIRS (kingfishertechtips.in)
4. Direct Query Mode ON In Tabular Model Issue – Visual Studio (kingfishertechtips.in)
5. SMTP Server In SSRS/PBIRS |Subscribe To Email Problem/Issue (kingfishertechtips.in)
6. SQL Server 2022 Download, Install, Connect Using SSMS Tool (kingfishertechtips.in)
7. Creating Tabular Project & Deploy Model To Analysis Service (kingfishertechtips.in)
8. Deploy Tabular Project To Azure Analysis Service – SSDT Tool (kingfishertechtips.in)
9. SSRS/PBIRS – Install And Configure The Report Server On-Prem (kingfishertechtips.in)
10. Could Not Load Folder Contents In Portal URLs – SSRSPBIRS (kingfishertechtips.in)
11. SSRS/PBIRS – Install And Configure The Report Server On-Prem (kingfishertechtips.in)
12. SQL Server 2022 Download, Install, Connect Using SSMS Tool (kingfishertechtips.in)
13. Capture Fiddler Trace for Debugging the Network Traffic (kingfishertechtips.in)
14. On-Premises Gateway: Configure/Map SQL DB To Power Bi (kingfishertechtips.in)
15. Fixed Column/Row Header Make Visible In Any Report (kingfishertechtips.in)
16. Self-Signed Certificate: Configure SSRS/PBIRS With SSL (kingfishertechtips.in)
17. Create Local User Account/Local Admin User In Windows 10/11 (kingfishertechtips.in)
18. Login With AAD/Domain/Microsoft Account To Azure VM’s/RDP (kingfishertechtips.in)