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 MS Docs: Enable Encrypted Connections to the Database engine. This is quite a complex change so a full read of the official docs is necessary before planning this change.

Hopefully my steps in this post help configuring forced encryption to your SQL Server. The following is included in this one:
> Create Certificate for SQL Server
> Import PK to Trusted Root Certificates
> Import Certificate into SQL Server
> Force Encryption in SQL Server
> Verify SQL Server Connections are Encrypted

Create Certificate for SQL Server

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 note in amongst a bunch of pertinent info, found in the MS Docs for this.

I’m using a New-SelfSignedCertificate for this tutorial, creating the certificate with PowerShell .

Before running the following PowerShell script, amend .pfk output file location and DNS Names to your own.

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

1. Open Microsoft Management Console (MMC), and add the Certificates (Local Computer) Snap-in.

2. Expand Trusted Root Certification Authorities.

3. Right-click and select All Tasks > Import.

4. Navigate to the .pfx file.

5. Enter a password & continue.

6. Finish up the wizard.

7. Have a look at the new certificate within MMC to verify.

Import Certificate into SQL Server

1. Open SQL Server Configuration Manager.

2. Right-click Protocols for MSSQLSERVER and select Properties.

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

4. Click to Apply & Ok out of the window – I’m doing the Forcing of Encryption separate next.

You’ll get this prompt.

Now, 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

To force connections to be encrypted in SQL Server:

1. In SQL Server Configuration Manager, right-click Protocols for MSSQLSERVER and select Properties.

2. Within the Flags tab, enable Force Encryption.

3. Restart the SQL Services.

Verify SQL Server Connections are Encrypted

Now that we have configured our SQL Server to force connections to be encrypted, lets verify that’s the case.

1. Open SSMS and connect to the SQL Server.
You may now have to ensure the Encrypt connection & Trust server certificate options are checked!

2. Once connected, query the sys.dm_exec_connections table and check the encryption_option column for all the incoming SQL connections.
Checkout the MS Docs as linked for more information on the meaning behind the information displayed below.

-- check if connections are encrypted (encrypt_option col)
SELECT * 
FROM sys.dm_exec_connections

Hope this guide was good info for you. Feel free to comment below if you have any questions.