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!

About these ads
Posted in Security, SQL. Tags: , , . 17 Comments »

17 Responses to “SQL: SSL and SQL Server 2008 – Service Doesn’t Start – Error Code 26014”

  1. Chris Says:

    Hi,

    ok, I think that’s only possible in win server 2008, in 2003, there is no manage private keys item in the certificate mmc.

    You need winhttpcertcfg to grant the rights to the respective account…

    • Nick Olsen Says:

      Good to know. Thanks for the information!

  2. Cameron Says:

    Perfect!!!!! Ran into this exact situation. Your outlined solution saved my bacon today. Thanks…

    • Nick Olsen Says:

      I’m glad it helped!

  3. John C. Kirk Says:

    Thanks for the tip. Digging through my notes, I came across this issue a couple of years ago, but I forgot to tweak the permissions when I re-installed my server in January (Windows 2008 R2, SQL 2008). Bizarrely, it worked anyway, until I installed SP1 for Windows 2008 R2, then it ground to a halt. I assume that “didn’t enforce permissions for private keys” was one of the bugs that Microsoft fixed in SP1.

  4. Julian Says:

    This solution works !!!! But …. didn’t find any “manage private key” as in your step 9. I tried on various OSs (W2003 and 2008s) without success. Which version or update are you using ? (winhttpcertcfg with more certs with the same subject [=machineName: very common] works only on the first. SO your MMC is the real solution)
    Tahnks ! Julian

  5. David Says:

    Thanks! Resolved our issue very quickly!

  6. Bob Snuggs Says:

    I have this exact issue, but after I updated the authority on the private keys the problem didn’t go away. If I change the SQL service to run as an administrator, it will start up (but I can’t leave it that way). Obviously I have some other authority problem. Any ideas?

    • Daniel Epperson Says:

      Same for me.

  7. Mike Lee Says:

    Thank you very much, fixed my problem!!1

  8. Yang Li Says:

    Hi Nick I have the exactly problem, however, in step 4 I found user MMSQL$SQLEXPESS and in step 11 I couldn’t find the same user. I only found SQLServerSQLAgentUser$computername$intancename. Then I select it but got “Object Permissions are not available for this object type”. It it seems a group.

    I am using Win Sever 2008 R2 SP1 64 bit OS. thanks

  9. John Ferguson (@fergie348) Says:

    Thanks Nick – very helpful..

  10. Mike Bridge Says:

    Thanks—I never would have figured this out from the useless Microsoft error messages!

  11. Bill Fry Says:

    The solution works for me but the problem is if I have to shut the server down, when it re-starts, the service account is not back in and have to redo the steps all over. Any idea how to get the account to stay in the security settings?

  12. simon Says:

    For some reason, my sql server for Business Contact Management somehow starting to use certification after a Ricoh printer monitoring program installed on my server with SQL 2005. I got error starting SQL and I do not need certification at all. I finally figure that I can simply leave CERTIFICATION key value to be EMPTY (not 0)

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib

    Hope this help someone else with this problem

  13. TechNet Blogs Says:

    […] SQL: SSL and SQL Server 2008 – Service Doesn't Start – Error Code 26014 […]

  14. Error Code -2146885628 | Nbctcp's Weblog Says:

    […] http://nickstips.wordpress.com/2010/09/08/sql-ssl-and-sql-server-2008-service-doesnt-start-error-cod… […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 68 other followers

%d bloggers like this: