Problem Starting SQL Server: Unable to Initialize SSL Support

Today I ran into a bit of a surprise trying to start a SQL server up from a reboot. The server was working before the reboot so I had a bad feeling that this issue might be difficult. The first thing I did is check the SQL server log to see what happened.

2014-08-23 02:09:44.63 Server      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 

                Dec 28 2012 20:23:12 

                Copyright (c) Microsoft Corporation

                Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

2014-08-23 02:09:44.63 Server      (c) Microsoft Corporation.

2014-08-23 02:09:44.63 Server      All rights reserved.

2014-08-23 02:09:44.63 Server      Server process ID is 3704.

2014-08-23 02:09:44.63 Server      System Manufacturer: 'VMware, Inc.', System Model: 'VMware Virtual Platform'.

2014-08-23 02:09:44.63 Server      Authentication mode is WINDOWS-ONLY.

2014-08-23 02:09:44.63 Server      Logging SQL Server messages in file 'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG'.

2014-08-23 02:09:44.63 Server      The service account is 'DOMAIN\databaseservice'. This is an informational message; no user action is required.

2014-08-23 02:09:44.63 Server      Registry startup parameters: 

                 -d E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf

                 -e E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG

                 -l E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

                 -T 8295

                 -T 4199

2014-08-23 02:09:44.63 Server      Command Line Startup Parameters:

                 -s "MSSQLSERVER"

2014-08-23 02:09:44.77 Server      SQL Server detected 1 sockets with 2 cores per socket and 2 logical processors per socket, 2 total logical processors; using 2 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

2014-08-23 02:09:44.77 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

2014-08-23 02:09:44.77 Server      Detected 8191 MB of RAM. This is an informational message; no user action is required.

2014-08-23 02:09:44.77 Server      Using conventional memory in the memory manager.

2014-08-23 02:09:44.82 Server      This instance of SQL Server last reported using a process ID of 4828 at 23/08/2014 2:05:00 AM (local) 22/08/2014 6:05:00 PM (UTC). This is an informational message only; no user action is required.

2014-08-23 02:09:44.82 Server      Node configuration: node 0: CPU mask: 0x0000000000000003:0 Active CPU mask: 0x0000000000000003:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

2014-08-23 02:09:44.83 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.

2014-08-23 02:09:44.84 spid4s      Starting up database 'master'.

2014-08-23 02:09:44.93 Server      CLR version v4.0.30319 loaded.

2014-08-23 02:09:44.97 Server      Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.

2014-08-23 02:09:44.99 spid4s      3 transactions rolled forward in database 'master' (1:0). This is an informational message only. No user action is required.

2014-08-23 02:09:45.00 spid4s      0 transactions rolled back in database 'master' (1:0). This is an informational message only. No user action is required.

2014-08-23 02:09:45.21 Server      Software Usage Metrics is enabled.

2014-08-23 02:09:45.29 spid4s      SQL Server Audit is starting the audits. This is an informational message. No user action is required.

2014-08-23 02:09:45.29 spid4s      SQL Server Audit has started the audits. This is an informational message. No user action is required.

2014-08-23 02:09:45.32 spid4s      SQL Trace ID 1 was started by login "sa".

2014-08-23 02:09:45.42 spid4s      Server name is 'SERVERNAME'. This is an informational message only. No user action is required.

2014-08-23 02:09:45.43 spid14s     The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030e. Check certificates to make sure they are valid.

2014-08-23 02:09:45.43 spid14s     Error: 26014, Severity: 16, State: 1.

2014-08-23 02:09:45.43 spid14s     Unable to load user-specified certificate [Cert Hash(sha1) "A2489BCC04B0E33E4564C32D59E758B9E672280C"]. 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-08-23 02:09:45.43 spid14s     Error: 17182, Severity: 16, State: 1.

2014-08-23 02:09:45.43 spid14s     TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property. 

2014-08-23 02:09:45.43 spid4s      Failed to verify Authenticode signature on DLL 'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\ftimport.dll'.

2014-08-23 02:09:45.43 spid14s     Error: 17182, Severity: 16, State: 1.

2014-08-23 02:09:45.43 spid14s     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-08-23 02:09:45.43 spid14s     Error: 17826, Severity: 18, State: 3.

2014-08-23 02:09:45.43 spid14s     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-08-23 02:09:45.43 spid14s     Error: 17120, Severity: 16, State: 1.

2014-08-23 02:09:45.43 spid14s     SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Luckily this wasn't so bad, if I look through the log the following error popped out:

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

I took a quick look at the certificate store for the machine and noticed that the SQL Server identification certificate was missing. To browse the certificate store for the machine launch MMC.EXE, select File then Add/Remote Snap-in...

Select Certificates then Add.

Select Computer Account then Next.

Click Finish.

Expand the Certificates node, expand Personal and select Certificates.

Below is a screenshot of the certificate that should be present.

To regenerate the certificate open the IIS 7 control panel and select the server then double click Server Certificates.

On the right hand side of the screen select Create Self-Signed Certificate.

Enter in the FQDN of the local server.

You should see a certificate for your server name and the Issued By field should match.

Open SQL Configuration Manager, expand the SQL Server Network Configuration node then right click Protocols for MSSQLSERVER. Select Properties to continue.

Select the Certificate tab and use the drop down to select the self-signed certificate you created. You can double check this by making sure the Issued By field matches the server name.

The following warning should appear, click OK.

Select the SQL Server  Services node, right click SQL Server and select Start.

If everything goes as planned your SQL server should be up an running. Also if you wish you can opt for a different certificate if you have PKI infrastructure in place but I kept this post simple since the certificate that comes with your Configuration Manager 2012 installed on top of SQL server is self-signed.

Take a look at the following article if you want to use a PKI to deploy a better certificate for this purpose.

http://www.jamesbannanit.com/2011/04/certificate-requirements-for-sccm-2012/