Skip to content

Powershell script to generate SQL Server TLS certificate (self-signed)

I just wanted to dump this script somewhere. It generatess a self-signed TLS certificate which is compatible with SQL Server. The requirements are:

  • Does not use RSA-PSS signature cause JDBC (Java8) clients can't verify them (yet)
  • Allows to set CN and SAN for various server names (defaults to computer name)
  • Uses classic CryptoAPI CSP not a new CNG KSP, since SQL Server / SChannel cant access those keys (would be good because of the extra key isolation)
  • Therefore uses RSA not ECDSA
  • Makes the key not exportable, should be run on SQL Server and machine must be backedup (you can always generate a new certificate so dont accept export risk
# Create Self Signed RSA Cert for SQL Server usage
# 
# Customize:
# + -Subject should contain hostname (or virtal name for FCI)
# + -FriendlyName is anything which helps you to recognize the key
# + -DnsName should list all variants (FQDN) of hostnames used by clients (VIP+Machines)
# + -NotAfter set expire accoring to your policy
# + (Non)Exportable is more secure but harder to manage
#
# - Using RSASSA-PSS (-AlternateSignatureAlgorithm) does not work with Java 8 clients:
#    Caused by: java.security.NoSuchAlgorithmException: 1.2.840.113549.1.1.10 Signature not available
# - Using CNG (Software KSP, Platform KSP) does not work with SQL Server
# - No ECDSA possible since CNG KSP is used
# - sets extended key usage id-kp-serverAuth
New-SelfSignedCertificate -Type SSLServerAuthentication `
    -Subject "CN=$env:COMPUTERNAME" -FriendlyName 'SQL Server RSA2048 G1' `
    -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) `
    -KeyExportPolicy NonExportable -KeySpec KeyExchange `
    -Provider 'Microsoft RSA SChannel Cryptographic Provider' `
    -CertStoreLocation Cert:\LocalMachine\My `
| fl -Property Thumbprint,FriendlyName,DnsNameList,NotAfter,PrivateKey,SerialNumber,Subject,Issuer

Write-Warning 'You need to open MMC "Manage Machine Certificates", select new cert in "Personal > Certificates"'
Write-Warning 'and specify "All Tasks > Manage private Keys...". Add MSSQL service login (NT Service\MSSQL$INST) with READ.

(Gist)

After generating the certificate manual steps to give service user access is needed (I would love to scrpt this with powershell too, but it seems to require deeper COM magic)

Trackbacks

Keine Trackbacks

Kommentare

Ansicht der Kommentare: Linear | Verschachtelt

Noch keine Kommentare

Kommentar schreiben

BBCode-Formatierung erlaubt
Umschließende Sterne heben ein Wort hervor (*wort*), per _wort_ kann ein Wort unterstrichen werden.
Die angegebene E-Mail-Adresse wird nicht dargestellt, sondern nur für eventuelle Benachrichtigungen verwendet.
Um einen Kommentar hinterlassen zu können, erhalten Sie nach dem Kommentieren eine E-Mail mit Aktivierungslink an ihre angegebene Adresse.

Um maschinelle und automatische Übertragung von Spamkommentaren zu verhindern, bitte die Zeichenfolge im dargestellten Bild in der Eingabemaske eintragen. Nur wenn die Zeichenfolge richtig eingegeben wurde, kann der Kommentar angenommen werden. Bitte beachten Sie, dass Ihr Browser Cookies unterstützen muss, um dieses Verfahren anzuwenden.
CAPTCHA