...Because someone's gotta tell the story...


To return to the main Blog List, click Full Blog Listing.



When SQL has no certificate...

Tuesday, April 24, 2018 in Technical Articles (Views: 2415)
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

  • We are going to make a self-signed certificate here.
    • 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
  • 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.


Related Blogs You May Be Interested In:

To leave a comment, please log in and/or register.