Sql-server – How to generate a self-signed SSL certificate for MS SQL server 2008 R2 using OpenSSL

certificatesql serverssl

Using OpenSSL, I'd like to generate a self-signed certificate for usage with Microsoft SQL server 2008 R2. This might be unusual, but I'd like to learn what's going on behind the scenes.

I have several problems:

1) The certificate, after having it installed into the trusted root certificates of the local computer, is not listed in MSSC utility.

2) Therefore, I have forcibly added it by changing the registry as described in several posts. After having done so, the SQL server service didn't start any more. The event log shows

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

followed by other error messages which are undoubtedly caused by that first one.

I am nearly sure that the certificate I generated does not fulfill SQL server's requirements. However, for the life of me, I can't figure out what is missing. This leads to the third problem:

3) Many posts out there recommend the tool CheckSQLssl.exe to examine the certificate and to debug issues. However, I can't find that tool anywhere (not on my SQL server, no download available, even Microsoft doesn't seem to know about it).

These are the commands I am using to generate the certificate:

openssl genrsa -out key.pem 2048
openssl req -new -key key.pem -out csr.pem -subj /CN=server.example.com
openssl req -x509 -sha256 -days 36500 -key key.pem -in csr.pem -out cert.pem
openssl x509 -extfile /etc/ssl/pp-openssl.cnf -sha256 -days 36500 -signkey key.pem -in cert.pem -out ext.pem
openssl pkcs12 -export -out cert.pfx -inkey key.pem -in ext.pem

So I generate the key and a CSR which contains the server's FQDN as common name (lines 1 and 2). Then I sign the CSR and generate a self-signed X509 certificate (line 3). Then I add the EKU for Server Authentication (see below) (line 4) to the certificate and finally convert it into PFX format (line 5).

pp-openssl.cnf just contains one line to add the correct EKU:

extendedKeyUsage=critical,1.3.6.1.5.5.7.3.1

When examining that PFX certificate on the server, it seems that it should meet the requirements:

C:\Users\Administrator\Desktop>certutil -dump -v cert.pfx |find "EXC"
    KeySpec = 1 -- AT_KEYEXCHANGE
    Algorithm Class: 0xa000(5) ALG_CLASS_KEY_EXCHANGE

and (only relevant snippet of the output)

C:\Users\Administrator\Desktop>certutil -dump -v cert.pfx
    2.5.29.37: Flags = 1(Critical), Length = c
    Enhanced Key Usage
        Server Authentication (1.3.6.1.5.5.7.3.1)

I have read that the EKU and AT_KEYEXCHANGE are important requirements; both are obviously met.

What else could be missing? I'll provide the complete output of certutil -dump -v if it helps.

Best Answer

I managed to generate a self-signed certificate that works for encryption of connections to MS SQL Server by using the New-SelfSignedCertificate command for PowerShell.

Example:

New-SelfSignedCertificate -Type SSLServerAuthentication -Subject "CN=$env:COMPUTERNAME" -DnsName "$env:COMPUTERNAME",'localhost.' -KeyAlgorithm "RSA" -KeyLength 2048 -Hash "SHA256" -TextExtension "2.5.29.37={text}1.3.6.1.5.5.7.3.1" -NotAfter (Get-Date).AddMonths(36) -KeySpec KeyExchange -Provider "Microsoft RSA SChannel Cryptographic Provider"

Running it:

C:\mssql>notepad create-ss.ps1

C:\mssql>powershell
Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

PS C:\mssql> .\create-ss.ps1


   PSParentPath: Microsoft.PowerShell.Security\Certificate::LocalMachine\MY

Thumbprint                                Subject
----------                                -------
14151D156A16557AAA67A4C9465631520CC18626  CN=WIN-9U7N81SI03H