1

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 ?

2
  • 1
    Try including -Type SSLServerAuthentication in the New-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
    – HandyD
    Commented Feb 26, 2020 at 23:19
  • @HandyD it worked! had to remove "$env:" from the script but everything else works just fine. If you post this solution as an answer, I will accept it Commented Feb 27, 2020 at 19:13

1 Answer 1

2

Answer from comment:

Try including -Type SSLServerAuthentication in the New-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

1
  • Thanks HandyD! do you know if there a way to check if my connection is using SSL or TLS 1.2 ? Commented Feb 28, 2020 at 6:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.