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
PowerShell New-SelfSignedCertificate
Certificate File Windows Explorer

Import PK to Trusted Root Certificates

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

MMC Local Computer
MMC Console

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

Trusted Root Certificate Import

Navigate to the .pfx file.

Import pfx File

Enter a password & continue.

Private Key Password

Finish up the wizard.

Certificate Import Wizard
Certificate Import Wizard

Have a look at the new certificate within MMC.

MMC

Import Certificate into SQL Server

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

SQL Server Protocols

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).

Protocols for MSSQLSERVER

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.

SQL Server Configuration Manager

Force Encryption in SQL Server

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

SQL Server Force Encryption

Restart the SQL Services.

SQL Server Service Restart

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.

SSMS Connection Properties

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

SELECT * FROM sys.dm_exec_connections
sys.dm_exec_connections

Comments

One response to “Forcing Encryption in SQL Server”

  1. Joe S Avatar
    Joe S

    I ran through this and it worked fantastic. Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *