0

Recently, I was asked to apply SSL certs to SQL. After a bit of research, I found an article to add the server cert to SQL using the SQL Server Configuration Manager.

When the server cert expires our PKI admin has it to auto update within the system via powershell script, our server certs are generated via MS CA and the name of the cert remains consist ex Server1.domain.crt - which i use in the SQL Cert settings

My question, when the cert expires and is replaced will it also be reflected in SQL? If not is there a way to automate updating to the new cert?

Improve this question
0

1 Answer 1

Reset to default
1

Will the replacement certificate automatically work?

No. When the certificate is replaced, SQL will not automatically use it. SQL decides which certificate to use based on the thumbprint, which will be different for the new certificate, even if the name is the same.

Is there a way to automate this?

Yes. I'll assume that your PKI admin can get it installed to the server's certificate store. What remains is the part where we tell SQL to use the new certificate.

You've seen the documentation about using the SQL Server Configuration Manager GUI to tell SQL which certificate to use. The good news is that you can also do that without using the GUI. You can use the registry.

Enter the thumbprint of the new certificate in the Certificate value at HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Identifier>\MSSQLServer\SuperSocketNetLib.

Some examples of an instance identifier include:

  • MSSQL10.MSSQLSERVER for a default SQL 2008 instance.
  • MSSQL12.InstanceName for a named SQL 2014 instance.
  • MSSQL16.InstanceName for a named SQL 2022 instance.

Depending on where you get the thumbprint from, you might have to remove the spaces from the thumbprint. The Certificate management control panel includes spaces between pairs of characters in the thumbprint, but the registry wants it as one continuous string.

While removing the spaces, make sure you don't have any invisible characters at the beginning or end of the string. The rich-edit control in the Certificates MMC snap-in tends to introduce an extra character at the beginning (thought to be a Left-To-Right Mark (U+200E)) when copying from the control to the clipboard. Neither Notepad nor Notepad++ will display this character, so it's easy to miss. If you have an extra invisible character in there when you paste it into the registry, the registry won't complain, but SQL will. You'll get a pair of errors like this when SQL tries to load the certificate.

TDSSNIClient initialization failed with error 0xd, status code 0x38. Reason: An error occurred while obtaining or using the certificate for SSL. Check settings in Configuration Manager. The data is invalid.

TDSSNIClient initialization failed with error 0xd, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The data is invalid.

Caution: There is a similar registry location --HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib. That's not the one you are looking for.

Sample registry entry:

Windows Registry Editor Version 5.00 [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib] "Certificate"="bbadaffaf415ed67e1a056f9c4230ef17bc3002e"

If the service account that the SQL Engine runs under is not a local administrator, you need to grant it Read access to the certificate's private key. Here's the manual process, and I suspect you can figure out how to automate it. (I think I got it automated around here somewhere, and if I can find my notes, I'll add them later.)

  • Open the Certificates management console, or add it as a snap-in to a fresh MMC session. Select Local Computer for the set of certificates to manage.
  • Find the certificate in question. It is probably under Personal --> Certificates. Right-click and select All Tasks --> Manage Private Keys....
  • Add the SQL Engine service account, giving it Read access. (It'll default to Full Control when you add it, but Read is sufficient.)
  • (In a cluster, you have to do this on each node.)
  • If the SQL Engine service account doesn't have read-access to the certificate, you'll get a pair of errors like this:

Unable to load user-specified certificate [Cert Hash(sha1) "C97DDE93C88EDB342C700711E1E2CA1087B3E0C5"]. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.

TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.

Restart SQL to get it to start using the new certificate.

All of that should be pretty straightforward to automate with any typical automation processes.

Footnotes

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.