2.1 — Data Encryption: Always Encrypted and Column-Level Encryption
DP-800 Domain 2 · Skill 2.1
What You Need to Know
Encryption questions turn on one point: who must not be able to read the data. Database administrators? Someone who steals the files? Someone sniffing the network? Each answer points to a different feature.
The encryption layers
| Feature | Protects against | Where the keys live | Can the DBA read the data? |
|---|---|---|---|
| TLS | Eavesdropping on the network | Server certificate | Yes |
| Transparent Data Encryption (TDE) | Stolen data files, backups and logs (at rest) | Server (service-managed or customer-managed in Key Vault) | Yes: data is decrypted in memory for any authorized query |
Column-level encryption (ENCRYPTBYKEY) |
Readers who lack the key | Symmetric key in the database, protected by a certificate or password | Anyone who can open the key can decrypt, which often includes admins |
| Always Encrypted | Everyone on the server side, including DBAs and cloud operators | Client side: the column master key in Azure Key Vault (or a certificate store) | No: the database only ever sees ciphertext |
Always Encrypted in detail
- Keys: the column master key (CMK), held outside the database in Key Vault, protects the column encryption keys (CEK) stored in the database.
- The client driver (with
Column Encryption Setting=Enabledin the connection) encrypts parameters and decrypts results transparently. Queries must use parameters, not literals. - Deterministic encryption always gives the same ciphertext for the same value, so it supports equality lookups, joins, grouping and indexing, but it reveals patterns. Good for IDs with many distinct values (SSNs).
- Randomized encryption is more secure but supports no server-side comparisons.
- Always Encrypted with secure enclaves lets the server run richer operations, such as range comparisons, pattern matching and in-place encryption, inside a protected enclave.
- Plan the key rotation of the CMK in Key Vault, and give applications Key Vault access through a managed identity.
Column-level encryption (cell-level)
CREATE SYMMETRIC KEY CardKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE CardCert;
OPEN SYMMETRIC KEY CardKey DECRYPTION BY CERTIFICATE CardCert;
UPDATE dbo.Payment SET CardEnc = ENCRYPTBYKEY(KEY_GUID('CardKey'), CardNumber);
SELECT CONVERT(varchar(20), DECRYPTBYKEY(CardEnc)) FROM dbo.Payment;
Encryption and decryption happen in T-SQL, so anyone with permission on the key and certificate can read the data. It's useful when the application can't change drivers, but it isn't protection from DBAs.
Choosing
| Requirement | Choose |
|---|---|
| "Backups and data files must be unreadable if stolen" | TDE (on by default in Azure SQL) |
| "DBAs and cloud operators must never see card numbers" | Always Encrypted |
| "Search by exact national ID number on encrypted data" | Always Encrypted, deterministic |
| "Range queries on an encrypted salary column" | Always Encrypted with secure enclaves |
| "Hide part of an email in query results for support staff" | Not encryption: dynamic data masking (lesson 2.2) |
Key Concept: TDE protects data at rest, but anyone allowed to query sees it in clear text. Always Encrypted keeps keys on the client, so even administrators see only ciphertext. Deterministic encryption allows equality queries; randomized encryption is safer but allows no comparisons.
Exam Traps
- Choosing TDE to keep data from DBAs
- Using randomized Always Encrypted on a column that must be searched by equality
- Storing the column master key inside the database it protects
- Expecting Always Encrypted to work with literal values in queries instead of parameters
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 14 DP-800 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.
Cancel anytime. Or €199 a year, which saves 17%.
Not sure yet? Try 10 free mock exam questions.
Already Pro? Log in.
Sources
- Always Encrypted, Microsoft Learn
- Always Encrypted with secure enclaves, Microsoft Learn
- Encrypt a column of data, Microsoft Learn
- Transparent data encryption for Azure SQL, Microsoft Learn