topic Re: Issues regarding power bi report server in Report Server

Issues regarding power bi report server

flem81 — Mon, 03 Jun 2024 08:13:18 GMT

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?

Re: Issues regarding power bi report server

ManuelEscanez — Mon, 03 Jun 2024 11:10:37 GMT

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

Re: Issues regarding power bi report server

ManuelEscanez — Mon, 03 Jun 2024 14:42:17 GMT

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

Re: Issues regarding power bi report server

ManuelEscanez — Mon, 03 Jun 2024 21:33:18 GMT

@Petebro

Please check this issue.....

DirectQuery reports do not work with the new version.

Something to do with database connection.

Thanks

Re: Issues regarding power bi report server

dy059606 — Tue, 04 Jun 2024 14:16:09 GMT

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.

Re: Issues regarding power bi report server

Petebro — Tue, 04 Jun 2024 16:47:56 GMT

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.

Re: Issues regarding power bi report server

ManuelEscanez — Tue, 04 Jun 2024 17:32:25 GMT

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

Re: Issues regarding power bi report server

Petebro — Tue, 04 Jun 2024 17:40:45 GMT

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

Re: Issues regarding power bi report server

dy059606 — Wed, 05 Jun 2024 08:46:08 GMT

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

Re: Issues regarding power bi report server

ManuelEscanez — Wed, 05 Jun 2024 14:11:29 GMT

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

Re: Issues regarding power bi report server

flem81 — Thu, 06 Jun 2024 06:34:39 GMT

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

Re: Issues regarding power bi report server

flem81 — Thu, 06 Jun 2024 07:29:44 GMT

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:

Re: Issues regarding power bi report server

ManuelEscanez — Thu, 06 Jun 2024 07:33:08 GMT

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

Re: Issues regarding power bi report server

liyongfeng0606 — Fri, 07 Jun 2024 08:57:36 GMT

是否有终极解决方案？
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启用加密证书吗）还是保持现状，不用加密证书？

Re: Issues regarding power bi report server

Yanhw — Fri, 14 Jun 2024 07:54:42 GMT

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,

Re: Issues regarding power bi report server

Yanhw — Fri, 14 Jun 2024 07:58:04 GMT

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

Re: Issues regarding power bi report server

Petebro — Fri, 14 Jun 2024 15:15:08 GMT

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

Re: Issues regarding power bi report server

ManuelEscanez — Fri, 14 Jun 2024 15:57:01 GMT

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

Re: Issues regarding power bi report server

Yanhw — Sat, 15 Jun 2024 05:06:52 GMT

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

Re: Issues regarding power bi report server

Yanhw — Sat, 15 Jun 2024 05:14:51 GMT

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