SQL

Configure Encrypted SQL Server Connections

Last updated: August 29, 2026.

Encrypted SQL Server connections protect credentials, queries, and results while they travel between the application and database. A production connection should require encryption and verify that the certificate belongs to the intended server.

Require encryption in the connection string

Start by setting encryption explicitly in the application. Use the fully qualified DNS name covered by the server certificate.

// ADO.NET
Server=tcp:sql01.example.com,1433;Database=Sales;
Integrated Security=True;Encrypt=True;TrustServerCertificate=False;

// ODBC Driver for SQL Server
Driver={ODBC Driver 18 for SQL Server};Server=tcp:sql01.example.com,1433;
Database=Sales;Trusted_Connection=Yes;Encrypt=Yes;TrustServerCertificate=No;

TrustServerCertificate=False makes the client validate the certificate chain and server name. Setting it to True encrypts traffic but skips normal identity validation, so it should be limited to temporary troubleshooting or controlled development environments.

Configure the server certificate

Install a valid server-authentication certificate that SQL Server can access. Its subject or Subject Alternative Name must match the DNS name clients use, and clients must trust the issuing authority. The SQL Server service account also needs permission to read the private key.

On Windows, select the certificate in SQL Server Configuration Manager. Enable Force Encryption only after testing representative applications, jobs, and reporting tools. Restart SQL Server during a maintenance window for the network configuration to take effect.

For an availability group or failover cluster, install a suitable certificate on every participating replica or node. If clients connect through a listener or DNS alias, include that connection name in the certificate and test failover before enforcing encryption.

Verify the active connection

Run the following query through the application connection being tested:

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

encrypt_option should return TRUE. This confirms transport encryption; check the client configuration separately to confirm certificate validation.

Common connection failures

  • Certificate chain not trusted: install the required root and intermediate CA certificates on the client.
  • Target principal name is incorrect: connect using a DNS name included in the certificate.
  • Only some applications fail: compare driver versions, encryption defaults, and certificate stores.
  • SQL Server fails to start: inspect the error log and verify the certificate, private key, and service-account permissions.

Continue with restore a SQL Server backup and transfer SQL Server logins.

References: SQL Server encryption configuration and certificate requirements.

admin

admin

Related Posts