Installing certificate Keys for SQL Server

Posted on by

SQL Server Connection Encryption – Inhouse Operations – Confluence

Prerequisites

 A SSL Certificate is needed to enable the connection encryption.

The following prerequisites must fulfill to setup the connection encryption:

  • The app team requests the certificate on dbPKI with the FQDN of the server as Subject without any sub- or prefix, i.e. (fradbhdimu1.de.db.com:65953-1.p12) would not work.
  • The certificate must include the public and private key (*.P12  file) and protected with a password.
  • Password provided to the implementer.

To setup the certificate it must import into the Windows certificate store first and permissions to the SQL Server Service Account must setup.

  1. Open up Management Console (mmc.exe) and add the Certificates Snap-In to the Computer Account – Local computer (use File/Add or Remove Snap-ins):

  2. Import the extracted certificates (both .crt and .p12 ,you’ll need a password for the p12 one) into the personal folder(click on the personal or certificate folder and then right click in the middle window – All Tasks – Import) :
     
  3. Open “Manged Private Keys” on the .p12 certificate (see below) to grant permission on the certificate to the SQL Server Service Account:

  4. Add the SQL Server Service Account (normally NT Service\MSSQLSERVER) and grant read permission:

  5. Start the SQL Server Configuration Manager and open the Properties of the Network Configuration:

  6. Enable Fore Encryption:

  7. Select the Certificate from the list:
  8. Restart the SQL Server Service

A quick test to see if its working is to use SSMS to connect and in the options enable “Encrypt Connection”

Troubleshooting

If you have a server with a misconfigured certificate which won’t start up then you can follow these steps to try and fix it and bring it up again:

1) in SQL Configuration Manager turn off “Force Encryption”

2) In Regedit go to key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib and remove the value for Certificate

3) You can now try to restart the SQL Server, if it works great if not then one final step to try is to restore the master database from a recent working backup to another instance of the same version (as oldmasterdb) and then detach it and replace the existing master.mdf and mastlog.ldf files with these restored files.

If you have a situation where you have followed all the steps but can’t see the certificate in the drop down list (7) then you can try these steps:

1) Get the value for the certificate by opening the certificate and looking at details-Thumbprint

2) Convert this thumbprint value to uppercase and remove all spaces

3) Open up Regedit and navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib and enter the converted value from above in the Certificate field

4) Final step is to set Force Encryption to Yes and then restart the SQL Server

5) To see if its worked check the SQL Server Error log for the following string: “The certificate [Cert Hash(sha1) “ED806573B335D5DAB8C6037278F5CF6AC4C06804″] was successfully loaded for encryption.”