SQL: SSL and SQL Server 2008 – Service Doesn’t Start – Error Code 26014

After requesting the SSL certificate and getting it in the correct format to import into the server’s Personal Certificate Store using the steps found here, I was finally ready to select the certificate and encrypt the database traffic. As outlined here I selected the correct SSL certificate set the ForceEncryption flag to Yes. After doing such, I went to restart the SQL service as told and was presented with the following error: “Windows could not start the SQL Server (XXXXX) on Local Computer. For more information, review the System Event Log… and refer to service-specific error code 2146885628”

Instead of the System Event Log I went straight to the SQL Error Log (using the default installation the error logs are found in C:\Program Files\Microsoft SQL Server\MSSQL10_50.InstanceName/MSSQL/Log) and found the following information:

2010-09-08 12:35:30.76 Server      Unable to load user-specified certificate [Cert Hash(sha1) "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"]. 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.
2010-09-08 12:35:30.79 Server      Error: 17182, Sev	erity: 16, State: 1.
2010-09-08 12:35:30.79 Server      TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.
2010-09-08 12:35:30.82 Server      Error: 17182, Severity: 16, State: 1.
2010-09-08 12:35:30.82 Server      TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.

The certificate hash matched that of the certificate I had associated with the instance but it wasn’t loading. By default, SQL Server is run under a non-privileged service account and doing such is good practice as if your database is compromised, the attacker will have limited ability to harm the rest of the data on the server. But, this also raises another problem as when using a custom SSL certificate to encrypt database traffic, the non-privileged service account needs to be able to read both the public and private key of the certificate. Understandably, by default the service account does not have permission to read the private key. Once we give the service account permission to read the private key, the SQL Server will start up without a problem.

Follow the steps below to allow the service account to read the private key of the SSL certificate.

1. First we need to find the name of the service account used by the instance of SQL Server. It will probably be something like ‘SQLServerMSSQLUser$[Computer_Name]$[Instance_Name]’.
2. One way to do this is to navigate to the installation directory or your SQL Instance. By default SQL Server is installed at C:\Program Files\Microsoft SQL Server\MSSQL10_50.InstanceName.
3. Right click on the MSSQL folder and click Properties.
4. Click the Security tab and write down the user in the Group or user names window that matches the pattern of ‘SQLServerMSSQLUser$[Computer_Name]$[Instance_Name]’.
   
5. Now, open the Microsoft Management Console (MMC) by click Start -> Run, entering mmc and pressing Enter.
6. Add the Certificates snap-in by clicking File -> Add/Remove Snap-in… and double clicking the Certificates item (Note: Select computer account and Local computer in the two pages on the wizard that appears.
7. Click Ok.
8. Expand Certificates (Local Computer) -> Personal -> Certificates and find the SSL certificate you imported.
9. Right click on the imported certificate (the one you selected in the SQL Server Configuration Manager) and click All Tasks -> Manage Private Keys…
10. Click the Add… button under the Group or user names list box.
11. Enter the SQL service account name that you copied in step 4 and click OK.
    
12. By default the service account will be given both Full control and Read permissions but it only needs to be able to Read the private key. Uncheck the Allow Full Control option.
    
13. Click OK.
14. Close the MMC and restart the SQL service.

If all went well the SQL service should restart without any problems now. To check to see if the selected SSL certificate is actually encrypting the traffic to and from the database, go back to the Log folder in the installation directory of the SQL Instance and open up the ERRORLOG file. You should see somewhere in that file the following line:

2010-09-08 13:33:47.88 Server      The certificate [Cert Hash(sha1) "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"] was successfully loaded for encryption.

Where the certificate hash code matches that of your imported SSL certificate. To verify the has code, go back to the MMC with the Certificates snap-in loaded and your SSL certificate visible. Double click on the certificate and click the Details tab. Scroll to the bottom of the list and select the Thumprint field. The value that appears should match that which is shown in the log file.

If you really want to know if the data is being encrypted, simply run a packet sniffer like Wireshark or Microsoft Network Monitor.

Hope this helps!

IIS: Disappearing SSL Certificate Problem Resolved

I followed the many different articles outlining how to import SSL certificates into IIS exactly and everything worked great except for the fact that when I navigated away from the Server Certificates window, the certificate would disappear. I could see the certificate in the server’s Personal Certificate Store using MMC (Microsoft Management Console) but it wouldn’t show up in IIS.

My problem was that the certificate that I was trying to import only contained the public key and not the private. For decryption to work correctly, the server obviously needs to have both the public and the private key. To resolve the problem I created the needed PKCS #12 Certificate File following the steps outlined here, and then clicked on the Import link (not the Complete Certificate Request… link) in the Server Certificates window in IIS.

From there simply select the .pfx file, enter the password used when creating the file, and you are good to go.

SQL: SSL and SQL Server 2008 – Creating the Certificate

One of the nice security features of SQL Server 2005 and 2008 is that it allows you to encrypt the traffic between the server and client using your own SSL certificate. This can be done using a self signed or a third party certificate from a credible CA but note that using a self signed certificate is less secure and introduces the possibilty for a man in the middle attack. The process for doing such is outlined very well on many different websites including this MSDN article.

I requested a certificate for our domain from GlobalSign and received the certificate back in the form of a text file with three sections: Regular Certificate (X509), Intermediate Certificate, and the Private Key. The file looked like the following:

Regular Certificate (X509)
-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----
Intermediate Certificate
-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----
Private Key
-----BEGIN RSA PRIVATE KEY-----
...
-----END RSA PRIVATE KEY-----

Having never worked with SSL before I wasn’t quite sure how to go from a text file to the needed PKCS #12 Certificate File format (.pfx file) to import the certificate into the server’s certificate store. After searching around I found that a tool called OpenSSL would do the trick. (You can download the binary files for Windows here.) It is a command line tool that allows you to manipulate SSL certificates in many different ways.

To generate the needed PKCS #12 Certificate File using OpenSSL do the following:

1. Download and install OpenSSL from here.
2. Save the three portions of the certificate into three separate files (Note: The Intermediate Certificate is optional, if you were not supplied with one, just skip the steps involving it):
   • privatekey.txt – Copy and paste the contents of the private key including the begin and end lines.
   • certificate.txt – Copy and paste the contents of the Regular Certificate including the begin and end lines.
   • intermediate.txt – Copy and paste the contents of the Intermediate Certificate including the begin and end lines.
3. Move the three files into the bin folder where you installed OpenSSL (default folder is C:\OpenSSL-Win32\bin).
4. Open the command prompt and navigate to the bin folder where you installed OpenSSL.
5. Enter the following line and press enter (Omit ‘-certfile intermediate.txt’ if you do not have an Intermediate Certificate):

   openssl pkcs12 -export -out certificate.pfx -inkey privatekey.txt -in certificate.txt -certfile intermediate.txt
   

After the process completes, there will be a certificate.pfx file in the bin directory that can be used to import the certificate into the servers personal certificate store.