Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
flem81
Frequent Visitor

Issues regarding power bi report server

Hi

We have two issues regarding power bi report server.

1st.

After updating to the latest version (Maj update), our reports using direct query does not work.
Error messages “we could not connect to analysis services..”

Is there any solution to this?

2nd.

This issues is not related to the update.
We are building a paginated report using a sunburst chart with report builder. When connecting to our local power bi report server or uploading the report to the server, the sorting doesn’t seem to be working. It works fine when the report runs in report builder.

E.g.:

MonthName MonthValue Order
january11
february12
March13
April14
May15
June16


Am I doing it wrong?

2 ACCEPTED SOLUTIONS
Petebro
Microsoft Employee
Microsoft Employee

Apologies for this change not being mentioned....I forgot to add this to the changelog and release notes (and will be fixing that today).

 

The May release has a expected change on the AS side of things which will require customer actions to keep some existing scenarios working. As part of a push for security customers will have to explictly include “Encrypt=True;TrustServerCertificate=True” as part of the connection string or apply the “PBI_SQL_TRUSTED_SERVERS” environment variable to their server to Power BI Report Server machine.

View solution in original post

Good afternoon,

 

To make this workaround to be effective it has to be:

 

setx PBI_SQL_TRUSTED_SERVERS [DDBB Server Name]

 

It is not enough to introduce the variable, but also include the server name.

 

Now it works fine!!!

 

 

View solution in original post

32 REPLIES 32

Hi,

The environment variable PBI_SQL_TRUSTED_SERVERS shall be in the PbiRS machine and you have to write the server name where you have the SQLServer Engine running.

 

Perform a "Powerbi Report Server" service restart and should work fine....

 

Rebooting the PbiRS machine won´t do any harm as well.......

 

Good luck

 

 

Thanks, I set the environment variable to PBI_SQL_TRUSTED_SERVERS LocalHost, and after restarting the server, it works successfully.

Capture.PNG

The server is running sql server and powerbi reportserver, set the value server name in the environment variable, but still prompts "We couldn't connect to the Analysis Services server. Make sure you've entered the connection string correctly."

Yanhw
Frequent Visitor

After setting the variables and restarting the server it still prompts  " "We couldn't connect to the Analysis Services server. Make sure you've entered the connection string correctly."

I am in the same boat. Were you eventually able to resolve the issue?

Yanhw
Frequent Visitor

In the environment variables, add PBI_SQL_TRUSTED_SERVERS    Localhost

Thanks for the update, can confirm its now working again following the addition of the env var server side and a quick RS reboot.

 

Handy guide on MS Docs for those using a gateway, we had to open notepad as admin and navigate to the file named in order to make the change. Scroll down to the "Limitations" header for the step by step.

 

Power Query SQL Server connector - Power Query | Microsoft Learn

Good afternoon,

 

I already unistalled the May2024 version and rolled back to Jan24.

 

I had to restore the DDBB, unistall pbirs and install again to recover the Directquery reports that were critical to some users….

 

Meanwhile, if I want to install again the may24 (and I imagine the coming versions), can you confirm to me the workaround in the environment variable??

 

I understood that the variable should be:

 

Variable Name:  PBI_SQL_TRUSTED_SERVERS

Value:  ¿¿BBDD Server Name??

 

 

Do I create this variable under “User Variable” or “System Variable”???

 

Thanks

dy059606
Frequent Visitor

Following this post as we have a load of direct query reports on our server and following an upgrade today none of these reports are working.

 

Yet to see any official response from Microsoft on the matter with a fix.

ManuelEscanez
Frequent Visitor

@Petebro 

Please check this issue.....

 

DirectQuery reports do not work with the new version.

Something to do with database connection.

 

Thanks

ManuelEscanez
Frequent Visitor

Good morning,

I am having the same issue with directQuery models.

 

Some of them are showing that "we could not conect to Analisys Services"

 

Those reports with a landing page show more detail about the error:

 

"A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - La cadena de certificación fue emitida por una entidad en la que no se confía.);La cadena de certificación fue emitida por una entidad en la que no se confía. La interfaz de IDbConnection ha producidola excepción."
 
Any idea how to solve the issue????
 

 

 

 

Going deeper on the problem, it looks that the SERVER SIDE of PBRS is trying to stablish an encrypted connection with the SQLServer which is not configured to work like this.

 

The Directquery reports work fine in PbiDesktop but, when uploaded on the server they won´t load.

 

This issue has been created 100% by the May 2024 update of the PBIRS.

 

It was working fine til last night when I updated the server....

 

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.