Encrypted Connections in SQL Server

2025.08.11

1. Introduction

The “TDE (at rest)” mentioned in the previous article is a feature that encrypts data stored on disk. This time, we will focus on a different layer — Encryption in Connections.

Why it matters: It is essential to protect authentication credentials, queries, and data exchanged over the network. Note that SQL Server login credentials are encrypted by default, but queries and result sets after login can be transmitted in plaintext unless encryption is explicitly enabled.

1-1. Requirements & Scope

  • SQL Server: On-premises SQL Server and Azure SQL (MI/DB) are both applicable.
  • Certificate: A server certificate with a private key in Local Computer > Personal, EKU = Server Authentication, and proper DNS names in SAN.
  • Ports: Default 1433 for default instance; named instances often use dynamic ports and SQL Browser (UDP 1434).

2. What is Connection Encryption?

By default, SQL Server does not encrypt query or data traffic, and sends/receives it in plaintext. To protect communication from “eavesdropping,” “tampering,” and “spoofing,” TLS (formerly SSL) encryption is required.

3. How Connection Encryption Works

When connecting, SQL Server uses a certificate to perform a TLS handshake and establish a secure session.

  • Login packets (username/password) are always encrypted.
  • Queries and result sets (data packets) are encrypted depending on the connection string and server settings.

4. Detailed Flow of Encrypted Communication

4-1. Overall Flow

  1. Client sends connection request (e.g., connection string with Encrypt=True or server-side Force Encryption enabled).
  2. Server presents its certificate (public key, CN/SAN, issuing CA, validity period, etc.).
  3. TLS handshake: negotiate cipher suite, key exchange (e.g., ECDHE), certificate validation.
  4. Generate session key (symmetric key): subsequent traffic is encrypted with a symmetric algorithm (e.g., AES).
  5. Encrypted session established: queries/results are exchanged over TDS (Tabular Data Stream) in encrypted form.

4-2. Key Points for Certificate Validation

  • Signed by a trusted CA? (Self-signed works for testing only.)
  • Target hostname (FQDN) matches CN or SAN DNS entries?
  • Still within validity period and not revoked?
  • Has the Server Authentication EKU; private key is present and readable by the SQL Server service account?

Note: Using TrustServerCertificate=True bypasses validation. Skipping validation increases the risk of man-in-the-middle attacks, so in production environments set this to False (validate).

4-3. ASCII Diagram (Concept)

[Client]                              [Server]
   |  TCP Connection Request              |
   |--------------------------------------->|
   |  Encryption Request (Encrypt=True or ForceEnc) |
   |--------------------------------------->|
   |  Server Certificate Sent              |
   |<---------------------------------------|
   |  Certificate Validation (CA, FQDN, Expiry)    |
   |--------------------------------------->|
   |  Cipher Suite Negotiation              |
   |<---------------------------------------|
   |  Key Exchange (e.g., ECDHE)            |
   |<-------------------------------------->|
   |  Session Key Generation                |
   |<-------------------------------------->|
   |  TLS Session Established               |
   |<======================================>|
   |  Query / Data Transmission (Encrypted) |
   |<======================================>|

5. How to Configure Encryption

5-1) Modify the Application Connection String

The easiest way is to require encryption in the client’s connection string.

  • Basic: Encrypt=True; TrustServerCertificate=False;
  • Meaning:
    • Encrypt=True: Require TLS encryption.
    • TrustServerCertificate=False: Validate the server certificate (recommended).

ADO.NET (C#) example:

var conn = new SqlConnection(
  "Server=myServer;Database=myDB;User Id=myUser;Password=myPass;" +
  "Encrypt=True;TrustServerCertificate=False;"
);

JDBC example:

jdbc:sqlserver://myServer:1433;database=myDB;encrypt=true;trustServerCertificate=false;

ODBC example (connection string):

Driver={ODBC Driver 18 for SQL Server};Server=myServer,1433;Database=myDB;
Encrypt=Yes;TrustServerCertificate=No;UID=myUser;PWD=myPass;

5-2) Configure “Force Encryption” on the SQL Server Side

  1. Prepare a server certificate (trusted CA or self-signed for testing).
  2. Open SQL Server Configuration Manager → “SQL Server Network Configuration” → “Protocols” for the target instance → Certificate tab to select the certificate.
  3. In the Flags tab, set Force Encryption to Yes.
  4. Restart the SQL Server service to apply changes.

