LLI Wiki — My Digital Knowledge Base

SQL Server: TLS certificate with PowerShell and certutil

Create, check, convert and import a TLS certificate for SQL Server on Windows.

Create, check, convert and import a TLS certificate for SQL Server on Windows: self-signed certificate with PowerShell, certificate issued by an enterprise certification authority (CA), checks, format conversions and import.

1. Self-signed certificate with PowerShell

The certificate is created in the machine store, with the FQDN and the short computer name as DNS names.

$Fqdn = [System.Net.Dns]::GetHostEntry('').HostName

$cert = New-SelfSignedCertificate `
 -Type SSLServerAuthentication `
 -Subject "CN=$Fqdn" `
 -DnsName @(
 $env:COMPUTERNAME,
 $Fqdn,
 "localhost"
 ) `
 -KeyAlgorithm RSA `
 -KeyLength 2048 `
 -HashAlgorithm SHA256 `
 -KeySpec KeyExchange `
 -Provider "Microsoft RSA SChannel Cryptographic Provider" `
 -CertStoreLocation "Cert:\LocalMachine\My" `
 -NotAfter (Get-Date).AddYears(3)

$cert

2. Certificate issued by an enterprise CA

List the certificate templates available, then request a certificate with the template MSSQLServer (the common name of the "MS-SQL Server" template).

certutil -Template
certreq -enroll -machine MSSQLServer

3. Find the certificate to use

Get-ChildItem Cert:\LocalMachine\My |
Select-Object Subject, Thumbprint, NotAfter, HasPrivateKey
$cert = Get-ChildItem Cert:\LocalMachine\My |
Where-Object Thumbprint -eq "THUMBPRINT"

Also check the SQL Server service account: it must be able to read the private key of the certificate.

Get-CimInstance Win32_Service |
Where-Object Name -like 'MSSQL*' |
Select-Object Name, StartName

4. Check the certificate

Check PowerShell / Windows OpenSSL
Show everything certutil -v -store My THUMBPRINT openssl x509 -in cert.pem -text -noout
CN / Subject $cert.Subject openssl x509 -in cert.pem -subject -noout
SAN $cert.Extensions | ? {$_.Oid.Value -eq "2.5.29.17"} | % {$_.Format($true)} openssl x509 -in cert.pem -ext subjectAltName -noout
EKU $cert.EnhancedKeyUsageList openssl x509 -in cert.pem -ext extendedKeyUsage -noout
Dates $cert | Select NotBefore,NotAfter openssl x509 -in cert.pem -dates -noout
SHA1 $cert.Thumbprint openssl x509 -in cert.pem -fingerprint -sha1 -noout
SHA256 $cert.GetCertHashString("SHA256") openssl x509 -in cert.pem -fingerprint -sha256 -noout
Private key $cert.HasPrivateKey openssl pkey -in key.pem -check
RSA size $cert.GetRSAPublicKey().KeySize openssl x509 -in cert.pem -text -noout
Provider / KeySpec certutil -v -store My THUMBPRINT Windows only

For SQL Server, the expected values are:

CN / SAN    = host name / FQDN
EKU         = Server Authentication
Private Key = True
RSA         = 2048 bits or more
KeySpec     = AT_KEYEXCHANGE
Provider    = Microsoft RSA SChannel Cryptographic Provider

Verify the certificate chain from Windows, or the TLS presentation of a server:

certutil -f -urlfetch -verify C:\temp\server.crt
openssl s_client -showcerts -connect sqlserver01.example.com:443

5. Formats

Format Content Private key
.cer / .crt Public certificate No
.pem Certificate, chain or key Possible
.key Private key Yes
.pfx / .p12 Certificate, private key and chain Yes
.der Binary certificate No
.csr Certificate request No

OpenSSL conversions:

# PFX -> certificate
openssl pkcs12 -in cert.pfx -clcerts -nokeys -out cert.pem

# PFX -> private key
openssl pkcs12 -in cert.pfx -nocerts -out key.pem

# PEM -> PFX
openssl pkcs12 -export -out cert.pfx -inkey key.pem -in cert.pem

# DER -> PEM
openssl x509 -inform DER -in cert.cer -out cert.pem

# PEM -> DER
openssl x509 -outform DER -in cert.pem -out cert.der

6. Import into Windows

# Certificate only
Import-Certificate `
  -FilePath ".\cert.cer" `
  -CertStoreLocation "Cert:\LocalMachine\My"

# Certificate with its private key
Import-PfxCertificate `
  -FilePath ".\cert.pfx" `
  -CertStoreLocation "Cert:\LocalMachine\My"

Graphical interface: certlm.msc, then Personal > Certificates > All Tasks > Import.

7. Renew an expired certificate

certreq -enroll -machine -q -cert "<SN>" renew reusekeys
certreq -enroll -machine -q -cert "<SN>" renew

The first command reuses the private key, the second one generates a new key. <SN> is the serial number of the certificate.

8. OID reference

OID Name Use
2.5.29.17 Subject Alternative Name (SAN) DNS, IP or other names associated with the certificate
2.5.29.15 Key Usage Signature, encryption, key encipherment, etc.
2.5.29.37 Extended Key Usage (EKU) Lists the allowed application purposes
1.3.6.1.5.5.7.3.1 Server Authentication TLS authentication of a server
1.3.6.1.5.5.7.3.2 Client Authentication TLS authentication of a client
1.3.6.1.5.5.7.3.3 Code Signing Code signing
1.3.6.1.5.5.7.3.4 Email Protection S/MIME, mail signature and encryption
2.5.29.19 Basic Constraints Tells whether the certificate is a CA or an end-entity certificate
2.5.29.14 Subject Key Identifier Identifies the public key of the certificate
2.5.29.35 Authority Key Identifier Identifies the key of the CA that signed the certificate

Notes

Mastodon LLIMastodon SocialFosstodon