My goal is to implement encrypted connections on Test SQL Server instance
First, tried to create self-signed certificate in the Certificates snap in (certlm.msc), Personal > All Tasks > Request New Certificate…
But creation failed, because Test SQL Server machine could not contact (no network connection to) one of the AD servers on which AD Certificate Services are installed.
It can contact some other AD servers, but these do not have AD CS, possibly sysadmin will help to resolve it but not today.
So I moved on to "New-SelfSignedCertificate" PowerShell cmdlet, which can create self-signed certificates
Tried below ways for creating cert:
New-SelfSignedCertificate -Subject "TEST-SQL-SERVER.domain.com" -DnsName "TEST-SQL-SERVER.domain.com" -CertStoreLocation "cert:\LocalMachine\My"
New-SelfSignedCertificate -Subject "TEST-SQL-SERVER.domain.com" -DnsName "TEST-SQL-SERVER.domain.com","TEST-SQL-SERVER" -CertStoreLocation "cert:\LocalMachine\My"
New-SelfSignedCertificate -Subject "TEST-SQL-SERVER.domain.com" -DnsName "TEST-SQL-SERVER.domain.com","TEST-SQL-SERVER" -CertStoreLocation "cert:\LocalMachine\My" -TextExtension "2.5.29.37={text}1.3.6.1.5.5.7.3.1"
New-SelfSignedCertificate -Subject "TEST-SQL-SERVER.domain.com" -DnsName "TEST-SQL-SERVER.domain.com","TEST-SQL-SERVER" -CertStoreLocation "cert:\LocalMachine\My" -TextExtension "2.5.29.37={text}1.3.6.1.5.5.7.3.1" -KeyAlgorithm RSA -KeyLength 2048
New-SelfSignedCertificate -Subject "CN=TEST-SQL-SERVER.domain.com" -DnsName "TEST-SQL-SERVER.domain.com","TEST-SQL-SERVER" -CertStoreLocation "cert:\LocalMachine\My" -TextExtension "2.5.29.37={text}1.3.6.1.5.5.7.3.1" -KeyAlgorithm RSA -KeyLength 2048
New-SelfSignedCertificate -Subject "CN=TEST-SQL-SERVER.domain.com" -DnsName "TEST-SQL-SERVER.domain.com","TEST-SQL-SERVER" -CertStoreLocation "cert:\LocalMachine\My" -TextExtension "2.5.29.37={text}1.3.6.1.5.5.7.3.1" -KeyAlgorithm RSA -KeyLength 2048 -KeySpec KeyExchange
Each time after generating certificate, right clicked it in Certificates snap in, All Tasks > Manage Private Keys… and granted Read and Full Control permissions to SQL Server's service account
But, in the SQL Server Configuration Manager, each time when I go to SQL Server Network Configuration > Protocols for MSSQLSERVER > Properties, I can not see newly generated certificate on the Certificates tab
P.S. also tried adding "-KeySpec KeyExchange" to my PowerShell command, but Windows Security requests some smart card and I can't proceed further
Question: what I am missing ?
How to properly create self-signed certificate that will be visible in SQL Server Confirugation Manager ?
Best Answer
Answer from comment:
Try including
-Type SSLServerAuthentication
in theNew-SelfSignedCertificate
cmdlet to ensure the certificate is for Server Authentication which is a requirement for the SQL SSL Certificate. Also, check out this link for an example PowerShell script for generating a suitable self-signed cert