6.2 Always Encrypted and VBS Enclaves

Why Always Encrypted Matters for DP-300

Always Encrypted is the column-level encryption model that protects sensitive data (national ID numbers, credit card numbers, health data) even from privileged database administrators and cloud operators. It is a direct response to the "untrusted admin" threat model that TDE and object-level encryption do not address. For the DP-300 exam, Always Encrypted questions cluster around: choosing deterministic vs randomized, configuring the CMK in Azure Key Vault, enabling the client driver, knowing what each encryption type can and cannot do, and now (since the syllabus update) the VBS enclave capability.

The Two-Key Hierarchy: CMK and CEK

Always Encrypted uses a two-tier key model:

• Column Master Key (CMK) - an asymmetric key (RSA) stored outside the database, typically in Azure Key Vault, a Windows Certificate Store, or another Key Store provider. The server never has the private key.
• Column Encryption Key (CEK) - a symmetric AES-256 key that actually encrypts the column data. The CEK is stored in the database wrapped (encrypted) by the CMK's public key.

The client driver holds (or can reach) the CMK, uses it to unwrap the CEK, and then encrypts parameter values before sending them to the server and decrypts column values on the way back. The server only sees ciphertext for the encrypted columns. The CMK is configured once per column; the CEK is configured per encrypted column.

Deterministic vs Randomized Encryption

When you encrypt a column you choose an encryption type:

Deterministic encryption produces identical ciphertext for identical plaintext, which lets the server perform equality operations on ciphertext without decryption. It leaks equality relationships, so it is appropriate when you need to filter or join by the column (for example, "WHERE NationalID = @n"). Randomized encryption uses a fresh initialization vector for each value, so identical plaintext produces different ciphertext - the strongest confidentiality but the server cannot perform any computation on it without an enclave. A frequent exam trap: a scenario asks how to encrypt a DateOfBirth column that must support range queries (age > 18) without pattern leakage - the correct answer is randomized encryption with secure enclaves, not deterministic, because deterministic does not support range comparisons at all.

Enabling the Client Driver

The application connection string must include Column Encryption Setting=Enabled; for the client driver to perform encryption and decryption. Without this, queries against encrypted columns either return ciphertext (treating it as varbinary) or fail. For parameterized queries, the driver matches parameters to encrypted columns by name and type - using the wrong type or a non-parameterized literal string against an encrypted column fails at runtime. Migrating data into an encrypted column requires parameterized INSERTs with the driver enabled; bulk insert of raw ciphertext is unsupported.

Always Encrypted with Secure Enclaves (VBS Enclaves)

The classic Always Encrypted limitation was that the server never saw plaintext, so it could not do range comparisons, LIKE, DISTINCT, or joins on randomized-encrypted columns. Always Encrypted with secure enclaves lifts that restriction. An enclave is a protected memory region inside the server process that the host OS and DBA cannot inspect. On Azure SQL Database and SQL Managed Instance the enclave is VBS (Virtualization-Based Security) - a software enclave backed by Hyper-V hypervisor protections, no special hardware required.

With enclaves:

• The client driver releases the CEK to the enclave after attestation. The CEK lives only inside enclave memory.
• The enclave decrypts column values, performs the operation (range comparison, LIKE pattern match, sort, join), and returns only results to the host.
• Range comparisons, LIKE, DISTINCT, and ORDER BY on randomized-encrypted columns become supported.
• In-place cryptographic operations are supported: CEK rotation and changing encryption type (deterministic <-> randomized) can be done inside the enclave without shipping data back to the client.

Enclave-Enabled CEKs

A CEK must be created as enclave-enabled at column key creation time. An enclave-enabled CEK can be used only on a server that supports enclaves and only after the client passes attestation. A non-enclave-enabled CEK cannot be retroactively promoted; you must rotate to a new enclave-enabled CEK. When a scenario says "we want to enable rich computation on already-randomized-encrypted columns", the answer involves creating a new enclave-enabled CEK and rotating the columns to use it - you cannot simply flip a flag on the existing key.

Attestation

Before the client driver releases the CEK to the enclave, it must verify the enclave is genuine and running the expected code. This is attestation. On Azure, the attestation service is Azure Attestation using the admin-defined policy (also called admin attestation). The client connection string includes Attestation Protocol=HGS (or AAS) and an enclave attestation URL pointing at the attestation service endpoint. Without attestation the client refuses to release the key, and operations requiring the enclave fail.

The exam phrasing to recognize: "Always Encrypted with secure enclaves" + "verify enclave" = attestation with Azure Attestation. The legacy Windows Host Guardian Service (HGS) term still appears in documentation for on-premises SQL Server; on Azure SQL Database the equivalent is the Microsoft Azure Attestation service.

Key Rotation

Two rotations:

• CMK rotation replaces the master key. A new CEK-wrapped-by-new-CMK is generated and stored alongside the old wrapped CEK; existing column data is unchanged because the same CEK still encrypts it. This is metadata-only and fast. Old wrapped CEK entries are removed once the new one is confirmed.
• CEK rotation replaces the symmetric key that encrypts column data. Without enclaves, the client driver must read every row, decrypt with the old CEK, re-encrypt with the new CEK, and write it back - a size-of-data operation. With enclaves and an enclave-enabled CEK, rotation happens in place inside the enclave without shipping data to the client, which is dramatically faster for large tables.

Limitations and Traps

Frequently tested limitations:

• Encrypted columns cannot participate in clustered index keys unless deterministic (randomized cannot be indexed at all without an enclave).
• Encrypted columns cannot be used with LIKE without an enclave.
• Default constraints, CHECK constraints, and computed columns referencing encrypted columns are limited.
• Switching encrypted columns between deterministic and randomized requires a CEK rotation; an enclave makes it in-place.
• The CMK must be reachable from every client that queries encrypted columns - loss of access to the Key Vault means those columns cannot be read.

When to Choose Always Encrypted

For DP-300, the decision tree is:

1. Threat model includes a compromised DBA or cloud operator? Then Always Encrypted is the answer, not TDE.
2. Need to filter, join, or group on the encrypted column? Choose deterministic encryption (it leaks equalities) or randomized + enclave if you also need to hide patterns or do range/LIKE.
3. No filtering, no joins on the column? Randomized without an enclave is the simplest secure choice.
4. Need to perform in-place key rotation or type changes on large tables? Use an enclave-enabled CEK and ensure the client driver passes attestation.

A pattern the exam favors: a scenario describes a SaaS application where the tenant's compliance team must be the only entity able to decrypt a specific column - the answer is Always Encrypted with the CMK held in the tenant's Azure Key Vault subscription, not shared with the SaaS operator. This separation of duties between data owner and service operator is the architectural property that distinguishes Always Encrypted from every other encryption option in the exam.