PS without BS: Finding the SQL Cert and trusting it to a remote system

This was an interesting case, and surprisingly didn't really find anything on how to do this out there. But, the problem statement was simple. When trying to connect to SQL (this was PowerBI, but was the same for SSMS), this error showed up:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

Now, I remember this situation coming up from SCCM (or something very similar), when you were using Report Builder on a remote system from the SQL Server. The answer was to export the SQL cert, and install it on the remote Report Builder system.

So, here is the script, but standard disclaimer applies: Using it does acknowledge all risk is on you, and I'm just sharing what worked for me. Your results may vary, so prior to taking the "cut and run" approach to the script, make sure you read below about the how's and why's. Keep in mind we are not creating a cert here, just leveraging the existing one.

 
$SQLVersionAndInstance = "MSSQL14.MSSQLServer"
$RegKey = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$SQLVersionAndInstance\MSSQLServer\SuperSocketNetLib"
$Thumbprint = (Get-ItemProperty -Path $RegKey -Name Certificate).Certificate
$Certificate=Get-ChildItem -Path Cert:\LocalMachine -Recurse | ? {$_.Thumbprint -eq $Thumbprint}
Export-Certificate -Cert $Certificate.PSPath -FilePath c:\temp\sqlcert.cer

Let's dissect this a bit:
The $SQLVersionAndInstance denotes the version of SQL you are running (i.e. MSSQL13 = 2016, MSSQL14 = 2017) and the instance (the default is MSSQLServer). So, if you were running SQL 2016 on named instance "MyInstance", the variable will be MSSQL13.MyInstance.

The registry key the SQL certificate is listed in will be in registry key in my default example (note the MSSQL14.MSSQLServer for SQL 2017 and default instance) will be HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLServer\MSSQLServer\SuperSocketNetLib in a string called "Certificate". You will see the cert thumbprint there.

Wait: What if this key has nothing in it? I wrote a followup: When SQL has no certificate...

We take this value and verify it is in the local computer certificate store (obviously if the thumbprints don't match, this won't work). This cert should be in the Personal store for the Computer certs.

Last, but not least, I then take this cert and export it to a file (CER export).

I then take this file and import it into Trusted Root Certification Authorities on the system throwing the error.

That should be it, simple, easy, and now you're back on your way.

— Easy link to my blog: https://aka.ms/leesteve —
If you like my blogs, please share it on social media and/or leave a comment.