Hi there Fabric community,
I'm fairly new to powerBI and PowerBI report server and have a question regarding a change that was made in powerbi may 2024 (it's been a while since that released so sorry if i have to unrust your minds for that). i do see solutions for this issue already but what we are trying to figure out is the root cause/origin of why this change is neccesary.
it's regarding the following change :
- A recent security change requires a privileged user to apply the "PBI_SQL_TRUSTED_SERVERS" environment variable on their Power BI Report Server machine for certain data types. Read more details in the article, SQL Server certificate isn't trusted on the client.
What we are trying to figure out is what the reasoning for this was the issue only came across for us once we implemented PBIRS on our production environments and did not appear in our test environments. Basicly we are wondering if there is an underlying security measure that enforces this.
we arrived on this realisation when the proposed solution that seems to fix it for everyone that encounters this issue with direct query's is this specific change. as seen in the following posts
https://community.fabric.microsoft.com/t5/Report-Server/Issues-regarding-power-bi-report-server/m-p/...
https://community.fabric.microsoft.com/t5/Report-Server/Power-BI-report-Server-issue-with-Direct-Que...
https://community.fabric.microsoft.com/t5/Report-Server/Issues-regarding-power-bi-report-server/m-p/...
and various other locations on the net. We have had trouble with a security plugin that was due to a non-related update in a .DLL for .NET security and that had delayed our release for this and we did not expect to run into this issue with our direct query reports after the upgrade since those functioned properly in all testing environments for reference we have 2 environments replicating a production scenario where SQL and PBIRS is seperated and cloud environments to quickly spin up that have the SQL servers and PBIRS servers on the same virtual machine. so if anyone in your community could enlighten me on this or give me some pointers to where i can find the appropriate information i would be very thankfull.
EDIT: As an update in-case someone stumbles on to this in the future.
The Release notes only talk about the Variable but it's a multifacted problem which is not very clear. From may 2024+ onwards you are required to implement this when you use direct query reports this needs 3 things to work
1. The system variable to be present on the relevant servers
2. .Net Framework 4.8 or higher due to security changes within .Net Framework
3. Forced encryption being on in the SQL servers and the certificates of these servers being configured correctly for this.
This was found out after constant investigation with a representative of the microsoft Power Platform Team. So hopefully it can help someone else if they run into this.