With this setting, the server forces encryption even if the client does not request it. Non-TLS-capable clients will be unable to connect.

5-3) Use Drivers/Environments with Encryption Enabled by Default

  • ODBC Driver 18+: In many environments, encryption is enabled by default.
  • OLE DB Driver 19+: Similar to ODBC Driver 18+, it defaults to encrypted connections in many configurations.
  • Azure SQL Database / Azure SQL Managed Instance: Designed with encryption enabled by default and recommended for all connections.

In all cases, explicitly specify the handling of TrustServerCertificate to control whether the certificate is validated, ensuring a balance between security and compatibility.

5-4) SSMS & sqlcmd quick recipes

SSMS: Connection Properties → “Encrypt connection” (on). For strict validation, ensure the server name matches the certificate and that the CA is trusted.

sqlcmd (classic):

:: Windows auth
sqlcmd -S myserver.contoso.com,1433 -d mydb -E -N

:: SQL auth (test-only if bypassing validation)
sqlcmd -S myserver.contoso.com,1433 -d mydb -U myuser -P "myp@ss" -N -C

go-sqlcmd:

# Enforce encryption
sqlcmd -S myserver.contoso.com,1433 -d mydb -U myuser -P 'myp@ss' -N true

# Test-only: bypass validation
sqlcmd -S myserver.contoso.com,1433 -d mydb -U myuser -P 'myp@ss' -N true --trust-server-certificate

6. How to Check if the Connection is Encrypted

Run the following SQL in the same session to verify:

SELECT encrypt_option
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;
  • encrypt_option = TRUE → Encrypted
  • encrypt_option = FALSE → Plaintext

All sessions view (handy in prod):

SELECT session_id, client_net_address, encrypt_option, protocol_type, local_tcp_port
FROM sys.dm_exec_connections
ORDER BY session_id;

6.5 Viewing the Difference in Wireshark (Encrypted vs Unencrypted)

The effect of encryption can be easily verified using a network analysis tool such as Wireshark.

When Encryption is Disabled

  • SQL Server communicates using the Tabular Data Stream (TDS) protocol.
  • If encryption is disabled, Wireshark will show queries and result data in plaintext within TDS packets.
  • Queries after login (e.g., SELECT * FROM Customers) and portions of the result can be seen directly as ASCII text.

When Encryption is Enabled

  • TDS packets are encapsulated within TLS payloads and appear as Application Data.
  • Query strings and result sets are not visible; only binary encrypted data is shown.
  • Even in Wireshark’s “Follow TCP Stream,” the content appears as unreadable encrypted data.

Capture Example (Concept)

Without Encryption:

 

With Encryption:

Wireshark filters (quick reference)

tcp.port == 1433                  # default instance
udp.port == 1434                  # SQL Browser (named instances)
tcp.dstport == 1433 || tcp.dstport == 15000  # combine multiple ports
ip.addr == 192.168.1.50 && tcp.port == 1433  # narrow by host

Note: Named instances often use dynamic TCP ports; confirm with
SELECT local_net_address, local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID;

7. Troubleshooting

  • “The certificate chain was issued by an authority that is not trusted.”
    Import the issuing CA chain to the Trusted Root store (Local Computer), or use a publicly trusted CA.
  • “The certificate’s CN name does not match the passed value.”
    Use FQDN in the connection string that matches the certificate’s CN/SAN.
  • Force Encryption is enabled and clients fail to connect
    Update client drivers to support TLS; verify CA trust and hostname validation.
  • No certificate appears in Configuration Manager
    Ensure the cert with a private key is in Local Computer > Personal and EKU includes Server Authentication.

8. Summary and Notes

  • Verify in a test environment before applying to production.
  • Enabling Force Encryption requires TLS-capable and properly configured clients.
  • Always validate certificates (TrustServerCertificate=False) to reduce man-in-the-middle risk.
  • Combine with Always Encrypted and TDE to protect both data at rest and data in transit.