TLS, Force Encryption, Certificates, Keys, TDE, Backup Encryption, and Key Lifecycle

Learning outcomes

“The database is encrypted” is not a useful production statement. ServiceHub has at least three different protection problems: clients must know they are talking to the intended SQL Server and keep network traffic confidential; database files and transaction logs must be protected at rest; and backup media must remain unreadable when copied away from the server. SQL Server addresses those with different mechanisms and different key dependencies.

This lesson separates TLS (Transport Layer Security) from Transparent Data Encryption (TDE) and backup encryption, then turns certificates into availability objects that must be inventoried, backed up, rotated deliberately and restore-tested.

1. Three encryption boundaries, three threat models

SELECT SERVERPROPERTY('ProductVersion') AS product_version,       SERVERPROPERTY('ProductUpdateLevel') AS update_level,       SERVERPROPERTY('Edition') AS edition,       SERVERPROPERTY('EngineEdition') AS engine_edition,       SERVERPROPERTY('IsIntegratedSecurityOnly') AS windows_auth_only;GOSELECT name,value_in_useFROM sys.configurationsWHERE name IN  (N'contained database authentication',   N'column encryption enclave type',   N'clr enabled',N'xp_cmdshell');GOSELECT DB_NAME() AS database_name,       ORIGINAL_LOGIN() AS original_login,       SUSER_SNAME() AS execution_login,       USER_NAME() AS database_user;GO

