7

This morning it transpired that SQL Server 2012 service running on Win2012 had shut itself down. When I logged in as the server admin and tried to access SSMS, after entering my credentials I got the 'transport' error. On checking Windows Services, both SQL Server and Agent were stopped. I tried to start them, but got a timeout error.

The SQL event log contains this entry:

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.

The Windows event logs contain these entries:

Log Name: Application

Source: MSSQLSERVER

Date: 14/03/2013 09:01:40

Description: Unable to load user-specified certificate [Cert Hash(sha1) "A1CF6EE1C352B7A185950EB400013269759C24BD"]. 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.

Description: TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.

Description: 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.

Description: 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.

Description: SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

The following procedure fixed the problem:

  1. Start > SQL Server Configuration Manager
  2. SQL Server Network Configuration > Protocols for MSSQLSERVER (Properties)
  3. Uncheck General > Force Encryption
  4. Start > Services > Start MSSQLSERVER service

Update:

The above only fixed the problem until the next reboot, whereby it happened again. Apparently the SSL certificate needs permissions to be read by SQL2012, but I daren't keep rebooting until this gets fixed, so am accessing via RDC now instead of SSMS over SSL.

Improve this question
9
  • Certificate subject is "elea1.memset.net" and the FDQN of SQL Server is "elea1" (well that's what's at the top of the Object Explorer - please advise if I can get more info elsewhere). We've never connected to SSMS externally even though it was set up to use SSL.
    – EvilDr
    Commented Mar 14, 2013 at 9:51
  • Is SQL host machine joined to memset.net domain? Had the certificate worked previously or it never worked?
    – Remus Rusanu
    Commented Mar 14, 2013 at 9:57
  • 2
    BTW, SQL will not 'shut itself down' on a cert issue. It won't start. So probably there was a restart involved, eg. a overnight patch was applied and required a restart. The event log will show this.
    – Remus Rusanu
    Commented Mar 14, 2013 at 10:02
  • Yes this is looking like what's happened. For info, the option to force encryption was set two months ago, with a fresh self-signed cert applied to the next dialog option. There was never a problem until this morning, so perhaps it never worked and we weren't aware until today...?
    – EvilDr
    Commented Mar 14, 2013 at 13:49
  • This blog post thesqldude.com/2011/08/03/… mentions adding the SQL Server user group account to the cert's permission list, however, it doesn't like the instructions are quite valid for Win2012 as I don't see that user group in my options. My SQL Server runs under the NT Service\MSSQLSERVER account, so perhaps I should give that read-permissions on the certificate under the 'Private Keys' option?
    – EvilDr
    Commented Mar 14, 2013 at 13:52

3 Answers 3

Reset to default
4

There are applications out there that change the ACLs on the machine keyset container folder. When this occurs, legitimate keyset users loose access to these keysets. See KB 278381: Default permissions for the MachineKeys folders for the correct permission set.

If you want absolute confirmation, see BUG: You cannot enable encryption by using a certificate when SQL Server 2005 is running under the Network Service account for a 'workaround' (it involves compiling a C++ app that will reset the ACLs properly...). But you can simply set the ACLs according to mentioned KB278381 and see if the problem dissapear.

More related KBs:

I am yet to find which application changes the ACLs. My money is on some antivirus apps. Don't ask me how I know about all this random pieces of disparate infos...

Improve this answer
2

I used a solution mentioned here. It worked for me.

  1. Check the SQL Server Service Account name.
  2. Check and verify the Certificate name. (It is configured in SQL Server Configuration Manager --> SQL Server Network Configuration --> Protocols for MSSQLSERVER --> Right CLick here and go to Properties --> In 'Certificate' tab, you can see a Certificate name.)
  3. Go to 'RUN', type 'MMC', and press 'Enter'.
  4. Go to 'File' --> Add/Remove Snap-in.
  5. Select 'Certificate' and click on the 'Add' button. Select appropriate options (from 'My User Account / Service Account / Computer Account').
  6. Click on the 'OK' button on the 'Add or Remove Snap-ins' window screen.
  7. On the 'Console1' window, go to the 'Console Root' option.
  8. Expand 'Certificates' --> Personnel --> Certificates.

Here you will see the list of certificates.

  1. Go to the certificate (which is configured in Step 2)
  2. Right-click on that and go to 'All Tasks' --> Manage Private Keys...
  3. Click on the 'Add' button and add the SQL Service Account here (from Step 1). Give all permission to this account.
  4. Now, try to start the SQL Services.
Improve this answer
0

I had a situation similar to this. Even though the SQL Server Configuration Manager showed that Force Encryption was "No" and the certificate page was clear, I continued to get this error and was unable to start the service at all.

In the registry (for my installation):

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib

The Certificate String had the value equal to the hash in the error message. Removing that value worked for me.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.