TDE, Column Encryption and Always Encrypted Compared

SQL Server encryption covers several different protections, and choosing the wrong one can leave the original risk untouched. Start by identifying who or what must be prevented from reading the data.

Start With the Threat

A stolen disk, an intercepted connection, and an administrator reading a column are different threats. No single checkbox answers all three. Write down the exposure before choosing a feature.

Also identify where plaintext is allowed to exist. An application must usually see the values it uses, while an operations team may not need that access. That boundary determines where keys and decryption should live.

Transport encryption protects the connection and remains a separate consideration. Data-at-rest encryption does not automatically establish a trusted encrypted connection. Review certificate validation and client settings as part of the wider design.

Use TDE for Database Files at Rest

Transparent Data Encryption protects database data and log files at rest. Database backups of a TDE-protected database retain encryption protection. The engine decrypts data for normal authorized queries, so applications usually need little or no query change.

SELECT d.name, d.is_encrypted,
       e.encryption_state, e.percent_complete,
       e.key_algorithm, e.key_length
FROM sys.databases AS d
LEFT JOIN sys.dm_database_encryption_keys AS e
  ON e.database_id = d.database_id;

TDE does not stop a sufficiently authorized database user from selecting readable values. It also does not replace access control. Its main purpose is protecting the stored database representation from access outside the normal engine path.

The database encryption key needs a protector, commonly a certificate in master on SQL Server. Preserve that certificate and its private key through the documented backup process. Test recovery on another instance with the required key material available.

Use Cell Encryption When the Engine May Decrypt

Cell-level encryption can protect selected values using functions such as ENCRYPTBYKEY and DECRYPTBYKEY. Your schema and code must handle ciphertext and decryption deliberately. The database engine participates in the cryptographic operation.

SELECT name, key_length, algorithm_desc,
       create_date, modify_date
FROM sys.symmetric_keys
ORDER BY name;
SELECT name, subject, expiry_date,
       pvt_key_encryption_type_desc
FROM sys.certificates
ORDER BY name;

These catalog queries inventory key metadata rather than exposing secret key material. Visibility depends on permissions. An inventory is useful, but it does not prove that backups and recovery procedures are complete.

Searching and indexing encrypted values requires careful design. Decrypting many rows to apply a filter can add work and limit useful access paths. Ordinary server-side encryption also does not create a boundary against administrators who control the required keys.

Use Always Encrypted for a Different Trust Boundary

Always Encrypted normally encrypts and decrypts sensitive values through a supported client driver. Column master keys remain in an external key store under client-side control. The database stores key metadata and encrypted column encryption keys.

SELECT name, key_store_provider_name, key_path
FROM sys.column_master_keys;
SELECT name, column_encryption_key_id
FROM sys.column_encryption_keys;
SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name,
       OBJECT_NAME(object_id) AS table_name,
       name AS column_name, encryption_type_desc,
       column_encryption_key_id
FROM sys.columns
WHERE encryption_type IS NOT NULL;

Key paths can reveal infrastructure details, so handle inventory output appropriately. The application identity needs the intended key-store access. Database access alone should not automatically provide decryption capability.

Without secure enclaves, supported computations on encrypted values are restricted. Deterministic encryption permits some equality operations but exposes repeated-value patterns. Randomized encryption offers different protection and query limitations.

Understand What Enclaves Change

Always Encrypted with secure enclaves enables additional operations inside a protected server-side memory area. It can support richer confidential queries and certain encryption maintenance operations. Support depends on the engine version, configuration, driver, and chosen operation.

The enclave changes the design and operational requirements, not just a connection-string option. Review attestation where applicable, supported data types, and key provisioning. Test the actual statements your application needs rather than assuming every query becomes available.

Neither form removes the need to protect the client. An application allowed to decrypt can expose plaintext through logs, exports, or compromised credentials. Follow the data beyond the database boundary.

Measure Cost and Rehearse Key Recovery

TDE adds cryptographic work around storage operations. Cell encryption can add per-value work and complicate filtering, while Always Encrypted adds client work and ciphertext overhead. The practical cost depends on the workload and configuration.

Measure representative reads, writes, backups, and recovery operations yourself. Include key rotation and the loss of the normal key-store path in planning. A design that protects data but cannot recover its keys can protect it from everybody.

Document who owns each key and how replacement staff obtain approved recovery access. Keep key backups separate from the only copy of the database backup. Encryption is an operational responsibility for the lifetime of the data.

Encryption is not one universal shield, it is protection designed around a specific threat.

This post was rewritten from scratch in September 2026. The original, published on 2009-11-20, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.