SQL Server TLS Certificate Configuration: Encrypting Connections and Replacing Expired Certificates

SQL Server encrypts every connection when a certificate is configured — but if no certificate is installed, SQL Server generates a self-signed one at startup that is only trusted by the local machine. Remote clients get the “self-signed certificate” warning (or more dangerously, they silently accept it). This article covers installing a proper TLS certificate, configuring SQL Server to use it, forcing all connections to be encrypted, and replacing an expiring certificate without service interruption.

How SQL Server TLS works

SQL Server uses TLS for the login packet exchange (always) and optionally for the entire session. The certificate must be in the Windows Certificate Store (not a file on disk) in LOCAL MACHINE\Personal. The SQL Server service account must have read access to the certificate’s private key.

SQL Server version and TLS support:

Version	TLS 1.2	TLS 1.3
SQL Server 2012	With patch	No
SQL Server 2014/2016	Yes	No
SQL Server 2019	Yes	No (Windows only limitation)
SQL Server 2022	Yes	Yes (with Windows Server 2022)

Step 1: Obtain a certificate

The certificate must meet these requirements:

• Subject or SAN must match the fully qualified hostname clients use to connect (e.g., sqlserver.example.com)
• Enhanced Key Usage (EKU) must include Server Authentication (1.3.6.1.5.5.7.3.1)
• Key length: 2048-bit RSA minimum (4096 recommended)
• The certificate must be in LOCAL MACHINE\Personal in the Windows Certificate Store

Option A — Request from an internal Active Directory CA:

Open certmgr.msc → Personal → Certificates → right-click → All Tasks → Request New Certificate. Select the Computer certificate template. Make sure the CN or SAN matches the server’s FQDN.

Option B — Import a PEM certificate from Let’s Encrypt or an external CA:

