Encryption

Without a TLS certificate, the server does not encrypt connections. The client then sends its password with the obfuscation of the TDS protocol only, which anyone on the network path can reverse, and every query and result travels in clear text. This is what SQL Server does too, and it is fine for a server that only listens on the loopback address, such as a test server. Configure a certificate for anything else.

When no certificate is configured, the server logs a warning at startup.

Configure a certificate

TLS is negotiated on the same endpoint as unencrypted connections, as SQL Server does: there is no separate port. The same endpoint also accepts Encrypt=Strict clients. Configure either a PFX file:

builder.AddTdsServer(options =>
{
    options.AddTcpListener(port: 14330, bindAddress: IPAddress.Loopback);
    options.TlsPfxPath = "certificates/server.pfx";
    options.TlsPfxPassword = "Password123!";
});

or a PEM certificate and its private key:

builder.AddTdsServer(options =>
{
    options.AddTcpListener(port: 14330, bindAddress: IPAddress.Loopback);
    options.TlsPemCertificatePath = "certificates/server.crt.pem";
    options.TlsPemPrivateKeyPath = "certificates/server.key.pem";
});

• The certificate must contain its private key.
• Relative paths are relative to the current directory of the process.
• Configure one source only: PFX and PEM options together are an error, and so is TlsPfxPassword without TlsPfxPath or one PEM path without the other.
• The certificate is loaded when the server starts, so a missing file or a wrong password fails at startup with an InvalidOperationException, a FileNotFoundException or a CryptographicException.
• Keep the PFX password out of the source code: read it from the configuration or a secret store.

TLS versions and strict encryption

The server tells the two kinds of encrypted clients apart by the first byte they send, so no option is needed:

• With Encrypt=Strict (TDS 8.0), the client starts with a TLS handshake, before any TDS message, and offers the tds/8.0 application protocol (ALPN). The operating system chooses the TLS version: TLS 1.3 where it supports it (Linux, Windows 11 and Windows Server 2022 and later), TLS 1.2 otherwise. SslStream does not support TLS 1.3 on macOS.
• With Encrypt=True or Encrypt=Optional, TLS is negotiated in the TDS PRELOGIN exchange and uses TLS 1.2, the version Microsoft.Data.SqlClient uses in this mode.

Encrypt=Strict is the setting Microsoft.Data.SqlClient recommends, and the only one with TLS 1.3. It always validates the certificate, even with TrustServerCertificate=True: trust a self-signed certificate with the ServerCertificate keyword, the path to a copy of the certificate (PEM or DER) that the client compares with the one the server presents.

Require encryption

With a certificate, the server still accepts clients that do not encrypt. Set RequireEncryption to reject them:

builder.AddTdsServer(options =>
{
    options.AddTcpListener(port: 1433, bindAddress: IPAddress.Any);
    options.TlsPemCertificatePath = "certificates/server.crt.pem";
    options.TlsPemPrivateKeyPath = "certificates/server.key.pem";
    options.RequireEncryption = true; // reject clients that do not encrypt
});

RequireEncryption needs a certificate: without one, the server fails to start.

What clients do

The Encrypt keyword of the client's connection string and the configuration of the server decide whether a connection is encrypted:

• Encrypt=True is the default of Microsoft.Data.SqlClient, so clients need Encrypt=Optional (or False) to connect to a server without a certificate.
• When the client does not ask for encryption, the server still encrypts the login packet, which holds the password, then continues without TLS, as SQL Server does.
• With Encrypt=Strict, the whole session is encrypted, the PRELOGIN exchange included.

The client also validates the certificate. A self-signed certificate is rejected unless the client trusts it, or sets TrustServerCertificate=True, which disables the validation and should be limited to tests. The name in the certificate must match the server name of the connection string, or the HostNameInCertificate of the client.