SQL SERVER – Unable to Load User-Specified Certificate [Cert Hash(sha1) “Thumbprint.here”]. The Server Will Not Accept a Connection

While preparing for a security demo, I broke my SQL Server and was unable to start it. In this blog, we would lean about fixing error Unable to load user-specified certificate.

Whenever I run into SQL startup trouble, I always start from SQL Server ERRORLOG.

SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location

Here are the messages at the end of the ERRORLOG file.

2018-08-04 14:21:46.02 Server Error: 26014, Severity: 16, State: 1.
2018-08-04 14: 21:46.02 Server Unable to load user-specified certificate [Cert Hash(sha1) "73EF12.thumbprint.here.CA8DE"]. 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.
2018-08-04 14:21:46.02 Server Error: 17182, Severity: 16, State: 1.
2018-08-04 14:21:46.02 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.

From the message we can see that there is something wrong with the certificate which I was using to start SQL Service. In the past, I have faced the same issue and I knew what needs to be done. Here is the registry key which stored thumbprint of the certificate

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.<Version>\InstanceName\MSSQLServer\SuperSocketNetLib\Certificate

The value of <Version> would be dependent on SQL Server version:

And you need to know the Instance name to reach to the right key. In below screenshot, I am showing key for SQL Server 2016 (MSSQL14) and Default Instance (MSSQLSERVER)

WORKAROUND/SOLUTION

If you are not using certificate, then you can rename “Certificate” to “Old_Certificate” and created a new entry (REG_SZ) called Certificate with no value (blank).  After doing so SQL Server should successfully.

If you are using certificate, then don’t play with that key. One other possible reason for such error while using certificate is that the SQL Server Service account doesn’t have full permission on Certificate private key. To fix this issue, you need to right-click on the certificate and in manage private key, grant full permission to SQL Server service account.

Reference: Pinal Dave (https://blog.SQLAuthority.com)