6.1 Transparent Data Encryption and Object-Level Encryption

Why TDE Matters for DP-300

Transparent Data Encryption (TDE) encrypts data and log files at rest at the page level: every 8 KB page is encrypted before it is written to disk and decrypted when it is read into memory, transparently to the application. TDE protects against the physical media or storage snapshot being read outside the database engine, which is the primary data-at-rest threat model for cloud databases. For DP-300, expect scenario questions on enabling TDE, choosing key models, and understanding the boundary between TDE, object-level encryption, and Always Encrypted.

On Azure SQL Database, TDE is enabled by default on every newly created database (this default changed in recent years, and many legacy exam answers still describe TDE as opt-in - the current behavior is on-by-default). You cannot turn TDE off on a database once it is in a geo-replication relationship.

DEK, Server Certificate, and the TDE Protector

TDE uses a two-tier key hierarchy. At the leaf is the database encryption key (DEK), a symmetric key stored inside the database (in sys.symmetric_keys). The DEK actually encrypts the data pages. The DEK itself is protected by the TDE Protector, which is either a certificate in the master database or, for customer-managed TDE, an asymmetric key in Azure Key Vault. The protector is the trust root; whoever holds the protector can decrypt the DEK and therefore the database.

Two key management models apply on Azure SQL Database:

With customer-managed TDE (BYOK), the database reaches into Azure Key Vault to unwrap the DEK at startup and periodically rewraps it. The Key Vault can be Microsoft-hosted or managed HSM, and the key can be RSA 2048 or RSA 3072 (and RSA 4096 on supported SKUs). Disabling or deleting the TDE Protector in the vault makes the database inaccessible - this is the revocation lever, and the exam may frame it as a data-breach containment step.

Key Rotation and Geo-Replication Implications

Two rotations exist and the exam tests the distinction:

• DEK rotation regenerates the symmetric DEK and re-encrypts every page in the database - a size-of-data operation. It is rarely necessary and is not done automatically.
• TDE Protector rotation rewraps the DEK with a new protector (a new Key Vault key or certificate). It is a metadata-only operation that takes seconds, regardless of database size. With customer-managed TDE you can rotate the protector on demand through the portal, CLI, or REST.

The geo-replication gotcha is heavily tested. A geo-secondary or geo-restored copy of a database needs the same TDE Protector. With service-managed TDE, the protector is regional and the geo-secondary is reachable automatically. With customer-managed TDE, the key vault that holds the TDE Protector must be available in the secondary region - either a vault replicated there or a separate vault holding the same key material. If you revoke the key in the primary region and the secondary cannot reach a vault with the key, the secondary stops serving. For geo-restore of a customer-managed TDE backup, the target server must have access to the same Key Vault key.

TDE on SQL Managed Instance and SQL Server on VMs

On SQL Managed Instance, TDE is available (and on by default for new databases, mirroring Azure SQL Database) and uses the same BYOK-through-Key-Vault flow. On SQL Server on Azure VMs and on-premises SQL Server, TDE is off by default and you enable it manually using the on-box workflow: create a master key in the master database, create or import a server certificate protected by the master key, then enable TDE:

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPwd!';
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';
USE SalesDB;
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDECert;
ALTER DATABASE SalesDB SET ENCRYPTION ON;

For EKM (Extensible Key Management) or Azure Arc-enabled SQL Server, the protector can be a Key Vault key instead of a local certificate. Azure Arc-enabled SQL Server extends cloud-based BYOK to on-premises and edge SQL instances: the server registers with Arc, a Key Vault-backed protector is wired through the EKM provider, and rotation/revocation behavior mirrors Azure SQL Database.

Disabling TDE means the engine decrypts every page as a size-of-data operation - plan for I/O load. A common exam trap is to assume ALTER DATABASE ... SET ENCRYPTION OFF is instant; it is not. Also, tempdb is automatically encrypted when any user database has TDE on, which can affect capacity planning for VMs.

Object-Level (Cell-Level) Encryption

Where TDE encrypts the whole database file, object-level encryption (sometimes called cell-level encryption) encrypts specific columns within a row using functions the application calls explicitly:

• EncryptByCert(cert_id, plaintext) - encrypts with a certificate's public key; decryption needs the private key.
• EncryptByPassPhrase('passphrase', plaintext) - derives a symmetric key from a passphrase.
• EncryptByKey(key_guid, plaintext) - uses a symmetric key opened in the session.

These functions require certificate and symmetric key management inside the database (CREATE CERTIFICATE, BACKUP CERTIFICATE, CREATE SYMMETRIC KEY), and the application must call the encrypt/decrypt functions on every read and write. Plaintext never persists unless you write it. The drawbacks: queries cannot search encrypted columns without a deterministic pattern, indexes on encrypted columns are not range-searchable, and application logic changes are invasive.

TDE vs Object-Level vs Always Encrypted

The exam frequently contrasts the three models:

The crucial boundary: with TDE and object-level encryption, the encryption happens inside the engine, so a privileged SQL login or anyone with access to the buffer pool or a memory dump can read plaintext. Always Encrypted pushes encryption to the client driver: the column master key never lives on the server, and the server only ever receives and stores ciphertext. That is the defining security property the exam tests - choose Always Encrypted when the threat model assumes a compromised or untrusted database administrator.

Choosing a Key Model for the Exam

When a scenario describes a compliance requirement that mandates customer control of keys (for example, financial services regulation that prohibits Microsoft holding the encryption key), the answer is customer-managed TDE with Azure Key Vault. When a scenario only needs protection of data at rest on the storage media and no customer-side key control, service-managed TDE is sufficient and is the default. When a scenario requires that even a database administrator cannot read sensitive columns, the answer is Always Encrypted - never TDE, because TDE always exposes plaintext in memory to the engine.

A subtle trap: TDE does not protect data in transit, does not protect data in use, and does not protect against anyone who can query the database with a valid login. It only raises the bar against physical or storage-level exfiltration of the data files - that is the full extent of its threat model. Exam answers that claim TDE stops a malicious SQL login are wrong; the login simply queries the data and the engine decrypts for it.