USE master;GOIF DB_ID(N'ServiceHubSecurityLab') IS NULL  CREATE DATABASE ServiceHubSecurityLab;GOALTER DATABASE ServiceHubSecurityLab SET COMPATIBILITY_LEVEL = 170;GOUSE ServiceHubSecurityLab;GOIF SCHEMA_ID(N'ops') IS NULL EXEC(N'CREATE SCHEMA ops AUTHORIZATION dbo;');IF SCHEMA_ID(N'api') IS NULL EXEC(N'CREATE SCHEMA api AUTHORIZATION dbo;');IF SCHEMA_ID(N'sec') IS NULL EXEC(N'CREATE SCHEMA sec AUTHORIZATION dbo;');IF SCHEMA_ID(N'lab14') IS NULL EXEC(N'CREATE SCHEMA lab14 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'ops.WorkOrderSecure',N'U') IS NULLBEGIN  CREATE TABLE ops.WorkOrderSecure  (    work_order_id bigint IDENTITY(14001,1) NOT NULL      CONSTRAINT PK_ops_WorkOrderSecure PRIMARY KEY,    tenant_code char(3) NOT NULL,    customer_code varchar(16) NOT NULL,    customer_name nvarchar(100) NOT NULL,    customer_email varchar(200) NOT NULL,    status varchar(16) NOT NULL,    priority tinyint NOT NULL,    amount decimal(12,2) NOT NULL,    opened_at datetime2(0) NOT NULL,    notes nvarchar(400) NULL,    CONSTRAINT CK_ops_WorkOrderSecure_tenant      CHECK (tenant_code IN ('N01','W02','E03')),    CONSTRAINT CK_ops_WorkOrderSecure_status      CHECK (status IN ('OPEN','ASSIGNED','CLOSED','ESCALATED')),    CONSTRAINT CK_ops_WorkOrderSecure_priority CHECK (priority BETWEEN 1 AND 5)  );  INSERT ops.WorkOrderSecure    (tenant_code,customer_code,customer_name,customer_email,status,priority,amount,opened_at,notes)  VALUES    ('N01','CUST-1401',N'Mina Rahimi','mina@example.test','OPEN',2,240.00,'2026-08-01T09:00:00',N'North queue'),    ('N01','CUST-1402',N'Owen Brooks','owen@example.test','ASSIGNED',3,480.00,'2026-08-01T10:15:00',N'North dispatch'),    ('W02','CUST-1403',N'Sara Chen','sara@example.test','ESCALATED',5,860.00,'2026-08-01T11:30:00',N'West escalation'),    ('W02','CUST-1404',N'Ali Reza','ali@example.test','OPEN',1,120.00,'2026-08-02T08:20:00',N'West queue'),    ('E03','CUST-1405',N'Nora Bell','nora@example.test','CLOSED',4,735.00,'2026-08-02T09:40:00',N'East closed'),    ('E03','CUST-1406',N'David Kim','david@example.test','OPEN',2,315.00,'2026-08-02T12:05:00',N'East queue');  CREATE INDEX IX_ops_WorkOrderSecure_tenant_status    ON ops.WorkOrderSecure(tenant_code,status,opened_at)    INCLUDE(customer_code,priority,amount);END;GO

TDE does not authenticate the server to a client. TLS does not keep a sysadmin from querying plaintext. Backup encryption does not encrypt the live database. Applying the wrong mechanism can produce a green “encrypted” checkbox while the actual threat remains untouched.

2. TLS needs encryption and server identity validation

SELECT c.session_id,c.net_transport,c.protocol_type,c.auth_scheme,       c.encrypt_option,c.client_net_address,       c.local_net_address,c.local_tcp_portFROM sys.dm_exec_connections AS cWHERE c.session_id=@@SPID;GO

If encrypt_option is true, the current channel is encrypted. That does not prove the client validated the server certificate’s trust chain and hostname. TrustServerCertificate=True can preserve encryption while explicitly skipping normal server-certificate identity validation.

Server=db01.servicehub.example;Database=ServiceHubSecurityLab;Encrypt=Strict;TrustServerCertificate=False;HostNameInCertificate=db01.servicehub.example;

Driver={ODBC Driver 18 for SQL Server};Server=tcp:db01.servicehub.example,1433;Database=ServiceHubSecurityLab;Encrypt=Strict;TrustServerCertificate=No;

Microsoft.Data.SqlClient 4.0 changed Encrypt to default true, and version 5.0 added Strict/TDS 8.0 behavior. ODBC Driver 18 defaults encryption to yes/mandatory. Do not let those secure defaults turn into a permanent TrustServerCertificate=True workaround when certificate-chain or hostname validation fails.

Server-side Force Encryption and certificate binding are instance/platform administration. Windows commonly uses SQL Server Configuration Manager; Linux uses its supported TLS configuration path. The chapter does not flip Force Encryption because doing so can break every client that lacks the trust chain and may require service restart/change control.

3. TDE protects pages/log at rest, not authorized query results

TDE creates a symmetric database encryption key (DEK) in the user database. The DEK is protected by a certificate or asymmetric key associated with master. Data pages are encrypted before being written and decrypted when read into memory. Database clients with permission to query the data still receive plaintext.

SELECT SERVERPROPERTY('Edition') AS edition;GOSELECT d.name,dek.encryption_state,dek.encryption_state_desc,       dek.percent_complete,dek.key_algorithm,dek.key_length,       dek.encryptor_typeFROM sys.databases AS dLEFT JOIN sys.dm_database_encryption_keys AS dek ON dek.database_id=d.database_idWHERE d.name IN (N'ServiceHubSecurityLab',N'tempdb');GOSELECT name,thumbprint,pvt_key_encryption_type_desc,pvt_key_last_backup_dateFROM master.sys.certificatesWHERE name NOT LIKE N'##%'ORDER BY name;GO

SQL Server 2025 Express does not support TDE; Standard and Enterprise do, as do the corresponding free Developer editions for development/test. The mandatory lab therefore inspects TDE state everywhere and leaves actual TDE enablement as an optional Standard Developer/Enterprise Developer exercise.

-- Administrative template; do not run blindly.USE master;GO-- Create the master database master key only if your instance does not-- already have one, using a deployment-supplied secret.-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<secret from secure deployment channel>';CREATE CERTIFICATE ServiceHubTDECert  WITH SUBJECT=N'ServiceHubSecurityLab TDE protector';GOUSE ServiceHubSecurityLab;CREATE DATABASE ENCRYPTION KEY  WITH ALGORITHM=AES_256  ENCRYPTION BY SERVER CERTIFICATE ServiceHubTDECert;ALTER DATABASE ServiceHubSecurityLab SET ENCRYPTION ON;GOSELECT DB_NAME(database_id) AS database_name,encryption_state_desc,percent_completeFROM sys.dm_database_encryption_keysWHERE database_id=DB_ID(N'ServiceHubSecurityLab');GO-- Immediately back up ServiceHubTDECert with its private key to protected,-- tested escrow before considering this production-ready.

4. Backup encryption is independent and edition-sensitive

SQL Server can encrypt a backup using a certificate or supported asymmetric key. That is valuable even when the live database is not TDE-enabled. Conversely, a TDE database’s backup already remains encrypted because the database encryption key protects its content, yet independent backup encryption can be chosen as a separate control.

-- SQL Server 2025 Standard/Enterprise/Developer template.-- Express can restore encrypted backups but cannot create them.USE master;GOCREATE CERTIFICATE ServiceHubBackupCert  WITH SUBJECT=N'ServiceHub backup encryption';GO-- Back up the certificate + private key to a DIFFERENT protected location.-- BACKUP CERTIFICATE ServiceHubBackupCert--   TO FILE = '<secure certificate path>'--   WITH PRIVATE KEY--     (FILE='<secure private-key path>',--      ENCRYPTION BY PASSWORD='<deployment-supplied secret>');GO-- BACKUP DATABASE ServiceHubSecurityLab--   TO DISK='<backup path>'--   WITH COMPRESSION,CHECKSUM,--        ENCRYPTION(ALGORITHM=AES_256,--                   SERVER CERTIFICATE=ServiceHubBackupCert);

On a different restore instance, import the exact protector certificate/private key before restoring the encrypted backup. Certificate thumbprints and retained old protectors matter: rotating the current key does not erase the dependency of older backup sets on the older encryptor.

5. Key lifecycle is part of HA/DR, not just security administration

Build an inventory of every protector, what it protects, owner, creation/expiry dates, backup date, escrow location, restore test, rotation history and replicas/DR sites that need it. Separate the backup from the key material; otherwise theft of one storage location may expose both. Also separate duties where practical so one routine administrator does not hold every database and key-management capability.

USE master;GOSELECT name,subject,start_date,expiry_date,       thumbprint,pvt_key_last_backup_dateFROM sys.certificatesWHERE name NOT LIKE N'##%'ORDER BY expiry_date,name;GOSELECT DB_NAME(database_id) AS database_name,       encryption_state_desc,encryptor_type,       encryptor_thumbprint,key_algorithm,key_lengthFROM sys.dm_database_encryption_keysORDER BY database_name;GO

6. Production judgment

Production judgment. Treat TLS certificate renewal and database encryption key rotation as planned changes with client/restore tests. Record exact driver versions and encryption modes because client defaults evolve independently from the Database Engine. For TDE/backup encryption, document edition support. For HA/DR, make sure protector material exists where restore or failover procedures require it without exposing private keys more broadly than necessary.

Verification checklist

• Record encrypt_option and separately document whether your client validates trust and hostname.
• Identify edition support before designing a TDE or encrypted-backup lab.
• Inventory TDE state and existing certificate private-key backup dates.
• For any encryption test, write the protector restore sequence before enabling the feature.
• Never use key rotation as an excuse to discard protectors still required by old backups.

Authoritative references

• SQL Server and client encryption summary — TLS configuration/validation combinations
• Special cases for encrypting connections — certificate trust and self-signed caveats
• Microsoft.Data.SqlClient connection string syntax — Encrypt, Strict, TrustServerCertificate and hostname validation
• ODBC connection-string attributes — ODBC Driver 18 encryption defaults and validation
• Transparent Data Encryption — TDE architecture and certificate backup dependency
• Backup encryption — backup encryptors, restore requirements and edition limits
• SQL Server 2025 editions and supported features — security feature matrix
• SQL Server encryption — encryption feature boundaries