Enable encryption for SQL Server connections detailed

Step by step details to enable encryption for SQL Server connections. All connections can be forced to be encrypted using certificates. CA generated certificates are ideal. To enable encrypted connections to a SQL Server we have to use certificates. There are multiple ways to create a certificate

• Use self signed certificate
• Use CA signed certificate
• Buy certificate from a third party

We have to make sure the certificate use Server Authentication template. Also the cname (CN aka Common Name in the certificate properties) is set to FQDN of the database server. In case of a cluster server make sure to have the Windows cluster virtual name FQDN as cname & node names as Alternative names.

Here am guessing, you have the server connected to AD and your windows administrator provide setup a CA server at the AD level. We do not want to use self signed certificate, as its not a best practice.

Certificate Installation steps

1. Login to SQL Server machines and connect to Certificate manager MMC

2. Make sure to select Manage Computer Certificates, & not Manage User Certificate MMC.

3. Click on Personal -> All Tasks -> Request New Certificate

4. Click Next

5. Leave the default option (Its AD enrolled) , Click Next

6. Tick Computer, and click Enroll

• Note1:- In case of a cluster server, this is where you have to expand the Details and enter the values as mentioned in the starting notes.
• Note2:- The template is shown as Computer here, it will be shown as Server Authentication generally, as the template is configured by your admin using Server Authentication template.

7. Click Finish. That’s all, the certificate is created by the CA, and you can check the properties to confirm.

8. You can see the certificate here

9. Now give Read permission for SQL Service account to the certificate as follows. Right click certificate -> All Tasks -> Manage Private Keys

10. Now Force encryption for SQL Server Instance & select the certificate we just created from the dropdown.

11. Restart SQL Server and you can see all connections are now encrypted. To verify, use below query and you will see encrypt_option column has TRUE for all sessions.

select session_id,encrypt_option from sys.dm_exec_connections

Note. In case if you do not see the certificate name in the dropdown box, you might have to enter the certificate hash in to the system registry. This is always the case with cluster servers. To achieve that follow these steps.

1. Double click on the certificate and go to Details. Now copy the Thumbprint.

2. Open Registry Editor (regedit.msc) and go to path: HKLM:\SOFTWARE\Microsoft\Microsoft SQLServer\MSSQL15.MSSQLSERVER\MSSQLServer\SuperSocketNetLib.

And enter the copied thumbprint value here. Make sure to delete empty spaces.

You can also get the thumbprint using Powershell

(Get-ChildItem -Path Cert:\LocalMachine\My | Where-Object {$_.subject -match "dbhost.example.com"})

Extra: If you need to create a simple self signed certificate for testing, use the below Powershell. This will generate the certificate and save it in C drive.

$CertificateName = "DBHOST.EXAMPLE.COM"
New-SelfSignedCertificate -DnsName $CertificateName -CertStoreLocation cert:\LocalMachine\My -FriendlyName $CertificateName-SQLServerCert -NotAfter (get-date).AddYears(99)
$Thumbprint = (Get-ChildItem -Path Cert:\LocalMachine\My | Where-Object {$_.FriendlyName -match "SQLServerCert"}).Thumbprint;
Get-Item -Path Cert:\LocalMachine\My\$thumbprint | Export-Certificate -Type CERT -FilePath C:\$CertificateName.cer -Verbose

Note: The imported certificate should be send to application team, so they can install in the app and connections will now be encrypted.