SQL Server service does not start after enabling SSL encryption
Posted by Sudarshan Narasimhan on August 3, 2011
I recently had a customer who came up to me with a SQL Service start-up issue. This issue started after they had enabled SSL encryption using a personal certificate. Now, before I begin let me confirm a couple of things :-
- The correct SSL certificate has been chosen in SQL Configuration Manager.
- The ForceEncryption flag is set to Yes.
There are some forum posts on this issue and I don’t entirely agree with the solutions proposed there, because they did not actually solve the real problem but worked around it without understanding how/why the problem occurred, and how to get a complete solution.
I am going to be bold and say this; please do not follow the above posts since they are workarounds. Most people in enterprise environments cannot run SQL Service under the Local System account, which is what the above posts are asking you do to, yikes!!!
As always, the first place to check when a SQL Service does not start is the SQL ERRORLOG (yes, ERRORLOG and NOT the EVENTLOGS – I said 1st place).
For a complete step-by-step guide to approach SQL Service start-up issues, you can read the blog post I wrote on MSDN SQL Server FAQ –> HERE
Here is what I found in the ERRORLOG,
2011-06-08 12:35:30.56 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.
2011-06-08 12:35:30.59 Server Error: 17182, Severity: 16, State: 1.
2010-06-08 12:35:30.59 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.
2011-06-08 12:35:30.62 Server Error: 17182, Severity: 16, State: 1.
2011-06-08 12:35:30.62 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.
So, it appears the certificate chosen for SSL encryption has some issues. Here are the things I checked next:-
1. The certificate itself is valid and has not expired (using certmgr.msc).
2. The certificate hash matched that of the certificate associated with the instance but it wasn’t loading.
The certificate appears to be fine. Well, what next?
The way SQL Server loads the certificate happens through the credentials of the SQL Server service account defined for this instance. So, I started to check what the SQLSvc account was and whether it had permissions to access the certificate. At this point I cam across this forum post for a similar issue
http://social.msdn.microsoft.com/Forums/en/sqlexpress/thread/db68b3bf-33a8-42d6-8dac-f7229a2f9cde
The solution listed there was to ensure that the SQL service account had a valid user profile on the server machine. This profile can sometimes be missing if the service account was changed to a new account, which doesn’t yet have a user profile created. You can verify the user profile on any machine by going to
My Computer –> Properties –> Advanced –> Settings (under User Profiles section).
Solution #1 (as applicable)
Identify the SQL service account and log on to the server machine that is running SQL Server, with the service account. During logon, Windows will create a new user profile for the service account.
Unfortunately, this wasn’t the scenario in my case. Another thing I have to caution people is regarding service account change. When changing the SQL Svc account ALWAYS ALWAYS use the SQL Server Configuration Manager, and NEVER do it through services.msc. Here are some reasons why:-
1. Permissions & ACL’s of SQL Server registry keys are done only by SSCM
2. SQL Server User Groups are updated with the new service account only by SSCM.
3. WMI namespace permissions are only set through SSCM.
So, if you changed the service account through services.msc, it is possible that the new service account does not have permissions to access the certificate. The certificate hash is stored in the following registry key. If the new service account cannot access it, then you “can” get the error above, since SQL cannot load the certificate due to insufficient privileges.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer\SuperSocketNetLib\Certificate
I started to check if this was happening in my case and found that the SQL Service account was not even present in the local group SQLServerMSSQLUser$[Computer_Name]$[Instance_Name]. This confirms that the service account change wasn’t done through SQL Configuration Manager.
This means the SQL Service account does not have permissions to open the private key for the certificate. i.e. SQL Server failed to load this specific certificate due to insufficient permissions. Once we give the service account permission to read the private key, the SQL Server should start up without a problem.
Here is what I did to resolve it,
Solution #2
1. Identify the SQL service account (could be a domain account or any of the built-in accounts like LocalSystem/LocalService/NetworkService).
2. If the service account was set properly using SQL Configuration Manager, then it should have been added to the SQL Security Groups. Look for this on the local machine groups. e.g. SQLServerMSSQLUser$[Computer_Name]$[Instance_Name]
3. Open the Microsoft Management Console (MMC) by click Start -> Run, entering mmc and pressing Enter.
4. 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.
Click OK.
5. Expand Certificates (Local Computer) -> Personal -> Certificates and find the SSL certificate you imported or created.
6. Right click on the imported certificate (the one you selected in the SQL Server Configuration Manager) and click All Tasks -> Manage Private Keys.
Note:
You will only see this option for SSL certificates imported from a certifying authority. E.g. you will not see this for EFS, RDP certificates
7. Click the Add button under the Group or user names list box.
8. Enter the SQL service security group that you identified in step #2 and click OK.
You can also directly add the service account itself here, but for any future changes you need to repeat these steps to add that individual account. Instead its better to add the security group, since the service account will always be present here if account change was done using SSCM.
Now, when I started the SQL service you can double-check that the certificate is being loaded and used by looking for this entry in the errorlog. In my case, it started fine and SSL encryption was working.
2011-06-08 12:48:47.88 Server The certificate [Cert Hash(sha1) “XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX“] was successfully loaded for encryption.
Problem Solved!
Now, to check if the connection is actually encrypted you can use Netmon or Wireshark to check the network packet. Refer to the screenshot posted in this blog on how to do that. An easier way for DBA’s to verify this is to use the DMV sys.dm_exec_connections, look at the encrypt_option column. This DMV also tells you if the connection is using Kerberos/NTLM/SQL Authentication -> auth_scheme column has this value.
Hope this saves you some time.
The SQL Dude! 
Fred said
Thanks, your analysis and solution worked for me when installing SC2012 on Windows 2008 64.
Sudarshan Narasimhan said
You’re welcome!
Andrew said
Very useful. I was having issues getting winhttpcertcfg to work for allowing the NETWORK SERVICE to access the private key. Your solution to go through the Certificates MMC worked like a charm. Thanks!
Fixer Registry said
After I initially left a comment I seem to have clicked on the -Notify me when new comments are added- checkbox and now
each time a comment is added I receive 4 emails with the exact same comment.
Perhaps there is a means you are able to remove me from that service?
Thanks!
Dave said
On Windows 2012 (SQL2012) there is no SQLServerMSSQLUser$ group. Do we create this? Within SSCM it states that SQL Server is running under the NT Service\MSSQLSERVER account.
Do we therefore add that to the certificate instead?
Sudarshan Narasimhan said
Dave,
During SQL Server installation, SQL Server Setup creates local Windows groups for SSAS and SQL Server Browser. For these services only, SQL Server configures the ACL for the local Windows groups. For SQL Server/SQL-Agent, per-service SID (e.g NT Service\MSSQLSERVER or NT Service\MSSQL$INSTANCENAME) is instead used. You can find more info on groups/permissions in this MSDN article under section “Windows Privileges and Rights”
http://msdn.microsoft.com/en-us/library/ms143504.aspx#Windows
The per-service SID (NT SErvice\MSSQLSERVER) already has access to the SQL registry hive and can read the certificate hash value without you granting any explicit permissions. Reference: http://msdn.microsoft.com/en-us/library/ms143504.aspx#Registry
So in your case since SQL is running the NT Service\MSSQLSERVER account, you can choose this service account in step#7.
HTH.
The tired guy said
I cannot count the hours you saved me. Because I did not waste them 🙂
I seriously love you!
Stefan said
Excellent article, saved my day. Another consultant had installed sccm2012 and changed the service account for SQL Server through Services.exe When we tried to change the service account we got the certificate problem and solution no 2 made it work again.
Kind regards
Stefan
basil said
thank you SQL Dude
Ravi said
Thanks for this port. it really help to solve SQL issue with cerficiate
Nicolas said
Thaksss…. it work for me! Regards
KS said
Hi, first of all thanks for the post, it is really helpful. However in my Win 2012 & MSSQL 2014 I do not have the SQLServerMSSQLUser$ group as Dave mentioned in his comment, granting access to NT Service\MSSQLSERVER as your reply solved the problem. However I notice that other services (server agent, reporting, analysis services) are logon as different user. Do these service need to access the certificate as well (so I need to add the permission)?
Sudarshan Narasimhan said
Hi, starting with SQL 2008 R2, the local groups are no longer created by SQL Server Setup. Instead, per-service SID’s (virtual accounts) are used. So if your SQL Service is a default instance, then the default service name is [NT Service\MSSQLSERVER] and if it’s a named instance then it is [NT Service\MSSQL$]. You can refer to https://msdn.microsoft.com/en-AU/library/ms143504.aspx#VA_Desc for all the virtual accounts based on the feature of SQL Server installed.
mukesh singh said
By using https://www.youtube.com/watch?v=4nwHlt39vW4 I am able to create certificate server on window Server 2012. I am not able to Fixed issue. because my certificate is setting under Administrator Tool -> Certification Authority -> Issued certificate ,
So how i can move to the Personal and give the Proper Right.
Chris Martens said
I just discovered this problem on my network. I don’t regularly access the SCCM management console unless doing updates, or adding/changing packages. All clients appeared to be able to access the Software Center, and are still getting endpoint protection configurations and client installations to new machines. Today however, I needed to access the management console, and it wouldn’t connect, with an ambiguous error. After spending hours troubleshooting this with the help of online resources, I finally found a resource that suggested checking the SQL server status, which was stopped. Starting the service resulted in the errors indicated in this article! Great! Now I can fix it! Looking into the errors, and their causes, it dawned on me that I had completely removed and set up a new PKI – making all previous certificates invalid.
The suggestions and steps in this article did not help – as it was impossible to identify the correct certificate being used by the SQL server for SSL (multiple certificates present, all labelled identically aside from some variations in purpose). While going through the SQL Configuration Manager to find the “Force encryption” flag to turn it off (until I can properly straighten out the issue) I found it was already off! I then noticed a separate Certificates tab, and the Certificate drop down list was blank. Dropping down the list provided me with one single certificate to choose from. Selecting this certificate and applying it corrected the issue. I am now back up and running!
Thanks a lot for at least taking me through the steps I needed to look at to find this!
Nick McAvoy said
It’s 2017, and this post is still a big help. I hope that is satisfying!
kiwiant said
More tips on this:
1. If copying/pasting the certificate thumbprint it may contain Unicode characters. Copy to notepad, save as Ascii, close then reopen, remove any question marks and spaces (case doesn’t matter)
2. You don’t need the certificate to be marked as exportable
3. Read permission is enough for the NT Service\MSSQLServer account to read the cert
Spencer Sullivan said
MANY thanks! Had this happen while testing on Local Machine. Great write up.
Norali said
Excellent! Just what I needed and now I understand how it works. Thanks!