certificate chain was issued by an authority / PBI_SQL_TRUSTED_SERVERS

[Do57792]

Hi,

In a SQL query that I have run several times before I am not receiving the following error: 

Message=A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

However, I am still able to access the databases that I receive this error for on PowerBI via SQL Server Management Studio. 

I read all the tips, which are at the forum, but the problem is still alive.

At SSL Provider, error: 0 - Certificate chain issued by an authority that is not trusted : r/PowerBI (r...
Unable to connect to an Azure managed SQL Server instance with Power BI : r/PowerBI (reddit.com)

and directly at Microsoft : Unable to connect to an Azure managed SQL Server instance with Power BI : r/PowerBI (reddit.com)
I found the solution 

"To troubleshoot this error when using Power BI Desktop, modify the value of the environment variable PBI_SQL_TRUSTED_SERVERS to include the SQL Servers. The supported values are the same as outlined for gateway configuration (as described in step 4 above)."

but it is absolutely unclear what to do, because I don't have a local gateway where I have to change the entry. So I have to modify an "environment variable". Where at Power Bi could I find this that I can modyfi the PBI_SQL_TRUSTED_SERVERS?

Has someone an example for me or would it explain for a dummy please?

Many thanks in advance. I am searching for hours for the solution.

[brunoxlipe]

My solution was to remove Microsoft.Data.SQLClient (Microsoft SQL Client Provider) from the msmdsrv.exe.config settings file and stop-start in Power BI Report Server.
Example of the command removed in msmdsrv.exe.config:

[Do57792 · Accepted solution]

Case Closed. Using another connector helped to set up SQLs/connect to the database. 

Using OLE DB was working well insted of SQL Server, which was working fine before the update.

[Do57792]

I tried the environment variable, but it has not worked.

It is an AWS Database/Server where I want to connect. Some more tips?

[lbendlin]

where are you connecting from, your PC or a gateway?

[yuzhang]

I have same issue with on premise sql server database, we put enviroment variables of sql server domain name, does not work at all

[lbendlin]

Remember that the method differs between desktop and gateway.  On desktop it's the enviroment variable, on gateway it is a config file edit.

[Do57792]

Thank you very much.

Do you or someone else know another solution, because at the PC I don't have the admin rights to change general settings.


I am using Power Bi desktop and the server credentials differ from the windows log in, so I cant change from organizatorial account to windows account. Also the change of settings does not work for me eigther.

Or do somebody know where to write the condition

TrustServerCertificate=TRUE;
because my request is similar to:

Quelle = Sql.Database("Server", "database",

[Query="SELECT top 5 *


 FROM table1"])

and not like #C where I can send some specifications also at the request...

[v-jingzhan-msft]

Hi @Do57792 

This is a new issue that comes with the new version of Power BI Desktop (February 2024). If you are not able to add the environment variable, you can reverse Power BI Desktop back to the December version. That version doesn't have this issue. Download the history version from Previous monthly updates to Power BI Desktop and the Power BI service

In addition, you may try changing the following setting to None. As this is not listed as a workaround for this issue, I'm not sure if it would work or not. You may give it a try. Certificate revocation check, Power BI Desktop - Power BI | Microsoft Learn

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Do57792]

I tried to use that setting, but nothing has changed. That was also described in another forum.

[lbendlin]

Where do you have the issue, on the desktop or the gateway?  On the desktop you need to add an environment variable.