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
- The CA configuration commands (CRL flags, CA store views) are not included: they belong to the CA administrator.
- Host names and common names are examples to replace.