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

[UOLandrewdale]

hi @Matt_Swan 

have you tried the environment variable fixes outlined above (copy below)? Next week we will try the April version and see if we need the variables

Tiemen 

Advocate II

‎11-03-2024 08:39 AM

@dg23 @rbrocks From MS support I recieved the following last week:

We have a new added feature to allow connection to designated SQL servers with either self-signed certificates or not configured for encryption. Before adding the feature , you would fail to establish a secure connection and users would be unable to connect. Now, if you trust the server, you can add it to the SQL trusted servers list. This prevents the need to purchase and deploy new certificates.

In your case , you have the following two options :

1. You should either consider getting the certificate into SQL Server if you don't prefer to apply the environment variable to all users .
2. Trust the server following the previously provided steps Power Query SQL Server connector - Power Query | Microsoft Learn
3. You can use the group policy Create Environment Variables with Group Policy | Microsoft Learn , for this please engage your Active directory team to check if GPO can be used to push the configuration and let us know if you need any help to open a collab with the windows team.

[MR_NK]

Having this issue as well, NEED it to be fixed. 

- have tried adding servers to trusted servers, no difference

- there is no certificate on the SQL server and we aren't going to be adding one

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

[UOLandrewdale]

@MR_NK 

previous files are here

https://learn.microsoft.com/en-us/power-bi/fundamentals/desktop-latest-update-archive?tabs=powerbi-d...

[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