Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

craigchamberlai

SQL Connection - "0 - The certificate chain was issued by an authority that is not trusted."

Submitted by Advocate I on ‎02-19-2024 05:20 AM

Following update to "Version: 2.126.927.0 64-bit (February 2024)" I can now nolonger connect to a particular slq server.

 

Microsoft SQL: 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.)

 

This is not my server and it does not support encryption.  Until now I have been able to turn off encryption and have not had any SQL Certificate Chain errors.

 

I can still connect with the same settings via SSMS.  I don't think I can try things like trustcertificate in the connection string with PowerBI.

 

Has anyone a solution to this or any reports that this is a bug to be addressed would be much appreciated.

 

Thanks

 

Craig

 

 

Status: Accepted

Hi all,

 

We have seen on the internal platform that other engineers have reported this issue and submitted it.
The engineers on the product team are now aware and will do their best to resolve the issue. I will update here if there is any progress, so please be patient.

 

Best regards.
Community Support Team_Caitlyn

See more ideas labeled with:
49 Comments (49 New)
Comments
Joshrodgers123
Advocate IV
Advocate IV
‎02-23-2024 08:17 AM

I found the solution to this in the SQL Server Power Query docs. Looks like they updated it earlier this month. 

It worked for me in both desktop and the gateway.

Power Query SQL Server connector - Power Query | Microsoft Learn

 

Joshrodgers123_0-1708705004671.png

 

craigchamberlai
Advocate I
Advocate I
‎02-23-2024 09:31 AM

Hi @Joshrodgers123,

 

This is promising although I didn't solve the problem with it just yet.  I think the server I'm connecting to has no certificate which might be the problem but the setting seems to relate to the trusted server not the trusted certificate domain.

 

Other thing I'm wondering about is if it's smart enough to be port aware.  My connection string is something like:

example.domain.com, 1234

 

I do have example.domain.com loaded into $env:PBI_SQL_TRUSTED_SERVERS

 

I also tried example.domain.com:1234 in the host and env variable.

 

Any ideas?  @Joshrodgers123, does your server have a cert?  Default port?

Joshrodgers123
Advocate IV
Advocate IV
‎02-23-2024 09:39 AM

We're using an Azure SQL DB, so I don't think we added any certificate on it. We were not hitting this error on any of our on-prem SQL DBs.

 

When we connect with SSMS, we need to check Trust Server Certificate for the connection to go through though. There does not seem to be an option for that in Power BI Desktop/Gateway/Service.

 

All I did was add this to our environment system variables and it worked for desktop. I also added this to the config file on our gateway and that worked too. I'm guessing this is the same thing as checking that Trust Server Certificate box in other tools.

 

Joshrodgers123_0-1708709732103.png

 

rbrocks
Advocate IV
Advocate IV
‎02-23-2024 10:55 AM

@Joshrodgers123 your scenario sounds exactly like mine. I will try this. Does it have to be a system level variable do you know? I need admin access to my computer which I do not have to add that but I can add a user level variable. I will need to get assistance from my local helpdesk otherwise. 

Joshrodgers123
Advocate IV
Advocate IV
‎02-23-2024 10:57 AM

I didn't try user level variable, but it could possibly work.

rbrocks
Advocate IV
Advocate IV
‎02-23-2024 12:56 PM

@Joshrodgers123 Unfortunately the environment variable did not work for me. Is your azure sql db accessed via a private endpoint? I tried both a user and system variable and rebooted both times. 

 

Below is the error I am getting. So maybe it is something different than the error on this thread. I do have a ticket in with MS and talked to a tech a few hours ago. It started for us here with the February update as well. 

 

rbrocks_0-1708721733211.png

 

Geeco1
Helper I
Helper I
‎02-26-2024 02:19 PM

Hi @Joshrodgers123, 

The fix that you posted above seemed to work for me for some reports that are using the gateway, however, for queries in Fabric Datamarts, it does not work.  The datamarts that are reaching out to the off-site sql servers are still returning the same certificate errors as originally posted.  I don't know if there is another location to make a similar config file change or not, but it would need to be somewhere within power bi service?

ddg
New Member
‎02-29-2024 01:25 PM

Is there any update on this issue?  This caused reporting to be down with SQL Server connections in PowerBI. 

Genrique
Regular Visitor
‎02-29-2024 11:36 PM

@ddg No yet, i found the "solution" by installing the previous version (December2023). You've to do it outside of the Microsoft store and also i would confirm with my organization that there is no a automatically update set for PBI on your tenant. It is a shame that a business tool like PBI is two weeks with a bug that affects many users. I think Microsoft should reconsider their update policy.

Tiemen
Advocate II
Advocate II
‎03-04-2024 06:32 AM

There is a new version (2.126.927.0) of PBI desktop available, does anybody knows if there is a fix for this issue in this release? 

Idea Statuses