Configuring Imported Self Signed SSL Certificate to SQL Server Express

Question

I've created a self-signed certificate and configured with SQL Server Express. The encryption works fine on my PC.

When I export the certificate to another PC I can import fine and can see the certificate in MMC under Personal > Certificates.

However when I try to configure with SQL Server Express on the new PC, the certificate does not appear in the dropdown.

Any suggestions?

I have tried a few things suggested on other forums

Making sure the private key is exported Making sure the certificate was created for local system (not user) Copy certificate into trusted certificates

The feedback I've got so far is that the certificate CN value is set to the computer name it was created on "Server1". This will not work when copying to another PC with a different computer name "Server2".

Is there a way around this?

Answer 1

The SQL TLS configuration GUI is super picky about the certs it will show in the drop down. And yes, the CN value is one of those things it is picky about. So if the CN doesn't match the server name, it won't show up even if the server name is in the Subject Alternative Name field.

So your first option is to generate a new self-signed cert that does match and use that.

The other option is to bypass the GUI and set the certificate via the registry instead. The registry location depends on the SQL version being used. I'm not sure if SQL Express is different than full SQL. But here's the location for full SQL 2016. Basically, find the SuperSocketNetLib key for your installation/instance.

HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer\SuperSocketNetLib

You also need the certificate thumbprint for your certificate. You can find this via PowerShell like this. Copy the value in the Thumbprint column for your cert.

dir cert:\LocalMachine\My

Now use the following from an elevated PowerShell session to set the thumbprint value and enforce encryption.

$regPath = 'HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer\SuperSocketNetLib'
$thumbprint = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
Set-ItemProperty $regPath -Name 'Certificate' -Value $thumbprint
Set-ItemProperty $regPath -Name 'ForceEncryption' -Value 1 -Type Dword

And finally, restart the SQL service to pick up the changes.