Today, now more than ever we need to safeguard our data, especially when allowing external connections to a SQL Server. We need to ensure fundamental measures are in-place to guarantee data security is enforcing encryption both at rest and in transit.

This guide walks you through the process of forcing encryption on all connections to your SQL Server, ensuring robust encryption protocols are in place for SQL connectivity.

To begin, it’s essential to understand that enabling encrypted connections involves several intricate steps. Referencing Microsoft Docs – Enable Encrypted Connections to the Database engine is advisable for a better understanding before proceeding with the following instructions.

How to Force Encryption in SQL Server

1. Create Certificate

Generate a certificate using PowerShell, ensuring it is issued for Server Authentication and named with the fully qualified domain name (FQDN) of the computer.

# Create new self-signed certificate for mssql
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 Create Self-Signed Certificate

2. Import Certificate PK File to Trusted Root Certificates

Use the MMC console to import the certificate into the Trusted Root Certification Authorities.

Locate the PFX certificate file and import:

3. Import Certificate into SQL Server

Open SQL Server Configuration Manager to import the certificate, making it available for encryption. Right click Properties on Protocols for MSSQLSERVER as shown below, and navigate to the Certificate tab in the prompted window.

SQL Server Configuration Manager Import Certificate

Valid certificates will show within the drop-down list. You should verify the certificate expiry date here too.

4. Enable Force Encryption in SQL Server

Enable the ‘Force Encryption’ option within SQL Server Protocols to mandate encryption for all connections.

5. Restart the SQL Server Service

For the encryption configuration changes to be applied to the SQL Server, we must restart the SQL Service.

SQL Server Configuration Manager Restart Service

6. Verify SQL Server Sessions are Encrypted

Now that the SQL Server is encrypted with a certificate, we need to ensure the Trust Server Certificate checkbox is ticked in SQL Server Management Studio.

Once connected, we can ensure all connected sessions in SQL are encrypted, by checking connection properties and querying the sys.dm_exec_connections view.

Forcing Encryption in SQL Server