Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Mar 14 '13 at 12:29

This question came from our site for professional and enthusiast programmers.

    
what is the subject of the certificate, and what is the FQDN name of the SQL Server instance? –  Remus Rusanu Mar 14 '13 at 9:45
    
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 Mar 14 '13 at 9:51
    
Is SQL host machine joined to memset.net domain? Had the certificate worked previously or it never worked? –  Remus Rusanu Mar 14 '13 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 Mar 14 '13 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 Mar 14 '13 at 13:49
show 3 more comments

2 Answers

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

share|improve this answer
add comment

In addition to the stuff that Remus mentioned I can see this happening if the cryptography service had crashed. Does rebooting the server fix the problem?

share|improve this answer
    
No. Rebooting actually caused the problem as at this point the SSL data could not be read and SQL Server refused to start. I actually stopped using this SSL cert so never properly resolved the problem, although I do see that permission needs to be given on the cert for the SSL engine to read it, however, without detailed instructions for Win2012 I'm not brave enough to try it. I can't afford to have SQL shut down in case it fails :-\ –  EvilDr May 14 '13 at 7:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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