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

[Geeco1]

I have been "told", by the Microsoft support person that has been working on my case, that the April release plus adding the trusted servers to the configuration file, is supposed to fix the issue.  I tried to install the April version as an Update, but the installation failed.  They told me that I have to uninstall the previous version first and then install the April version.  This will take more time on my end, so I will have to schedule some downtime to do this.  Before I go this route, has anyone had any luck with this version?

Thanks,

Geeco1

[Matt_Swan]

@UOLandrewdale & @Geeco1 

No change or improvement for me.  I don’t have any access or option to get any changes / certificates added at server side, its controlled elsewhere.  Nor do I have access to the machine with the on-premises gateway is installed.

I have to say, this approach of me doing ‘local fixes’ isn’t an acceptable or appropriate answer.  Microsoft have created this issue, albeit unintentionally, they need to correct it, not me.

If this continues much longer, it’ll seriously make me question the point of continuing with Power BI at all.  As having to use an out of date version or the restricted features Report Server version is becoming less appealing and less useful.

[MR_NK]

Does anyone have the install file for a previous version, or know where I could find one? We use the version from the MS Store and so it auto-updates and I don't have a previous install file to fall back on.

[Don32]

I have a weirder issue. I applied the config fix and env fix and it works on my laptop. What is odd is that it fails for others with the ssl error. I also get that error when I try and setup the connection in power apps.

I do have access to put a cert on the sql server, but I am yet going down that path yet. This is half working, which is very odd.

[Matt_Swan]

Updated yesterday to Version: 2.128.1177.0 64-bit (April 2024), no change or improvment, still getting the same error:

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

It is becoming untenable to use Power BI with this issue.

[Geeco1]

Over the weekend, I was able to update to the April version with success.  I uninstalled the previous version, and installed the April version.  I added the server name to the "Trusted Servers" settings in the Config file and restarted the gateway.  I didn't have the Recovery Key for the previous version, so a direct update was not feasible.  I had to re-create all of my connections in the new gateway and I had to go into each report and point the semantic models to the new gateway connectors.  For reports that connected to datamarts, I had to edit the report in desktop and "Get Data" again for those datasources to re-establish the connection through the new gateway.  For now, it seems to be working correctly.

Hope this helps.

Geeco1

[Matt_Swan]

Updated yesterday to Version: 2.131.901.0 64-bit (July 2024), no change or improvment, still getting the same error:

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

[ricsno_8900s]

Has anyone resolved this for the desktop version in the August 2024 Power BI version by adding the SQL Server name to their desktop's Environment Variables? Seems to have worked for me (hold thumbs). 

It seems to be the suggestion from here: Power Query SQL Server connector - Power Query | Microsoft Learn 

although this Microsoft Learn documentation talks more explicitly about the service. 

[PdctAnlyst]

Hi all,

Rolling back to a previous version of PBI solved the problem for me, I recently found an article that might be helpful on this topic:

Resolving the SQL Server SSL Certificate Error in Power BI | by Mohsin Mukhtiar | Microsoft Power BI...

Hope it helps