Forcing Encryption in SQL Server

If you’re ever planning to allow external connections to a SQL Server, one of the first things to ensure is that the data is encrypted at rest and in transit.

SQL Server has an option where we can force all connections on a SQL Server to be encrypted, which ensures us that we’re achieving encryption for our SQL connectivity.

This post follows Microsoft Docs – Enable Encrypted Connections to the Database engine. It’s quite a complex piece so a full read of this would be necessary before planning this change.

# Create Certificate (PowerShell)
# Import PK to Trusted Root Certificates
# Import Certificate into SQL Server
# Force Encryption in SQL Server
# Verify SQL Server Connectivity is Encrypted

Create Certificate (PowerShell)

The certificate must be issued for Server Authentication. The name of the certificate must be the fully qualified domain name (FQDN) of the computer.

The above is a little note in amongst a bunch of pertinent info, found here; Microsoft Docs – Enable Encrypted Connections – Remarks

I’m using a New-SelfSignedCertificate for this tutorial and creating it with PowerShell.

Before running the following PowerShell script, amend .pfk output file location & DNS Names.

New-SelfSignedCertificate -DnsName lab-sql1.whyte.net -CertStoreLocation cert:\LocalMachine\My -FriendlyName lab-sql1-cert -KeySpec KeyExchange -NotAfter (get-date).AddYears(99)
$thumbprint = $(Get-ChildItem Cert:\LocalMachine\My).thumbprint
$Pwd = ConvertTo-SecureString -String "Str0ngePassword1!" -Force -AsPlainText
Export-PfxCertificate -Cert "Cert:\LocalMachine\My\$thumbprint" -FilePath "C:\temp_certificates\lab-sql1pk.pfx" -Password $Pwd -Force

Import PK to Trusted Root Certificates

Open MMC and add the Certificates (Local Computer) Snap-in.

Expand Trusted Root Certification Authorities, right-click & select All Tasks > Import…

Navigate to the .pfx file.

Enter a password & continue.

Finish up the wizard.

Have a look at the new certificate within MMC.

Import Certificate into SQL Server

Open SQL Server Configuration Manager, then right-click Protocols for MSSQLSERVER and select Properties.

Open the Certificate tab and you should be able to view & select the new certificate from the drop-down menu.

Click to Apply & Ok out of the window – I’m doing the Forcing of Encryption separate (below).

You’ll get this prompt.

Any changes made will be saved, however, they will not take effect until the service is stopped and restarted

Restart the SQL Services from the configuration manager. If your services don’t start back up again, then ensure the service accounts have the appropriate permissions. For this demo, my AD Service Accounts are members of the local Administrators group.

Force Encryption in SQL Server

Right click Protocols for MSSQLSERVER, select Properties and within the Flags tab enable the Force Encryption option.

Restart the SQL Services.

Verify SQL Server Connectivity is Encrypted

Open a local and/or remote query session. You may have to ensure the Encrypt connection & Trust server certificate options are checked.

Now query sys.dm_exec_connections to check the encryption_option of all SQL connections.

SELECT * FROM sys.dm_exec_connections

SQL DBA Blog