SQL Server now supports Transport Layer Security (TLS) to encrypt data transmitted between clients and the server. Using TLS ensures that your data remains secure and protected from eavesdropping.
This guide explains how to create a suitable certificate, bind it to SQL Server, and verify that TLS encryption is working.
1. Requirements for the Certificate
Your SQL Server must have a certificate that meets the following criteria:
- FQDN Matching: The certificate’s name must match the fully qualified domain name (FQDN) of your SQL Server.
- Server Authentication: The certificate must be issued for Server Authentication.
- Key Specification: Use
AT_KEYEXCHANGE(notAT_SIGNATURE) to allow SQL Server to securely exchange encryption keys with client computers. - Trusted Authority: Sign your server certificate with your Enterprise Certificate Authority (CA), which should already be trusted and installed on all client machines.
Using a trusted CA avoids warnings on client machines and ensures secure communication.
2. Creating a Server Certificate (PowerShell Example)
Administrators can create a certificate for SQL Server using PowerShell. This example creates a self-signed certificate that:
- Authenticates your SQL Server
- Provides an encryption key for secure communication
- Is valid for 3 years
- Allows the key to be exported
<# Create a certificate signed by our Enterprise Certificate Authority to authentify your SQL server and give it an encryption key it can exchange with your computers to encrypt the connections. Make it valid for 3 years and make that key exportable so you can install it on your server#>
$certSQLServerEncrypt = New-SelfSignedCertificate -Subject “YOURSQLSRV.YOURDOMAIN.local” -KeySpec KeyExchange -type SSLServerAuthentication -CertStoreLocation “cert:\LocalMachine\my” -NotAfter (Get-Date).AddYears(3) -KeyExportPolicy Exportable -Signer $certEnterpriseCA
Tip: Replace
YOURSQLSRV.YOURDOMAIN.localwith your SQL Server’s actual FQDN.
If you don’t have an Enterprise CA, you can create a self-signed certificate like this:
New-SelfSignedCertificate `
-Subject "YOURSQLSRV.YOURDOMAIN" `
-DnsName "YOURSQLSRV.YOURDOMAIN","YOURSQLSRV" `
-CertStoreLocation "cert:\LocalMachine\My" `
-KeySpec KeyExchange `
-KeyExportPolicy Exportable `
-NotAfter (Get-Date).AddYears(3) `
-FriendlyName "SQL Server TLS Certificate" `
-TextExtension @("2.5.29.37={text}1.3.6.1.5.5.7.3.1")
After creating the certificate, ensure the SQL Server service has read access to the private key. To do this, follow these steps:
- Open MMC (Microsoft Management Console).
- Add the Certificates snap-in for Local Computer.
- Navigate to Certificates (Local Computer) → Personal → Certificates, then right-click your certificate.
- Select All Tasks → Manage Private Keys…
- In the Security tab, add the SQL Server service. Usually, it is named like
NT Service\MSSQL$SQL2022(for SQL Server 2022). - Allow Full Control and Read rights for the service.
3. Binding the Certificate to SQL Server
Once the certificate is created:
- Open SQL Server Configuration Manager.
- Navigate to SQL Server Network Configuration → Protocols for [Your Instance].
- Right-click Properties → Certificate and select your new certificate.
- Under Flags, set Force Protocol Encryption (old flag: Force Encryption) to
Yesif you want all connections to use TLS. - Restart the SQL Server service to apply the changes.
4. Recommended TLS Versions
- Use TLS 1.2 or 1.3, as older versions (TLS 1.0/1.1) are considered insecure.
- Ensure both server and clients support the selected TLS version.
You can verify or disable TLS versions in Windows using registry settings or group policies.
5. Verifying the TLS Configuration
After binding the certificate, you can verify TLS encryption with:
SQL Query:
SELECT session_id, encrypt_option
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;
encrypt_optionshould returnTRUEfor an encrypted connection.
PowerShell:
Get-ChildItem -Path Cert:\LocalMachine\My
- Ensure your certificate appears in the personal store and is valid.
6. Client Trust and Deployment
- Ensure client machines trust your Enterprise CA.
- If using self-signed certificates without a trusted CA, clients may show warnings or fail to connect.
7. Renewal and Expiration
- Monitor your certificate’s expiration date.
- Renew or replace the certificate before it expires to avoid connection failures.
- Export the certificate and key if needed for deployment on other servers.
Summary
Enabling TLS on SQL Server protects your data and ensures secure communication. By following this guide, you will:
- Create a properly configured certificate
- Bind it to SQL Server
- Enforce modern TLS standards
- Verify encrypted connections
Sources:
- Microsoft: Enable encrypted connections to the Database Engine
- Microsoft: Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager)
- SQLShack: How to set and use encrypted SQL Server connections
- Stackoverflow: SSL Certificate missing from dropdown in SQL Server Configuration Manager627/ssl-certificate-missing-from-dropdown-in-sql-server-configuration-manager
