Secure Your SQL Server 2019: A Practical Guide to Enabling SSL/TLS Encryption

In today’s security-first IT environment, protecting database traffic is no longer optional. SQL Server often holds business-critical information such as customer records, financial data, application transactions, reports, and internal operational data. If connections between applications and SQL Server are not encrypted, sensitive information may be exposed while travelling across the network.

For organisations running Microsoft SQL Server 2019, enabling SSL/TLS encryption is one of the most important steps to strengthen database security. This guide explains what SQL Server SSL/TLS encryption is, why it matters, and how to enable it safely in a production environment.

What Does SSL/TLS Encryption Do in SQL Server?

SSL/TLS encryption protects the communication channel between SQL Server and client applications. This includes connections from:

Application servers
Web applications
Reporting tools
SQL Server Management Studio
Integration services
Third-party systems
Remote users and administrators

When encryption is enabled, data travelling between the client and SQL Server is encrypted in transit. This helps protect usernames, passwords, queries, result sets, and application data from being intercepted or read over the network.

Although people often still call it “SSL”, modern SQL Server encryption uses TLS, which is the newer and more secure protocol.

Why Enable SSL/TLS on SQL Server 2019?

Enabling SSL/TLS encryption provides several important benefits.

1. Protect Sensitive Data in Transit

Databases often contain confidential information. Encryption helps prevent attackers or unauthorised users from reading data packets if network traffic is captured.

2. Improve Compliance and Security Posture

Many industries require encrypted communication for systems handling personal, financial, medical, or business-critical data. TLS encryption helps support compliance with internal security policies and external regulatory requirements.

3. Secure Remote and Application Connections

Modern environments often include remote access, VPN connectivity, cloud-hosted applications, hybrid systems, and distributed infrastructure. Encrypting SQL Server connections reduces risk when traffic moves across different network zones.

4. Strengthen Customer and Business Trust

Customers and business stakeholders expect sensitive data to be handled securely. Enabling encryption is a practical and visible step toward stronger data protection.

Key Requirements Before Enabling SQL Server Encryption

Before configuring SSL/TLS on SQL Server 2019, make sure the certificate is suitable for SQL Server.

The certificate should meet the following requirements:

Requirement	Description
Certificate type	Server authentication certificate
Private key	The certificate must include a private key
Certificate store	Install under Local Computer certificate store
Subject or SAN	Must match the SQL Server name used by clients
Validity	Certificate must not be expired
Service account access	SQL Server service account needs read access to the private key
Key usage	Suitable for key exchange and server authentication

A common mistake is installing a certificate that looks valid but does not appear in SQL Server Configuration Manager. This usually happens when the certificate does not have a private key, does not include Server Authentication, is installed in the wrong store, or does not match the SQL Server hostname.

Step 1: Install the Certificate on the SQL Server Machine

Press Windows + R, then type:

certlm.msc

Go to:

Certificates - Local Computer
> Personal
> Certificates

Import the SSL/TLS certificate into this location.

After importing, open the certificate and confirm that it says:

You have a private key that corresponds to this certificate.

If the private key is missing, SQL Server will not be able to use the certificate for encryption.

Step 2: Grant SQL Server Access to the Certificate Private Key

SQL Server must be able to read the certificate’s private key.

In the certificate store:

Right-click the certificate.
Select All Tasks.
Select Manage Private Keys.
Add the SQL Server service account.
Grant Read permission.

Common SQL Server service accounts include:

NT Service\MSSQLSERVER
NT Service\MSSQL$InstanceName
DOMAIN\sqlserviceaccount

If this permission is missed, SQL Server may fail to start after the certificate is applied.

Step 3: Bind the Certificate to SQL Server 2019

Open SQL Server Configuration Manager as Administrator.

Navigate to:

SQL Server Network Configuration
> Protocols for MSSQLSERVER

For a named instance, select the relevant instance instead.

Then:

Right-click Protocols for MSSQLSERVER.
Select Properties.
Open the Certificate tab.
Select the correct certificate from the dropdown list.
Click OK.

