TLS/SSL Encryption

cTDS relies on FreeTDS for all network communication, including TLS/SSL encryption. Encryption is configured through the FreeTDS configuration file (freetds.conf), not through the k_ctds.connect() call. This page explains how to set up encrypted connections for common scenarios.

Note

Pre-built wheels (installed via pip install k-ctds) already bundle FreeTDS compiled with OpenSSL 3.0 — no additional setup is needed for TLS/SSL support.

If you built FreeTDS from source, make sure you compiled it with the --with-openssl flag. See Getting Started for details.

Encryption Modes

FreeTDS supports four encryption modes, set via the encryption directive in freetds.conf:

Mode	Behavior
`off`	Encryption is disabled. The connection will fail if the server requires encryption.
`request`	Encryption is used if the server supports it. This is the default for TDS versions 7.1 and above.
`require`	Encryption is mandatory. The connection will fail if the server does not support encryption.
`strict`	Like `require`, but uses TDS 8.0 and forces certificate validation regardless of other settings. Requires FreeTDS 1.4+.

Basic Encrypted Connection

For most SQL Server instances that support (but do not require) encryption, the default request mode is sufficient and no configuration changes are needed.

To explicitly require encryption, add the encryption directive to your server entry in freetds.conf:

[myserver]
host = db.example.com
port = 1433
tds version = 7.4
encryption = require

Then connect normally:

import k_ctds

with k_ctds.connect('myserver', user='myuser', password='mypassword') as conn:
    with conn.cursor() as cursor:
        cursor.execute(
            "SELECT encrypt_option FROM sys.dm_exec_connections "
            "WHERE session_id = @@SPID"
        )
        print(cursor.fetchone()[0])  # Should print 'TRUE'

Connecting to Azure SQL Database

Azure SQL Database requires encrypted connections. Add the following to your freetds.conf:

[azure]
host = yourserver.database.windows.net
port = 1433
tds version = 7.4
encryption = require

Then connect as usual:

import k_ctds

with k_ctds.connect(
    'azure',
    user='youruser@yourserver',
    password='yourpassword',
    database='yourdatabase'
) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT 1")
        print(cursor.fetchone()[0])

Certificate Validation

By default, FreeTDS does not validate the server’s TLS certificate. This means any certificate (including self-signed) is accepted. For production environments, you should enable certificate validation.

[myserver]
host = db.example.com
port = 1433
tds version = 7.4
encryption = require
ca file = /etc/ssl/certs/ca-certificates.crt
check certificate hostname = yes

The following certificate-related options are available:

Option	Description
`ca file`	Path to a PEM file containing root CA certificates used to validate the server certificate. Set to `system` to use the operating system’s default trust store. When omitted, any certificate is accepted.
`crl file`	Path to a certificate revocation list file. Only used when `ca file` is also set.
`check certificate hostname`	Whether to verify the server hostname matches the certificate. Defaults to `yes` when `ca file` is set.
`certificate hostname`	Override the expected hostname in the certificate. Only used when `ca file` is set. Defaults to the `host` value.

Note

If the server uses a self-signed certificate (common in development environments), omit the ca file directive to skip validation. Never do this in production.

Legacy Servers (TLS 1.0)

Some older servers (such as Windows Server 2008) only support TLS 1.0, which is disabled by default in modern FreeTDS builds. To connect to these servers:

[legacy-server]
host = oldbox.example.com
port = 1433
tds version = 7.1
encryption = require
enable tls v1 = yes

Warning

TLS 1.0 has known security vulnerabilities and should not be used in production. Upgrade the server if at all possible.

Configuration File Locations

FreeTDS searches for its configuration file in the following order:

The path set by the FREETDSCONF environment variable
~/.freetds.conf (user-specific)
The system-wide freetds.conf (typically /usr/local/etc/freetds.conf or /etc/freetds/freetds.conf, depending on how FreeTDS was installed)

You can use the FREETDSCONF environment variable to point to a custom configuration file without modifying system files:

export FREETDSCONF=/path/to/my/freetds.conf
python myapp.py

Verifying Encryption

To confirm that your connection is encrypted, query the sys.dm_exec_connections system view:

import k_ctds

with k_ctds.connect('myserver', user='myuser', password='mypassword') as conn:
    with conn.cursor() as cursor:
        cursor.execute(
            "SELECT encrypt_option, protocol_type, auth_scheme "
            "FROM sys.dm_exec_connections "
            "WHERE session_id = @@SPID"
        )
        row = cursor.fetchone()
        print(f"Encrypted: {row[0]}")       # TRUE or FALSE
        print(f"Protocol: {row[1]}")         # TSQL
        print(f"Auth scheme: {row[2]}")      # SQL or NTLM

Note

Access to sys.dm_exec_connections requires the VIEW SERVER STATE permission. If you do not have this permission, ask your database administrator.

Troubleshooting

Connection fails with “Read from SQL server failed”: The server requires encryption but FreeTDS was not compiled with OpenSSL support, or the encryption setting is off. Rebuild FreeTDS with --with-openssl and set encryption = require.
Connection fails with a certificate error: The ca file directive points to a CA bundle that does not contain the issuing CA for the server’s certificate. Verify the server certificate chain and update the CA file, or remove the ca file directive to skip validation (not recommended for production).
Connection hangs or times out: Some TDS protocol versions have issues with certain encryption configurations. Try setting tds version = 7.3 if you are currently using 7.4.
Verifying FreeTDS has TLS support: Use tsql -C to check the build configuration. Look for OpenSSL: yes or GnuTLS: yes in the output.

$ tsql -C
Compile-time settings (established with the "configure" script)
                            Version: freetds v1.4.10
             freetds.conf directory: /usr/local/etc
     ...
                    OpenSSL: yes
                     GnuTLS: no