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:

MSSQL10SQL Server 2008
MSSQL10_50SQL Server 2008 R2
MSSQL11SQL Server 2012
MSSQL12SQL Server 2014
MSSQL13SQL Server 2016
MSSQL14SQL 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)

SQL SERVER - Unable to Load User-Specified Certificate [Cert Hash(sha1) "Thumbprint.here"]. The Server Will Not Accept a Connection cert-thumb-err-01

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)

, , ,
Previous Post
SQL SERVER – Database Mirroring Login Attempt Failed With Error: ‘Connection Handshake Failed. An OS Call Failed: (80090350)
Next Post
SQL SERVER – Setting ARITHABORT ON for All Connecting .Net Applications

Related Posts

11 Comments. Leave new

  • Thank you for providing useful information

    Reply
  • This resolved my problem, thanks!

    Reply
  • Jeffrey Ayoub
    June 6, 2019 12:26 am

    The process worked for me when we had a project to Clone via VCenter a server that had SQL Server 2014 installed. I could not get the SQL Server Service to start and got the error message above. I deleted the old server certificate from MMC and still getting the same error. I finally went into the registry and perform these steps and the instanced started up perfectly.

    Reply
  • gaetanbouchard99
    August 15, 2019 11:15 pm

    Worked prefectly for me today. After most recent Windows update, SQL server refused to start service.

    Reply
  • Thank you, this worked for me as well;

    It is disappointing that there is no real documentation from official SQL Server on this problem

    Reply
  • I have left numerous comments over the years, but THANK YOU AGAIN! I was trying to secure my instance and I secured it so well that I could not get into it. This corrected the problem. You are the best ever!

    Reply
  • Also worked for me. Renaming the registry key did a certificate reset (new cert was generated). However, I am completely clueless on why my certificate got corrupted/inaccessible/…. Anybody found a root cause on this one?

    Reply
  • Hi Panal,

    Thankyou so much i was having the issue and fixed after reading your blog :)

    Reply
  • Awesome sir ji, you saved my time…kudos to you.

    Reply

Leave a Reply

Menu