If the certificate does not appear in the dropdown list, check the certificate requirements again. Most issues are related to missing private key, wrong certificate store, incorrect hostname, missing Server Authentication usage, or private key permission problems.

Step 4: Enable Force Encryption

To make SQL Server require encrypted connections, enable Force Encryption.

In SQL Server Configuration Manager, go to:

SQL Server Network Configuration
> Protocols for MSSQLSERVER
> Properties
> Flags

Set:

Force Encryption = Yes

Click OK.

Step 5: Restart the SQL Server Service

The certificate and encryption changes will not take effect until the SQL Server service is restarted.

In SQL Server Configuration Manager, go to:

SQL Server Services

Then restart:

SQL Server (MSSQLSERVER)

For a named instance, restart the relevant SQL Server instance.

This restart should be planned during a maintenance window, especially in production environments.

Step 6: Validate That Encryption Is Working

After SQL Server restarts, connect using SQL Server Management Studio and run:

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

If encryption is working, the result should show:

encrypt_option = TRUE

To check all active client connections, run:

SELECT 
    session_id,
    encrypt_option,
    auth_scheme,
    client_net_address
FROM sys.dm_exec_connections
WHERE client_net_address IS NOT NULL;

This allows DBAs to confirm whether existing application and user connections are encrypted.

Step 7: Update Application Connection Strings

Applications should be configured to use encrypted connections.

A recommended connection string setting is:

Encrypt=True;
TrustServerCertificate=False;

Example:

Server=sqlprd01.company.local,1433;
Database=ApplicationDB;
Encrypt=True;
TrustServerCertificate=False;
Integrated Security=True;

Using TrustServerCertificate=False ensures the client validates the SQL Server certificate properly.

For testing only, some teams use:

TrustServerCertificate=True

However, this bypasses certificate validation. It encrypts the connection but does not properly verify the server identity, so it should not be used as the preferred production configuration.

Common Issues When Enabling SQL Server SSL/TLS

Certificate Does Not Appear in SQL Server Configuration Manager

Possible causes:

Certificate is installed in the wrong certificate store
Certificate does not have a private key
Certificate is expired
Certificate does not include Server Authentication
Certificate subject or SAN does not match the SQL Server name
Certificate key type is not compatible

SQL Server Fails to Start After Applying the Certificate

Possible causes:

SQL Server service account cannot read the private key
Incorrect or corrupted certificate
Certificate was removed after being selected
Permission was applied to the wrong service account

Applications Cannot Connect After Force Encryption Is Enabled

Possible causes:

Client does not trust the certificate authority
Application uses an old SQL driver
Connection string does not support encryption properly
Certificate name does not match the server name used by the application

Error: Certificate Chain Was Issued by an Authority That Is Not Trusted

This usually means the client machine does not trust the CA that issued the SQL Server certificate.

The correct fix is to install the trusted root or intermediate CA certificate on the client servers or workstations.

Rollback Plan

Before enabling SSL/TLS encryption in production, always prepare a rollback plan.

If applications fail after enabling Force Encryption:

Open SQL Server Configuration Manager.
Go to SQL Server Network Configuration.
Open the properties of the SQL Server protocol.
Set Force Encryption back to No.
Restart the SQL Server service.

If SQL Server does not start after certificate binding, remove or replace the certificate configuration and confirm that the SQL Server service account has private key read permission.

Best Practices for Production

For a secure and stable production configuration:

Use a certificate issued by a trusted internal or public Certificate Authority.
Include all required SQL Server names in the Subject Alternative Name field.
Avoid using self-signed certificates in production.
Grant private key access only to the SQL Server service account.
Test the certificate on a non-production environment first.
Validate all application connection strings.
Plan a maintenance window for the SQL Server restart.
Monitor SQL Server error logs after enabling encryption.
Document the certificate expiry date and renewal process.

Secure Your SQL Server 2019: A Practical Guide to Enabling SSL/TLS Encryption