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:
1
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.
Nbctcp's Weblog 