Unable to connect to SQL Server using Report Server but is able to connect using Desktop

[jkl317]

I have a set of reports which was published to Report Server and was working until recently.

Reports include both Direct Query and Import reports.

It stopped working now. The credentials are working fine if I try Manage > Data sources > Test connection. They work when the report is open in Power BI Desktop as well.

When trying to run the direct query report, I get below error:

We couldn't connect to the Analysis Services server. Make sure you've entered the connection string correctly.

Request ID <>

Time <>

Service version /powerbi/libs

When when scheduled refresh is run for Import reports, I get below error:

SessionID: <>

[0] -1055784932: The credentials provided for the SQL source are invalid. (Source at <server;database>.). The exception was raised by the IDbCommand interface.

[1] -1055129594: The current operation was cancelled because another operation in the transaction failed.

[2] -1055784932: The command has been canceled.. The exception was raised by the IDbCommand interface.

[Anonymous · Accepted solution]

Thank you aduguid

Hi, @jkl317 

Due to the Report server update, we get this error from you when uploading a report using a Directquery connection to the Report server:

Power BI report data sources in Power BI Report Server - Power BI | Microsoft Learn

PBI_SQL_TRUSTED_SERVERS

You can configure this property by following the tutorial below:

Press the Win + R key combination to open the Run dialog box. Enter sysdm.cpl and press Enter:

At this point, we need to add the PBI_SQL_TRUSTED_SERVERS as a user environment variable and a system environment variable:

Then restart your Report server:

After that, you can see your Directquery report normally.

The following case is a case that once discussed this issue:

Solved: Power BI report Server issue with Direct Query to ... - Microsoft Fabric Community

Best Regards

Jianpeng Li

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

[BharatRajpoot]

Thank Youuuuuuuuuuuu,
Bro I am trying to figure it out from last few days,

thank god I found this article...

[aduguid]

There is an issue with direct query in the May 2024 version of the report server.  I'd suggest installing the previous version.