Convert to PFX (PKCS#12) first:

# On Windows, import the PFX
$password = ConvertTo-SecureString "PFXpassword" -AsPlainText -Force
Import-PfxCertificate -FilePath "C:\certs\sqlserver.pfx" `
  -CertStoreLocation "Cert:\LocalMachine\My" `******

Or from the OpenSSL tools:

openssl pkcs12 -export \
  -in /etc/letsencrypt/live/sqlserver.example.com/fullchain.pem \
  -inkey /etc/letsencrypt/live/sqlserver.example.com/privkey.pem \
  -out sqlserver.pfx \
  -passout pass:YourPFXPassword

Then copy sqlserver.pfx to the Windows server and import it.

Step 2: Grant the SQL Server service account access to the private key

After importing the certificate, the SQL Server service account must be able to read the private key.

1. Open certlm.msc (Local Machine certificates).
2. Navigate to Personal → Certificates.
3. Find your certificate, right-click → All Tasks → Manage Private Keys.
4. Click Add, type the SQL Server service account name (e.g., NT SERVICE\MSSQLSERVER or DOMAIN\sqlsvc).
5. Grant Read permission.
6. Click OK.

To do this via PowerShell:

$thumbprint = "YOUR_CERTIFICATE_THUMBPRINT"
$cert = Get-Item "Cert:\LocalMachine\My\$thumbprint"
$rsaKey = [System.Security.Cryptography.X509Certificates.RSACertificateExtensions]::GetRSAPrivateKey($cert)
$keyPath = [System.IO.Path]::Combine($env:ALLUSERSPROFILE, "Microsoft\Crypto\RSA\MachineKeys", $rsaKey.Key.UniqueName)

$acl = Get-Acl $keyPath
$rule = New-Object System.Security.AccessControl.FileSystemAccessRule("NT SERVICE\MSSQLSERVER", "Read", "Allow")
$acl.AddAccessRule($rule)
Set-Acl $keyPath $acl

Step 3: Configure SQL Server to use the certificate

Open SQL Server Configuration Manager (search for it in Start menu, or SQLServerManager16.msc):

1. Expand SQL Server Network Configuration.
2. Click Protocols for MSSQLSERVER (or your instance name).
3. Right-click the Protocols node → Properties.
4. Go to the Certificate tab.
5. Select your certificate from the dropdown.
6. On the Flags tab, set Force Encryption to Yes if you want to require TLS for all connections.
7. Click OK.

Via PowerShell (SQL Server 2019+):

# Find the thumbprint
Get-ChildItem Cert:\LocalMachine\My | Where-Object {$_.Subject -like "*sqlserver.example.com*"} | Select-Object Thumbprint, Subject

# Set the certificate (replace with your instance registry path)
$regPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\SuperSocketNetLib"
Set-ItemProperty -Path $regPath -Name "Certificate" -Value "YOUR_CERTIFICATE_THUMBPRINT_LOWERCASE"
Set-ItemProperty -Path $regPath -Name "ForceEncryption" -Value 1

Note: The thumbprint in the registry must be all lowercase with no spaces. The thumbprint shown by certlm.msc may have a leading invisible character — copy it via PowerShell ($cert.Thumbprint.ToLower()) to avoid this trap.

Step 4: Restart SQL Server

Restart-Service MSSQLSERVER
# or for a named instance:
Restart-Service "MSSQL$INSTANCENAME"

Check the SQL Server error log for confirmation:

The certificate [Thumbprint: ...] was successfully loaded for encryption.

If you see:

Unable to load user-specified certificate [Cert Hash(sha1) "..."]

Go back and verify:

• The certificate is in LocalMachine\Personal (not CurrentUser).
• The service account has Read access to the private key.
• The thumbprint in the registry is correct and lowercase.

Step 5: Verify encryption from a client

Using SQL Server Management Studio (SSMS):

After connecting, run:

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

encrypt_option = TRUE confirms the session is encrypted.

Using sqlcmd:

sqlcmd -S sqlserver.example.com -U sa -P password -Q "SELECT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID" -N

The -N flag requests encryption. The -C flag trusts the server certificate without verification — do not use -C in production.

Checking which certificate is in use:

SELECT c.session_id, c.encrypt_option, c.net_transport,
       cert.name AS certificate_name,
       cert.expiry_date
FROM sys.dm_exec_connections c
CROSS APPLY (
    SELECT TOP 1 name, expiry_date
    FROM sys.certificates
    WHERE thumbprint = (
        SELECT CONVERT(varbinary(20), CONVERT(varchar(40), SERVERPROPERTY('CertificateThumbprint')), 2)
    )
) cert
WHERE c.session_id = @@SPID;

Step 6: Configure clients to trust the certificate

When Force Encryption is ON, clients must trust the server certificate. The options:

Option A — Install the server’s CA certificate in Trusted Root Certification Authorities on every client machine (recommended).

Option B — In the connection string, specify TrustServerCertificate=False and Encrypt=True. This forces the client to validate the certificate using the Windows certificate store.

Connection string example:

Server=sqlserver.example.com;Database=mydb;User Id=appuser;******;Encrypt=True;TrustServerCertificate=False;

JDBC:

jdbc:sqlserver://sqlserver.example.com:1433;databaseName=mydb;encrypt=true;trustServerCertificate=false;trustStore=/path/to/truststore.jks

Replacing an expiring certificate

The hardest part of SQL Server certificate rotation is doing it without dropping connections. Here is the safest procedure:

1. Import the new certificate and grant the service account read access to its private key (Steps 1 and 2 above).
2. Verify the new certificate is visible in certlm.msc and has the correct CN/SAN.
3. Open SQL Server Configuration Manager, select the new certificate, click OK.
4. Schedule a maintenance window and restart the SQL Server service.
5. Verify via sys.dm_exec_connections that new connections use the updated certificate.
6. Revoke or archive the old certificate.

There is no hot-reload for SQL Server TLS certificates — a service restart is always required. Plan accordingly.

Monitoring certificate expiry

Create a SQL Agent job or a monitoring query to alert on certificate expiry:

-- Run this from your monitoring system
SELECT
    session_id,
    encrypt_option,
    -- Get the server cert expiry via xp_instance_regread
    NULL AS placeholder
FROM sys.dm_exec_connections
WHERE session_id = 1;

-- Or query the certificate directly if it's in the DB store
SELECT name, expiry_date, DATEDIFF(day, GETDATE(), expiry_date) AS days_remaining
FROM sys.certificates
WHERE DATEDIFF(day, GETDATE(), expiry_date) < 60
ORDER BY expiry_date;

For the server-level certificate (Windows cert store), use PowerShell monitoring:

$cert = Get-ChildItem Cert:\LocalMachine\My | Where-Object {$_.Subject -like "*sqlserver.example.com*"}
$daysLeft = ($cert.NotAfter - (Get-Date)).Days
if ($daysLeft -lt 60) {
    Write-Warning "SQL Server certificate expires in $daysLeft days"
}

SQL Server on Linux (SQL Server 2017+)

On Linux, SQL Server reads the certificate from a file rather than the Windows Certificate Store. Edit /var/opt/mssql/mssql.conf:

[network]
tlscert = /etc/ssl/mssql/server.crt
tlskey  = /etc/ssl/mssql/server.key
tlsprotocols = 1.2,1.3
forceencryption = 1

Set permissions:

chown mssql:mssql /etc/ssl/mssql/server.crt /etc/ssl/mssql/server.key
chmod 600 /etc/ssl/mssql/server.key
chmod 644 /etc/ssl/mssql/server.crt

Restart:

sudo systemctl restart mssql-server

Troubleshooting

Error	Cause	Fix
The certificate chain was issued by an authority that is not trusted	Client does not trust the CA	Install CA cert in client’s Trusted Root store
A connection was successfully established with the server, but then an error occurred during the pre-login handshake	Certificate not found or no private key access	Check thumbprint registry entry; check private key ACL
Unable to load user-specified certificate	Wrong thumbprint format or cert not in LocalMachine\My	Verify thumbprint is lowercase hex; check store location
Connections fail after cert import, before restart	SQL Server still using old cert	Restart the service
TrustServerCertificate=False causes connection failure	CA cert not trusted on client	Install CA cert on client or use group policy

Summary

SQL Server TLS configuration is done through SQL Server Configuration Manager (Windows) or mssql.conf (Linux). On Windows, the certificate lives in the Windows Certificate Store and the SQL Server service account must have read access to the private key. Set Force Encryption = Yes to reject unencrypted connections. Clients should use Encrypt=True;TrustServerCertificate=False with the server’s CA in the trusted store. Certificate replacement requires a service restart — plan a maintenance window and keep the old certificate importable until the new one is confirmed working.