Encryption: Always Encrypted and Column-Level

Keeping secrets secret

Simple explanation

Imagine sending a letter in a locked box.

With column-level encryption, the post office (SQL Server) locks the box before storing it. The post office has the key, so it can unlock and read the letter if needed. With Always Encrypted, YOU lock the box before handing it to the post office. The post office stores the locked box but never has the key — even the database admin cannot read your letter.

Both protect data, but Always Encrypted is stronger because the database engine never sees the plaintext.

Always Encrypted: the database cannot read your data

Always Encrypted is a client-side encryption feature. Encryption keys never leave the client application — SQL Server stores and processes only ciphertext.

Encryption types

Two encryption types in Always Encrypted
Type	Behaviour	Can Query With =?	Best For
Deterministic	Same plaintext always produces same ciphertext	Yes (equality only)	Columns used in WHERE, JOIN, GROUP BY
Randomized	Same plaintext produces different ciphertext each time	No	Maximum security, columns not searched

CREATE COLUMN MASTER KEY CMK_VaultBank
WITH (KEY_STORE_PROVIDER_NAME = 'AZURE_KEY_VAULT',
      KEY_PATH = 'https://vaultbank-keys.vault.azure.net/keys/CMK/...');

CREATE COLUMN ENCRYPTION KEY CEK_SSN
WITH VALUES (COLUMN_MASTER_KEY = CMK_VaultBank,
             ALGORITHM = 'RSA_OAEP', ENCRYPTED_VALUE = 0x01...);

CREATE TABLE Customers (
    CustomerId INT NOT NULL PRIMARY KEY,
    SSN CHAR(11) COLLATE Latin1_General_BIN2
        ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK_SSN,
                       ENCRYPTION_TYPE = DETERMINISTIC,
                       ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
    CreditCard NVARCHAR(19)
        ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK_SSN,
                       ENCRYPTION_TYPE = RANDOMIZED,
                       ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')
);

Exam tip: Always Encrypted with secure enclaves

Standard Always Encrypted only supports equality comparisons on deterministic columns. Secure enclaves extend this by performing operations inside trusted memory. With enclaves you can use LIKE, range comparisons, ORDER BY, and pattern matching on encrypted columns.

If the exam requires range queries on encrypted data, the answer is secure enclaves.

Column-level encryption vs Always Encrypted vs TDE

Three encryption approaches — each protects against different threats
Feature	Always Encrypted	Column-Level Encryption	TDE
Encryption location	Client driver	SQL Server engine	Storage layer
DBA can read data?	No	Yes (has keys)	Yes (decrypted in memory)
Query support	Equality only (or enclave)	Full after OPEN KEY	Full — transparent
Performance impact	Client-side crypto overhead	Moderate per operation	Minimal — hardware accelerated
Protects against	Compromised server, malicious DBA	Unauthorized file access	Stolen backups, disk theft
Best for	Highest sensitivity (SSN, card numbers)	Moderate sensitivity with query needs	Baseline at-rest protection

Question

What is the key difference between Always Encrypted and column-level encryption?

Click or press Enter to reveal answer

Answer

Always Encrypted performs encryption/decryption in the CLIENT driver — SQL Server never sees plaintext or holds keys. Column-level encryption is performed by the SQL Server ENGINE — the DBA has access to keys and can decrypt data.

Click to flip back

Question

When should you use Always Encrypted with secure enclaves?

Click or press Enter to reveal answer

Answer

When you need to query encrypted columns with operations beyond equality — LIKE, range comparisons, ORDER BY, or pattern matching. Standard Always Encrypted only supports equality on deterministic columns.

Click to flip back

Question

What is the difference between deterministic and randomized encryption?

Click or press Enter to reveal answer

Answer

Deterministic: same plaintext always produces same ciphertext — allows equality comparisons. Randomized: same plaintext produces different ciphertext each time — no querying, but more secure.

Click to flip back

Knowledge Check

Priya at Vault Bank needs to encrypt SSNs so even DBAs cannot read them, but the app must search by exact SSN. Which approach?

Next up: Dynamic Data Masking and Row-Level Security — control what users see without changing the underlying data.