SQL Server – [SOLVED] “Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate."

TL;DR

Generate an appropriate certificate (see below) and plug it into SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for {instance_name} > Properties > Certificate > Certificate

Install Cert

TS;WM

I read through dozens of folks wrestling with this issue and various suggested remedies (reinstalling, sysprep, etc) yet I found none addressing the certificate error directly in this way … so it seems worth getting this message out there.

Root Cause

For me, my SQL Server 2008 R2 (v10.50.1600.1) install went wonky (ran into some group policy brick walls) and somehow the default self-signed certificate must’ve gotten wiped out. Interestingly, on other servers where the install ran without issue, this certificate entry is also blank… so that tells me we’re fortunate SQL Server is able to utilize this new one we throw in.

How to get an appropriate cert

One fairly straight shot at generating self-signed certs is with “SelfSSL.exe” from the IIS 6.0 Resource Kit Tools. Here’s example command line usage:

  • If you’re not already familiar with certs, the name following “/N:CN=” above must EXACTLY match the “network” name of the machine you’re installing it to… otherwise it gets hidden or rejected at various levels… for example, it won’t show up in SQL Server Configuration Manager’s certificate drop down list… this name should be the “FQDN” (Fully Qualified Domain Name) aka Canonical Name… typically the “Full Computer Name” as listed under Control Panel > System.
  • The /V option is the #days the cert is valid for… it appears 1999999 is the max allowed… that currently pushes expiration out to the year 7487, which will hopefully last ya 😉
  • Note: SelfSSL often spews “Error opening metabase: 0x80040154” … This would probably be bad news if you wanted to use this certificate for IIS SSL but apparently it’s not a factor for SQL Server SSL.
  • Examine the certificates that have been generated this way by launching CertMgr.msc from Win+R and looking into the “Personal” certificate store… or if that doesn’t exist, launch MMC.exe, CTRL+M to add the “Certificates” snap in and select “Computer account”.

Snap1

In a blatant attempt to cast a wide net on search hits, here’s a typical sql log that’ll be spewed along with the aforementioned error:

timestamp type message
2012-02-10 09:57:09.07 Server Initializing the FallBack certificate failed with error code: 1, state: 1, error number: -2146893802.
2012-02-10 09:57:09.08 Server Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.
2012-02-10 09:57:09.08 Server Error: 17182, Severity: 16, State: 1.
2012-02-10 09:57:09.08 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.
2012-02-10 09:57:09.08 Server Error: 17182, Severity: 16, State: 1.
2012-02-10 09:57:09.08 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.
2012-02-10 09:57:09.09 Server Error: 17826, Severity: 18, State: 3.
2012-02-10 09:57:09.09 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.
2012-02-10 09:57:09.09 Server Error: 17120, Severity: 16, State: 1.
2012-02-10 09:57:09.09 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.
  • Oz

    That’s brilliant!
    Thanks heaps for that.
    Didn’t even know where to start 🙂