Database Encryption in SQL Server

SQL Server Hardening covers Transparent Data Encryption (TDE) briefly as one control among many. This article goes deeper on encryption specifically — both whole-database encryption at rest (TDE) and column-level encryption (native cell-level encryption and Always Encrypted) — because they solve different threat models and are frequently confused with each other. TDE protects against someone stealing a disk or backup file; it does nothing against a DBA, a compromised application account, or an attacker with a live authenticated connection reading plaintext through ordinary queries. Column-level encryption, done right, protects against exactly that. Most production systems handling sensitive data need both, applied to different things.

Scope: SQL Server 2016–2022, on-premises and IaaS. Azure SQL Database/Managed Instance support the same features with some PaaS-specific management differences (e.g., TDE with customer-managed keys via Azure Key Vault), noted where relevant.

1. The SQL Server Encryption Hierarchy

Both TDE and column-level encryption sit on top of the same underlying key hierarchy — understanding it once makes every feature built on it easier to reason about:

Windows DPAPI (protects the instance's credentials)
   └── Service Master Key (SMK) — one per instance, auto-created, never leaves the instance
        └── Database Master Key (DMK) — one per database, created explicitly by an admin
             └── Certificates / Asymmetric Keys — protected by the DMK
                  └── Symmetric Keys / Database Encryption Key (DEK) — protected by a cert/asym key
                       └── Actual data (TDE's DEK, or a column's ciphertext)

Every layer is encrypted by the layer above it. Losing a certificate or key without a backup means everything it protects — an entire TDE-encrypted database, or a set of encrypted columns — becomes permanently unrecoverable, not just inaccessible. This is the single most common self-inflicted encryption incident: enabling encryption without immediately backing up the certificate/key that makes it reversible.

2. Transparent Data Encryption (TDE) — Whole-Database Encryption at Rest

What TDE Actually Protects

TDE encrypts the physical data (.mdf), log (.ldf), and tempdb files, plus backups, at the storage layer. It's called "transparent" because no application changes are needed — SQL Server decrypts pages on the fly for any authenticated connection. That transparency is also its limitation:

Setup

-- 1. Service-level master key and certificate (once per instance/database)
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong-unique-password>';
CREATE CERTIFICATE TDE_Cert WITH SUBJECT = 'TDE Certificate';

-- 2. Database encryption key, protected by that certificate
USE [YourDatabase];
CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;

-- 3. Turn it on
ALTER DATABASE [YourDatabase] SET ENCRYPTION ON;

Encryption of existing data happens as a background scan — the database remains online and usable throughout, but avoid triggering it during peak load on very large databases. Monitor progress and current state:

SELECT db_name(database_id) AS db_name, encryption_state, percent_complete, encryptor_type
FROM sys.dm_database_encryption_keys;

encryption_state: 0 = no key present, 1 = unencrypted, 2 = encryption in progress, 3 = encrypted, 4 = key change in progress, 5 = decryption in progress, 6 = protection change in progress (e.g., swapping the protecting certificate).

Certificate and Key Management — the Part People Skip

-- Immediately after creating the certificate -- do not skip this step
BACKUP CERTIFICATE TDE_Cert TO FILE = 'D:\Secure\TDE_Cert.cer'
WITH PRIVATE KEY (
    FILE = 'D:\Secure\TDE_Cert.pvk',
    ENCRYPTION BY PASSWORD = '<different-strong-password>'
);

• Store the certificate and private key backup separately from database backups, on access-controlled storage, and vault the password.
• Without this backup, restoring a TDE-encrypted backup to another server, or recovering from a corrupted master database, is impossible — not "difficult," impossible. TDE is deliberately designed so there's no bypass.
• Rotate the DEK periodically per policy: ALTER DATABASE [YourDatabase] SET ENCRYPTION KEY ... REGENERATE; re-encrypts with a new key without a full offline re-encryption cycle for the certificate layer.
• For higher-assurance environments, use Extensible Key Management (EKM) to root the TDE certificate in an external HSM rather than SQL Server's own key store — Azure SQL's TDE with a customer-managed key (Azure Key Vault) is the PaaS equivalent of this pattern.

Always On / Replication Considerations

Every secondary replica in an Availability Group, and every subscriber in log shipping/replication involving a TDE-encrypted database, needs the same certificate and private key imported before it can host that database. This is a common cause of failed AG joins — plan certificate distribution before enabling TDE on a database that's already part of a topology.

3. Cell-Level Encryption — Native T-SQL Column Encryption

Before Always Encrypted existed (pre-2016), SQL Server's native way to encrypt specific columns was via symmetric/asymmetric keys and the ENCRYPTBYKEY/DECRYPTBYKEY (or ENCRYPTBYCERT/ENCRYPTBYASYMKEY) T-SQL functions. It's still valid, still supported, and still occasionally the right tool — mainly for legacy applications that can't adopt an Always Encrypted-aware driver.

-- Setup: master key → certificate → symmetric key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong-password>';
CREATE CERTIFICATE Col_Cert WITH SUBJECT = 'Column Encryption Certificate';
CREATE SYMMETRIC KEY Col_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Col_Cert;

-- Column must be varbinary -- it stores ciphertext, not the original type
ALTER TABLE dbo.Customers ADD SSN_Encrypted varbinary(256);

-- Write: open the key for the session, then encrypt
OPEN SYMMETRIC KEY Col_Key DECRYPTION BY CERTIFICATE Col_Cert;
UPDATE dbo.Customers
SET SSN_Encrypted = ENCRYPTBYKEY(KEY_GUID('Col_Key'), SSN)
WHERE SSN_Encrypted IS NULL;
CLOSE SYMMETRIC KEY Col_Key;

-- Read: open the key, then decrypt
OPEN SYMMETRIC KEY Col_Key DECRYPTION BY CERTIFICATE Col_Cert;
SELECT CustomerID, CONVERT(varchar(11), DECRYPTBYKEY(SSN_Encrypted)) AS SSN
FROM dbo.Customers;
CLOSE SYMMETRIC KEY Col_Key;

What This Does and Doesn't Buy You

• The column is genuinely ciphertext at rest and in backups.
• It does not meaningfully protect against a DBA. Access to decrypt is gated by a database permission (GRANT VIEW DEFINITION / control over the certificate and key), and anyone with sysadmin or db_owner can grant themselves that permission, or simply take a certificate backup and decrypt offline. This is the key distinction from Always Encrypted below.
• You lose native indexing, sorting, and equality search on the plaintext value — the column is varbinary, so any lookup requires decrypting rows (expensive at scale) or maintaining a separate deterministic hash/HMAC column purely for lookup purposes.
• Application/T-SQL changes are required throughout — this is not transparent the way TDE is.

4. Always Encrypted — Client-Side Column Encryption

Always Encrypted's core architectural difference: encryption and decryption happen in the client driver, not in the database engine. Plaintext, and the keys that would decrypt it, are never present inside SQL Server at all — not in memory, not in query plans, not in backups, not visible to a sysadmin running SELECT *. This is the feature that actually protects sensitive columns from a DBA or from an attacker who has fully compromised the SQL Server instance itself.

Key Architecture

Because the CMK never enters SQL Server, SQL Server can never decrypt the CEK, and can therefore never see plaintext. This isn't a permission that can be misconfigured away — it's an architectural guarantee as long as the CMK stays outside the instance.

Deterministic vs. Randomized Encryption

For a low-cardinality column (a boolean, a small enum), deterministic encryption can leak the distribution of values through ciphertext frequency alone — think carefully before using deterministic encryption on anything with few possible values.

Setup

-- Typically done via SSMS's Always Encrypted wizard or PowerShell, shown here as T-SQL for clarity
CREATE COLUMN MASTER KEY CMK_Customer
WITH (
    KEY_STORE_PROVIDER_NAME = 'AZURE_KEY_VAULT',   -- or 'MSSQL_CERTIFICATE_STORE' on-prem
    KEY_PATH = 'https://your-vault.vault.azure.net/keys/CMK1/<version>'
);

CREATE COLUMN ENCRYPTION KEY CEK_Customer
WITH VALUES (
    COLUMN_MASTER_KEY = CMK_Customer,
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x...   -- generated by the tooling, not hand-typed
);

ALTER TABLE dbo.Customers
ALTER COLUMN NationalID varchar(11)
COLLATE Latin1_General_BIN2   -- required collation for deterministic encryption on string columns
ENCRYPTED WITH (
    COLUMN_ENCRYPTION_KEY = CEK_Customer,
    ENCRYPTION_TYPE = DETERMINISTIC,
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
);

Application Requirements

Unlike TDE, this is not transparent to existing applications. The connection string must explicitly opt in:

Column Encryption Setting=Enabled;

And the client driver must support Always Encrypted: modern ADO.NET, JDBC 6.0+, ODBC 17.1+, and current versions of common ORMs. An application connecting without an Always Encrypted-aware driver simply receives ciphertext varbinary blobs and cannot use the data at all — there's no silent fallback to plaintext, which is the correct failure mode for a security control but does mean every consumer of that data needs to be inventoried and updated before rollout.

Secure Enclaves (SQL Server 2019+)

Secure enclaves narrow the gap between deterministic's query flexibility and randomized's stronger security: computations (range queries, LIKE, in-place re-encryption) can run against randomized-encrypted data inside a hardware-protected memory enclave (Intel SGX) on the server — a region that's isolated even from the OS and hypervisor, and that an attestation service verifies before the client will send key material into it. This requires compatible hardware/VM SKUs and an attestation service, so it's a deliberate infrastructure investment, not a checkbox — but it's the way to get richer query support without falling back to deterministic encryption's pattern-leakage tradeoff.

5. Choosing Between the Three

These aren't mutually exclusive — a common, sound pattern is TDE on the whole database as a baseline, plus Always Encrypted on the specific columns that need DBA-proof protection.

6. Key and Certificate Management Practices

Applies across all three mechanisms:

• Back up the Service Master Key, every Database Master Key, and every certificate immediately after creation — before you need them, not after a disaster.
• Store key/certificate backups separately from database backups, encrypt the backup files themselves, and keep the password in a vault, not a shared drive next to the backup.
• Rotate keys and certificates on a defined schedule, and immediately on any suspected compromise or when a DBA with direct access to key material leaves the organization.
• For Always Encrypted, prefer Azure Key Vault or an on-prem HSM over the Windows Certificate Store for the CMK in production — a cert-store-based CMK is only as protected as that one machine, and doesn't scale cleanly to multiple app servers needing access.
• Consider EKM to root TDE's certificate in a physical/cloud HSM instead of SQL Server's own encryption hierarchy, removing SQL Server itself as the ultimate trust anchor.

7. Monitoring and Verification

Alert on: TDE unexpectedly disabled on a database that should have it, a certificate approaching expiry, a new Column Master Key or Column Encryption Key introduced outside a change window, and permission grants on cell-level encryption keys/certificates to unexpected principals.

Implementation Checklist

NIST SP 800-53 Rev. 5 Control Mapping

References

• Microsoft Learn — Transparent Data Encryption (TDE)
• Microsoft Learn — Always Encrypted (Database Engine)
• Microsoft Learn — Always Encrypted with Secure Enclaves
• Microsoft Learn — Encrypt a Column of Data (Cell-Level)
• Microsoft Learn — Extensible Key Management (EKM)
• CIS Microsoft SQL Server Benchmarks
• NIST SP 800-53 Rev. 5 — Security and Privacy Controls
• SQL Server Hardening
• Encryption