2

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.

0

1 Answer 1

5

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

Certificate details

Use of the certificate:

enter image description here

Giving access to the certificate private key is required as well.

Find the account used to run MS SQL Server:

Find SQLSERVER Service Logon As

Now from Management Console, Certificates Add-on, Select Manage Private Keys

MMC / Certificates /  / All Tasks / Manage Private Keys

Add the user running MS SQL

 Add the user running MS SQL

Restart MS SQL Server and you should be done.

5
  • Great answer. But... the CommonName (CN) MUST match the Server Name - which it will in your script due to the use of $env:COMPUTERNAME but this will error on a client connecting to the server via IP or domain name e.g sql.contoso.co.uk with the certificate installed: The target principal name is incorrect.. Is there a solution to this?
    – Leigh
    Jun 13, 2022 at 16:27
  • 1
    According to someone "Using a DNS name in the Common Name field is deprecated by both the IETF and the CA/Browser forums. Put your DNS names in the Subject Alt Names (SAN). There can be multiple SANs. " stackoverflow.com/questions/23523456/… Jun 14, 2022 at 22:05
  • How do I know which key generated the certificate? Mar 24, 2023 at 20:33
  • @rafaelmaldonado not trivial... it seems you have to export it by right-clicking on it from the microsoft management console -> digicert.com/blog/…. Mar 28, 2023 at 15:40
  • 1
    Giving the SQL service account read access on the private key was the critical step. Thanks.
    – HackSlash
    May 1, 2023 at 21:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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