Forum Discussion
Issues regarding power bi report server
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.
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!!!
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.
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,
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.
Hi Petebro
Thank you for this workaround but it means for my organization (we use PBIRS on-premise) that we have to change the “PBI_SQL_TRUSTED_SERVERS” environment variable and reboot the server everytime a new SQL Server is used as a DirectQuery data source of a Power BI Report.
Could you tell me if there is a wildcard value (e.g "*") for this environment variable that means that all the SQLServers are trusted?
Thanx in advance for your answer.
Have an amazing day!
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
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
“As part of a push for security customers will have to explictly include "Encrypt=True;TrustServerCertificate=True" " I've never been able to find the modification, please tell me.
This is done on the connection string inside Power BI Destkop application then republishing it to the Report Server.
Power Query SQL Server connector - Power Query | Microsoft Learn
Hello,
I have just upgraded to the May 2024 server version and effectively all my Direct Query reports on the portal no longer work and receive the same message as you do.
So to solve the problem I followed your recommendations.
PS: I use several SQL Server where I have DB which are datasources of the reports published on the RS portal.
So I configured on the Power BI Server the env variable PBI_SQL_TRUSTED_SERVERS
Connect on server power bi with the system user declared on power bi Serve
Simply set PBI_SQL_TRUSTED_SERVERS = Server1,Server2,Server
Restart power bi and Oh magic all direct query reports work again
I expect this will help you, I've been working on this problem for 1 day.
Fabien
