Encryption: TDE, Always Encrypted, and VBS Enclaves

Encrypting data at rest

Transparent Data Encryption (TDE)

TDE encrypts the entire database at rest — data files, log files, and backups are all encrypted on disk.

How it works:

1. A Database Encryption Key (DEK) encrypts the database
2. The DEK is protected by a TDE protector (certificate or asymmetric key)
3. Encryption/decryption happens at the I/O level — transparent to applications
4. No application code changes required

TDE on Azure SQL Database and MI:

• Enabled by default on all new databases
• Uses service-managed keys by default (Microsoft manages the key)
• Can switch to customer-managed keys (CMK) in Azure Key Vault for full control

TDE on SQL Server (VMs):

• Not enabled by default — must be configured manually
• Requires a certificate backup (critical for disaster recovery)

-- Enable TDE (SQL Server on VMs)
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert;

ALTER DATABASE NorthStarERP SET ENCRYPTION ON;

-- Check TDE status
SELECT db.name, db.is_encrypted, dek.encryption_state
FROM sys.databases db
LEFT JOIN sys.dm_database_encryption_keys dek ON db.database_id = dek.database_id;

ℹ️ Customer-managed keys (CMK) for TDE

With CMK, you store the TDE protector in Azure Key Vault:

• You control the key lifecycle — create, rotate, revoke
• Revoke access = database becomes inaccessible (the “kill switch”)
• Requires Azure Key Vault with soft-delete and purge protection enabled
• The SQL server’s managed identity needs Get, Wrap Key, and Unwrap Key permissions on the key

The exam may ask: “How can Amara ensure Harbour Health retains full control over encryption keys?” → Customer-managed TDE keys in Azure Key Vault.

Object-level encryption

For encrypting specific values within the database (not the whole database):

-- Create a symmetric key
CREATE SYMMETRIC KEY PatientDataKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE DataCert;

-- Encrypt a value
OPEN SYMMETRIC KEY PatientDataKey DECRYPTION BY CERTIFICATE DataCert;
UPDATE Patients SET SSN_Encrypted = ENCRYPTBYKEY(KEY_GUID('PatientDataKey'), SSN);
CLOSE SYMMETRIC KEY PatientDataKey;

-- Decrypt
OPEN SYMMETRIC KEY PatientDataKey DECRYPTION BY CERTIFICATE DataCert;
SELECT PatientID, CONVERT(VARCHAR, DECRYPTBYKEY(SSN_Encrypted)) AS SSN
FROM Patients;
CLOSE SYMMETRIC KEY PatientDataKey;

Limitations:

• Application must manage key opening/closing
• Encrypted columns are VARBINARY — no indexing, no range queries
• More code changes than TDE or Always Encrypted

Always Encrypted

Always Encrypted is client-side encryption — data is encrypted by the application (or driver) before it reaches the database engine.

Key concept: The database engine never sees plaintext. Even a DBA with full access sees only ciphertext.

Architecture

Component	Where It Lives	Purpose
Column Master Key (CMK)	Azure Key Vault, Windows Certificate Store, or HSM	Protects the column encryption key
Column Encryption Key (CEK)	Database (encrypted form only)	Encrypts the actual data
Client driver	Application side	Encrypts/decrypts data using the CEK

Encryption types

Type	What It Does	Supports	Use When
Deterministic	Same plaintext always produces same ciphertext	Equality comparisons, joins, GROUP BY, indexing	You need to search or join on encrypted columns
Randomized	Same plaintext produces different ciphertext each time	Nothing (no server-side operations)	Maximum security, no need to query on the column

Setting up Always Encrypted

Amara protects patient SSN and medical record numbers:

Using SSMS wizard:

1. Right-click the table → Encrypt Columns
2. Select columns to encrypt and encryption type (deterministic or randomized)
3. Choose where to store the CMK (Azure Key Vault recommended)
4. SSMS generates the CEK, encrypts existing data, and configures the columns

Using T-SQL:

