Skip to content

Instantly share code, notes, and snippets.

@ecki
Created March 8, 2019 15:38
Show Gist options
  • Save ecki/5b922ecc41680c8ea683145e551eb86e to your computer and use it in GitHub Desktop.
Save ecki/5b922ecc41680c8ea683145e551eb86e to your computer and use it in GitHub Desktop.
Powershell to generate self-signed SQL Server TLS certificate
# 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.'
@ecki
Copy link
Author

ecki commented Apr 11, 2024

Hm odd, I think I tested it on server.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment