Issues regarding power bi report server

[flem81]

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
january	1	1
february	1	2
March	1	3
April	1	4
May	1	5
June	1	6

Am I doing it wrong?

[Petebro · Accepted solution]

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.

[stanley_1913]

I want to say a big thank you to you. After struggling for two days to solve the problem, I finally solved it with your post.

[Yanhw]

I set the environment variable PBI_SQL_TRUSTED_SERVERS server name on the server side, but it still doesn't work, and I don't know what's wrong with it,

[ManuelEscanez]

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

[Yanhw]

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

[Yanhw]

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]

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."

[SSMan]

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

[Yanhw]

In the environment variables, add PBI_SQL_TRUSTED_SERVERS    Localhost

[dy059606]

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

[ManuelEscanez]

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]

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]

@Petebro 

Please check this issue.....

DirectQuery reports do not work with the new version.

Something to do with database connection.

Thanks

[ManuelEscanez]

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????

[ManuelEscanez]

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....