ITBlog

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)