Configuring SQL Server to Use TLS Certificates

2018-09-06 · mssql, security, tls

The first time an auditor asked me about encryption in transit, I had to admit that SQL Server only encrypts the login packet by default. Queries and result sets cross the network in clear text unless the client asks for encryption, and even then the server uses a self-signed certificate nobody can verify. If traffic leaves the trusted server VLAN, or someone with a checklist is asking, you want a real certificate bound to the instance and encryption forced.

Most failures come from the certificate, not SQL Server, so get that right first. It needs to come from a CA your clients trust (internal AD CS is fine), with Enhanced Key Usage including Server Authentication (1.3.6.1.5.5.7.3.1), a Subject or Subject Alternative Name matching the FQDN clients connect with, and an exportable private key, and it has to sit in the local computer's Personal store. The service account needs read permission on that key. Plan a restart, so a maintenance window.

I'm on SQL Server 2016 or 2017 here. Older versions need specific updates for TLS 1.2 (2012 SP3 and 2014 SP2 include it), and clients need Native Client 11 with the TLS 1.2 update or ODBC Driver 13.

Step 1: Import the certificate and grant key access (Windows)

Import the PFX into the computer store:

Import-PfxCertificate -FilePath C:\certs\sql01.contoso.local.pfx `
    -CertStoreLocation Cert:\LocalMachine\My `
    -Password (ConvertTo-SecureString 'PfxPassword' -AsPlainText -Force)

Then grant the service account read access to the private key: open certlm.msc, right-click the certificate, All Tasks → Manage Private Keys..., add the SQL Server service account (for example CONTOSO\svc_sql or NT Service\MSSQLSERVER) with Read permission.

Step 2: Select the certificate in SQL Server Configuration Manager

Open SQL Server Configuration Manager → SQL Server Network Configuration, right-click Protocols for MSSQLSERVER → Properties. On the Certificate tab pick the certificate from the drop-down. On the Flags tab set Force Encryption to Yes. Restart the SQL Server service.

If the certificate does not appear in the drop-down, it fails one of the requirements above; the most common cause is the Subject not matching the machine's FQDN. For an Availability Group listener or a cluster you cannot use the GUI at all, because the drop-down only shows certificates matching the local computer name. In that case write the thumbprint directly to the registry:

$thumb = (Get-ChildItem Cert:\LocalMachine\My | Where-Object Subject -like '*sql01.contoso.local*').Thumbprint.ToLower()
$key = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer\SuperSocketNetLib'
Set-ItemProperty -Path $key -Name Certificate -Value $thumb
Set-ItemProperty -Path $key -Name ForceEncryption -Value 1
Restart-Service MSSQLSERVER -Force

The thumbprint must be lowercase with no spaces. Replace MSSQL13 with MSSQL14 for SQL Server 2017.

Step 3: Configure the certificate on Linux

On SQL Server 2017 for Linux, copy the certificate and key in PEM format and point mssql-conf at them:

sudo mkdir -p /var/opt/mssql/ssl
sudo cp sql01.contoso.local.pem /var/opt/mssql/ssl/mssql.pem
sudo cp sql01.contoso.local.key /var/opt/mssql/ssl/mssql.key
sudo chown mssql:mssql /var/opt/mssql/ssl/mssql.*
sudo chmod 600 /var/opt/mssql/ssl/mssql.*

sudo /opt/mssql/bin/mssql-conf set network.tlscert /var/opt/mssql/ssl/mssql.pem
sudo /opt/mssql/bin/mssql-conf set network.tlskey /var/opt/mssql/ssl/mssql.key
sudo /opt/mssql/bin/mssql-conf set network.tlsprotocols 1.2
sudo /opt/mssql/bin/mssql-conf set network.forceencryption 1
sudo systemctl restart mssql-server

The private key must not be password protected.

Step 4: Turn off old protocols on Windows (optional)

I do this, but only after checking the clients. SQL Server uses the Schannel settings of the operating system. To refuse TLS 1.0 and 1.1 on Windows Server 2012 R2 or 2016:

foreach ($p in 'TLS 1.0','TLS 1.1') {
    $k = "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\$p\Server"
    New-Item -Path $k -Force | Out-Null
    Set-ItemProperty -Path $k -Name Enabled -Value 0
    Set-ItemProperty -Path $k -Name DisabledByDefault -Value 1
}

This affects every Schannel-based service on the machine and needs a reboot. Make sure all clients have drivers that speak TLS 1.2 first: .NET Framework 4.6 or later, ODBC Driver 11 with the TLS 1.2 update or Driver 13/17, JDBC 4.2 or later.

Is it really encrypted?

Connect and check what the connection is actually using:

SELECT session_id, encrypt_option, protocol_type, auth_scheme, client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

encrypt_option must say TRUE. Then, from a client, connect with encryption required and certificate validation on, so the certificate chain is actually verified:

sqlcmd -S sql01.contoso.local -E -N -Q "SELECT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID"

-N requests encryption; without -C (trust server certificate) sqlcmd validates the chain, so a successful connection proves the certificate is trusted. In the SQL Server error log you should also see The certificate [Cert Hash(sha1) "..."] was successfully loaded for encryption.

When it goes wrong

Unable to load user-specified certificate [Cert Hash(sha1) "..."]. The server will not accept a connection. You should verify that the certificate is correctly installed. The service account has no read permission on the private key, or the thumbprint in the registry contains a hidden character. Re-do Step 1 and retype the thumbprint by hand.
The target principal name is incorrect. / The certificate chain was issued by an authority that is not trusted. Client-side validation failed: the name in the connection string does not match the certificate, or the issuing CA is not in the client's Trusted Root store. Connect by FQDN, not by IP or short name.
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. Protocol mismatch, usually a client that only speaks TLS 1.0 after you disabled it. Update the driver.
The certificate drop-down is empty in Configuration Manager. The certificate has no private key, is in the user store instead of the computer store, or lacks the Server Authentication EKU. Check with certlm.msc.

Put the certificate expiry date in your calendar. SQL Server will happily load an expired certificate, and then every client that validates it will refuse to connect.