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

[craigchamberlai]

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

[Joshrodgers123]

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

[craigchamberlai]

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]

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.

[rbrocks]

@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]

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

[rbrocks]

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

[Geeco1]

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]

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

[Genrique]

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

[Anonymous]

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?