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

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

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