How to generate a self signed certificate for SQL Server with New-SelfSignedCertificate

20 June,2022 by Tom Collins

Use the Powershell cmdlet New-SelfSignedCertificate

Warning: I only ever use this method for testing purposes - never use for Production environments. A typical test would be for  SQL Server Network Encryption SSL Certificate Management

Encryption strategy  should be part of a wider review of Database Security Countermeasures against hacker attacks

For more details check the Microsoft documentation but here are some notes on some of the parameters  attached to the Powershell cmdlet - New-SelfSignedCertificate.

Explanations of the parameters used below on the New-SelfSignedCertificate

-Type SSLServerAuthentication which is the default
-Subject Specifies the string that appears in the subject of the new certificate

-DnsName Specifies one or more DNS names to put into the subject alternative name extension of the certificate when a certificate to be copied is not specified via the CloneCert parameter

-KeyAlgorithm Specifies the name of the algorithm that creates the asymmetric keys that are associated with the new certificate. The other available is ECDSA

-KeyLength - the length of the key in bits associated with the new certificate

-NotAfter specified the certificate expiration date

 

 

New-SelfSignedCertificate -Type SSLServerAuthentication -Subject "CN=$env:COMPUTERNAME" -FriendlyName "SQL Server Test self-signed" -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(24) -KeySpec KeyExchange -Provider "Microsoft RSA SChannel Cryptographic Provider"-CertStoreLocation "Cert:\LocalMachine\My"


Result Output: 

PSParentPath: Microsoft.PowerShell.Security\Certificate::LocalMachine\My

Thumbprint Subject
---------- -------
337989CF4B7V1501682B582C000382A08F8DA97E CN=myServer

 

This process will allow you to create & export a self signed certificate in Personal Information Exchange (.pfx) format. 

One of the Powershell command parameters used is  -CertStoreLocation  , which adds the new certificate to the CertStore Location. So , if we want to use this certificate for SQL Server SSL , we'll need to export the certificate from the designated store 

These steps will result in a pfx file - which you can then use to bind to SQL Server.   Before importing the pfx file to SQL Server check How to check SSL encryption is enabled on SQL Server with Powershell     & How to check a SQL Server connection is encrypted with SSL

Step 1 : To view the certificate go to Manage computer certificates | Personal | Certificates . 

Step 2: Right Click on the certificate choose “All Tasks” —> “Export…”

Step 3: Welcome to the Certificate Export Wizard - Click "Next"

Step 4: Export Private Key - Choose "Yes,export the private key" 

Step 5: Choose - Personal Information Exchange - PKCS #12(.PFX)

Step 6: Security - choose password and select the    encryption : AES256-SHA256

 

Ssl_security

Step 7: Specify the filename you want

Step 8: Go through to completion - and the pop up window "The export was successfull" 

 

Now you have a self-signed certificate , which you can use to configure SQL Server to support TLS connections

 

Read more on SQL Server TLS

TLS , SQL Server and powershell cmdlet - Get-TLSCipherSuite

Connection failed - SQL Server Error 772 - TCPIP Socket

 

 

 


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to generate a self signed certificate for SQL Server with New-SelfSignedCertificate


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer