Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Do57792
Helper I
Helper I

certificate chain was issued by an authority / PBI_SQL_TRUSTED_SERVERS

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.

1 ACCEPTED SOLUTION
Do57792
Helper I
Helper I

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_0-1713164571141.png

 

View solution in original post

10 REPLIES 10
brunoxlipe
New Member

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:

brunoxlipe_1-1718643016399.png

 

 

 

Do57792
Helper I
Helper I

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_0-1713164571141.png

 

Do57792
Helper I
Helper I

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


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

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

yuzhang
Frequent Visitor

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

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
Helper I
Helper I

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

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

vjingzhanmsft_0-1710236654537.png

 

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

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

lbendlin
Super User
Super User

 

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

lbendlin_0-1710190510280.png

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.