When SQL has no certificate...

  • Article

Yesterday I posted a blog on how to identify the SQL Certificate used. I had some questions come from this, and the biggest one was, but what if the registry key is blank?

Fear no more, here are steps to populate this registry key properly.

I was able to reproduce this issue on a system with the HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\(Your SQLVersionAndInstance)\MSSQLServer\SuperSocketNetLib\Certifcate key being blank.

So, here is how you populate said key:

  • Act I: Go into IIS Manager
    • Click on the Server Name and in the features view, open Server Certificates.
    • Click Create Self-Signed Certificate
    • For a friendly name of the Certificate, use the FQDN of the server.
    • Click OK, and you will see the new certificate appear
  • We are going to make a self-signed certificate here.
  • Act II: Checking the Service Account
    • Go to Services.msc and look for the SQL Server service account you're using, and of course, write it down.
  • Act III: Giving the certificate proper permissions
    • Go into mmc.exe and add the certficate snap-in for the computer account.
    • Find the certificate you created (should be in the Personal\Certificates container, right click, and Manage Private Keys.
    • Add the SQL Server service account from Act II. You just need read permissions only.
  • Act IV: Adding the certificate to SQL Server
    • Go back to Configuration Manager and under Protocols for (Instance Name), click Properties
    • Click the Certificates tab, and the certificate your created should show in the drop down.
    • Select the certifcate, and click OK.
    • Restart SQL Services to confirm the certificate is working.
  • Act V: (Almost there) Check that the registry key "Certificate" is now populated.

This should be what you need to create and verify the SQL Server certificate is now in place.

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