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:
- Start > SQL Server Configuration Manager
- SQL Server Network Configuration > Protocols for MSSQLSERVER (Properties)
- Uncheck General > Force Encryption
- 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.
Best Answer
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...