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.
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:
|MSSQL10||SQL Server 2008|
|MSSQL10_50||SQL Server 2008 R2|
|MSSQL11||SQL Server 2012|
|MSSQL12||SQL Server 2014|
|MSSQL13||SQL Server 2016|
|MSSQL14||SQL Server 2017|
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)
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)