-- Create CMK definition (metadata pointing to Key Vault)
CREATE COLUMN MASTER KEY CMK_AKV
WITH (KEY_STORE_PROVIDER_NAME = 'AZURE_KEY_VAULT',
      KEY_PATH = 'https://harbourhealthvault.vault.azure.net/keys/AlwaysEncryptedCMK/...');

-- Create CEK (encrypted by the CMK)
CREATE COLUMN ENCRYPTION KEY CEK_SSN
WITH VALUES (COLUMN_MASTER_KEY = CMK_AKV,
  ALGORITHM = 'RSA_OAEP',
  ENCRYPTED_VALUE = 0x01AA...);

-- Create table with encrypted columns
CREATE TABLE Patients (
    PatientID INT PRIMARY KEY,
    Name NVARCHAR(100),
    SSN NVARCHAR(11) ENCRYPTED WITH (
        COLUMN_ENCRYPTION_KEY = CEK_SSN,
        ENCRYPTION_TYPE = DETERMINISTIC,
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')
);

Always Encrypted with VBS enclaves

Standard Always Encrypted has a limitation: the database engine can’t perform operations on encrypted data (no sorting, pattern matching, or range queries on randomized columns).

VBS enclaves solve this by creating a secure, isolated memory region (enclave) within the database engine:

• The enclave can access encryption keys inside its protected boundary
• Operations on encrypted data (comparisons, LIKE, sorting, range queries) happen inside the enclave
• Data remains encrypted outside the enclave — the rest of the database engine can’t see plaintext

Feature	Standard Always Encrypted	With VBS Enclaves
Equality queries (deterministic)	Yes	Yes
Range queries	No	Yes (inside enclave)
LIKE pattern matching	No	Yes (inside enclave)
Sorting	No	Yes (inside enclave)
In-place encryption	No (requires data export)	Yes (enclave encrypts in-place)
Key rotation	Complex (re-encrypt all data)	Simpler (enclave handles re-encryption)

💡 Exam tip: when to recommend each encryption method

• TDE — “Protect the entire database at rest with no application changes” → TDE
• Always Encrypted — “Even DBAs should not see sensitive column data” → Always Encrypted
• Always Encrypted + VBS enclaves — “DBAs can’t see data, but we need range queries on encrypted columns” → VBS enclaves
• Object-level — “Encrypt specific values with custom key management” → ENCRYPTBYKEY functions
• TDE + Always Encrypted — “Defence in depth: encrypt at rest AND encrypt sensitive columns” → Both together

Question

What is the key difference between TDE and Always Encrypted?

Click or press Enter to reveal answer

Answer

TDE encrypts the entire database at the storage level — transparent to apps, but the DB engine sees plaintext. Always Encrypted is client-side — data is encrypted before reaching the engine, even DBAs see only ciphertext.

Click to flip back

Question

Deterministic vs randomized encryption in Always Encrypted?

Click or press Enter to reveal answer

Answer

Deterministic: same input always produces the same ciphertext. Supports equality comparisons, joins, indexing. Randomized: same input produces different ciphertext each time. Maximum security, but no server-side operations.

Click to flip back

Question

What does a VBS enclave add to Always Encrypted?

Click or press Enter to reveal answer

Answer

A secure, isolated memory region in the database engine that can access encryption keys. Enables range queries, LIKE, sorting, and in-place encryption/key rotation on encrypted data.

Click to flip back

Knowledge Check

Amara needs to encrypt patient SSN numbers so that even database administrators cannot see the plaintext values. The application needs to search for patients by exact SSN. Which approach should she use?

ATDE with customer-managed keysBAlways Encrypted with randomized encryptionCAlways Encrypted with deterministic encryptionDObject-level encryption with ENCRYPTBYKEY

Check Answer

Knowledge Check

Kenji's team uses Always Encrypted to protect financial data. They now need to run range queries (WHERE amount BETWEEN 1000 AND 5000) on an encrypted column. What should they implement?

ASwitch from randomized to deterministic encryptionBEnable Always Encrypted with VBS enclavesCUse TDE instead of Always EncryptedDCreate a filtered index on the encrypted column

Check Answer

Next up: Network Security: Firewalls, Private Links, and Endpoints — control who can reach your databases at the network level.