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.

[liyongfeng0606]

20240812 更新：
1.如果Sql server 服务器启用了加密链接，在PQ链接数据源配置为加密；
2.如果Sql server服务器没有启用加密链接，那么按照大佬的建议两条路：
 a.修改链接字符串，添加“Encrypt=True;TrustServerCertificate=True” ，但是这个我尝试了很多方法，比如数据库底层直接修改数据源，PowerShell，前端post强制修改，都无法完成；因此这条路走不通；
--我个人猜测，这个字符串必须针对 sql server启用了加密链接的，按照方案1走，可以自动携带此参数；否则手工无法修改；

b. 添加环境变量的方案（终于走通了）：
需要在用户和系统两个级别添加环境变量：PBI_SQL_TRUSTED_SERVERS；
--用户（不是管理员账号，这个用户有特殊意义），应该为这个界面下对应的账号，所以你需要用这个账号登录Power BI Report server 服务器，然后在这个用户账号下配置环境变量(我这里是:*****e\co_bi)关键信息隐藏了，这是一个域账号；如果你用的是管理员，那就用管理员账号，根据真实情况定；

--系统用户环境变量用具有管理权限的账号配置即可；

配置如下（图仅供参考，我这里用的是管理员账号，但是真实环境请用上一步的用户账号登录进行配置用户变量)

-- 环境变量的值：对应的sql server 数据库的hostname；建议不要用IP；需要和你的报告的数据源配置保持一致吧；
不要这里用hostname，报告源配置为ip；

比如我的

环境变量值为：****-db;
报告数据源也为:****-db;
这里保持一致吧；

好了，这就是配置的关键点：

配置完后，记得重启电脑（服务器）；

然后再尝试访问报告；恭喜你，应该是通了。

如果有问题：
中国朋友请加微信：93636319，联系我。

[kevin_Lin]

我们只在report Server添加了系统的环境变量，现在也可以正常访问数据库了。 感谢

[JiuluSun]

making the system environment change PBI_SQL_TRUSTED_SERVERS=SQLServerName (PBI report data source), but you have to reboot the report server windows box to make it work, I tried many times with only restart powerBI report server service, and it did not work, only the reboot works.

that's my 3 hours struggle on this.

[liyongfeng0606]

是否有终极解决方案？
1.我尝试了添加环境变量（用户级别和系统级别），并重启RS 服务和PBI RS服务器，directquery报表仍然不工作；
2.无法在链接字符串添加加密字符串选项；（PBI desktop打开重新编辑，保留加密选项,保存到rs后没有生效，链接字符串并没有添加Encrypt=True;TrustServerCertificate=True；
尝试用postman，python，powershell强制修改这个字符串并保存，无法提交，报错401；

谁能出一个完整的教程？

我的疑问：

1.我的sql server直连数据源都是没有启用证书加密链接的；
2.数据源没有准备好的情况下，修改PBI 报告的链接字符串为启用加密选项，这样不是冲突了吗？
3.在PBI RS服务器增加环境变量，（前提是SQL SERVER启用加密证书吗）还是保持现状，不用加密证书？

[flem81]

Is there a solution to problem no. 2, the sorting problem?

Sunburst running in report builder:

Sunburst running in report builder but connected to power bi report server:

[Petebro]

I'll confirm the details and update the respected docs but I think any value works (the presense of the environment var is all that it cares about). Running the command from a cmd prompt with admin rights will work:

setx PBI_SQL_TRUSTED_SERVERS 1

[Giuseppe_Aino]

I've solved using a tips from medium.com .

My connection string use:

serverName\Instance,1234

I've used the "*" as a jolly to trust every server... it's not a beatiful idea, especially for safety, but it works .... (for the moment)

So the setup was:

setx PBI_SQL_TRUSTED_SERVERS * /M

[Giuseppe_Aino]

I read all the posts on this thread so I understood that there was a lot of confusion.

To summarize,

• the problem will be resolved using the system variable PBI_SQL_TRUSTED_SERVERS (which can be easily set using SETX command from admin Command Prompt).
• It is not clear if the environment variables must be for user or system level or even both, let's assume both.
• The variable what will contains?
  1. Someone said to simply enter 1
  2. Someone said to simply enter the hostName of the Database server, for more than one servers separating it with a comma. Someone put the host in square brackets [], correct?
  3. At the beginning you wrote that the text "Encrypt=True;TrustServerCertificate=True" had to be inserted and how does it reconcile with the insertion of the host server?
  you did not explain it.

Opened questions:

• In case the database service (the hostName? and if we use a DB instance?) is reached on a non-standard port, how do we use it?

Thank you for your patience and time

PS For enyone that are still asking for, it's not necessary to reboot the server machine but also restart the service.

[ManuelEscanez · Accepted solution]

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

[DougCastell]

Tip: To set the environment variable system-wide, add a /M to the end. 

Solution:
Run administrative command prompt
setx PBI_SQL_TRUSTED_SERVERS [DDBB Server Name] /M

Restart Power BI Report Server service

[flem81]

I added the variable with the power bi services account user and it works fine.

[ManuelEscanez]

Hi,

If you don´t want to decide which user, just add the variable as a System Variable in the PbiRS machine.

I forgot before...

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

[TinyToons]

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!

[Yanhw]

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

[Petebro]

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

[bouchardcote]

I can't find how to achieve this. How can we add "TrustServerCertificate=True;Encrypt=True;" to a Power BI Desktop report in direct query mode? Is it possible to post detailed step by step instructions with screen captures or something ?

The official documentation is saying this is possible, but not how to do it:

Thanks !

[TinyToons]

Hi @Petebro

For me, with Power BI Desktop RS May 2024, and according to the Power Query documentation of the "Sql.Database" function, it is not possible to add the "Encrypt" and the "TrustServerCertificate" options to the connection string.

Could you help me to find tell me how I can do it?

Thanx in advance for your time and your help.

Have an amazing day!

[fabienChery]

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 

[PedramMadah]

Thank you for the picture. We were finally able to resolve the issue by following the provided instructions.

One important point to consider is that this variable must exist in both the user variables and the system variables. However, when using the setx command to add "PBI_SQL_TRUSTED_SERVERS," it only adds it to the user variables, even if the Command Prompt is run as an administrator.

Therefore, you need to manually add the variable to the system variables to fully resolve the issue and ensure all Direct Query dashboards run correctly.

Additionally, if you are using Power BI Report Server (PBIRS), you should also set this variable in the PBIRS host.