次の方法で共有

The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030d. Check certificates to make sure they are valid.

  • [アーティクル]

This time I came across a certificate error, that was stopping the SQL Server service from starting:

The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030d. Check certificates to make sure they are valid.
Error: 26014, Severity: 16, State: 1.
Unable to load user-specified certificate 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.
2014-10-15 09:44:12.23 Server Error: 17182, Severity: 16, State: 1.
2014-10-15 09:44:12.23 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.
2014-10-15 09:44:12.23 Server Error: 17182, Severity: 16, State: 1.
2014-10-15 09:44:12.23 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.
2014-10-15 09:44:12.23 Server Error: 17826, Severity: 18, State: 3.
2014-10-15 09:44:12.23 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2014-10-15 09:44:12.23 Server Error: 17120, Severity: 16, State: 1.
2014-10-15 09:44:12.23 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

The critical question as always is what was the last change before the error started occurring.

Fortunately the DBA was willing to share this information. Usually nobody remembers if they did any changes at all :S

The last change was that the SQL Server service account had been removed from the Local Admins group.

BINGO! A permissions issue!

A Process Monitor trace captured from the reproduction of the issue showed clear access denied events at these folders and registry keys:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\
C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys
HKLM\System\CurrentControlSet\Services\WinSock2\Parameters

Adding Full Control permissions for the SQL Server service account at these locations solved the certificates errors.

Long live the Process Monitor tool! :)

Comments

  • Anonymous
    January 16, 2015
    Thanks, this helped me a lot! I had come across some other articles suggesting that the user running the SQL Server service (NT ServiceMSSQLSERVER in my case) needed more permissions to access my new certificate, but none that spelled out the exact folders & registry keys like you did. Joe

  • Anonymous
    August 13, 2015
    The comment has been removed

  • Anonymous
    October 23, 2015
    Awesome, worked for me to! Thank you.

  • Anonymous
    November 16, 2015
    Awesome, worked well for me as well!!!!!!!!

  • Anonymous
    January 18, 2016
    This solution works. Saved me from going insane.