Study guide

6.2 Always Encrypted and VBS Enclaves

DP-300: Administering Microsoft Azure SQL Solutions Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

1.1 Current DP-300 Exam Facts 1.2 Skills Outline and Domain Weights 1.3 Study Strategy and Official Resources

2 Planning and Deploying Azure SQL Solutions

2.1 Choosing and Deploying Azure SQL Database Offerings 2.2 Automated Deployment, Azure Arc, and SQL Database in Microsoft Fabric 2.3 Table Partitioning and Sharding Strategy 2.4 Hybrid SQL Server and IaaS Patching

3 Configuring Resources for Scale and Performance

3.1 Configuring Azure SQL Database for Scale and Performance 3.2 Configuring Azure SQL Managed Instance for Scale and Performance 3.3 Configuring SQL Server on Azure VMs for Scale and Performance 3.4 Configuring Table Partitioning and Data Compression

4 Planning and Implementing a Migration Strategy

4.1 Evaluating Migration Requirements and Strategies 4.2 Implementing Online, Offline, and Azure Migrations 4.3 Migrating Between Azure SQL Services and Troubleshooting

5 Configuring Database Authentication and Authorization

5.1 Microsoft Entra ID Authentication for Azure SQL 5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting 5.3 Security Principals and Entra-Backed Users 5.4 Permissions, Least Privilege, and T-SQL Management

6 Implementing Security for Data at Rest and in Transit

6.1 Transparent Data Encryption and Object-Level Encryption 6.2 Always Encrypted and VBS Enclaves 6.3 Firewall Rules, Private Links, and Service Endpoints

7 Implementing Compliance Controls for Sensitive Data

7.1 Data Classification and Server/Database Auditing 7.2 Change Data Tracking and Dynamic Data Masking 7.3 Row-Level Security and Ledger in Azure SQL

8 Monitoring Resource Activity and Performance

8.1 Operational Baselines and Performance Metric Sources 8.2 Interpreting Metrics and Configuring Activity Monitoring 8.3 Monitoring with Database Watcher (Preview) 8.4 Monitoring with Extended Events

9 Monitoring and Optimizing Query Performance

9.1 Configuring and Monitoring with Query Store 9.2 Session Blocking and Dynamic Management Views 9.3 Index Changes and Execution Plan Review 9.4 Query Construct Modifications and Intelligent Insights

10 Configuring Database Solutions for Optimal Performance

10.1 Index, Statistics, and Integrity Maintenance 10.2 Automatic Tuning and Database-Scoped Configuration 10.3 Server Settings and Resource Governor 10.4 Compute/Storage Scaling and Intelligent Query Processing

11 Creating and Managing SQL Server Agent Jobs

11.1 Schedules for Regular Maintenance Jobs 11.2 Job Alerts, Notifications, and Agent Troubleshooting 11.3 Automating Deployment with ARM and Bicep Templates

12 Automating Deployments and Database Tasks in Azure

12.1 Azure PowerShell, Azure CLI, and Deployment Monitoring 12.2 Elastic Jobs for Cross-Database Automation 12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks

13 Planning an HA/DR Strategy and Backup and Restore

13.1 Recommending HA/DR Strategy from RPO and RTO 13.2 Hybrid and Azure-Specific HA/DR Solutions and Testing 13.3 Backup and Restore Strategy with Native Tools 13.4 Point-in-Time Restore and Recovery Operations

14 Configuring HA/DR: Long-Term Retention, Geo-Replication, and Failover Groups

14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage 14.2 Active Geo-Replication 14.3 Failover Groups

15 Advanced HA/DR: Availability Groups, FCIs, Log Shipping, and Troubleshooting

15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs 15.2 Always On Failover Cluster Instances and Log Shipping 15.3 Monitoring and Troubleshooting HA/DR Solutions

6.2 Always Encrypted and VBS Enclaves

Key Takeaways Explain This Topic AI

Always Encrypted performs encryption in the client driver using a column master key (CMK) that never leaves the client side; the server stores only a column encryption key (CEK) wrapped by the CMK

Deterministic encryption produces the same ciphertext for the same plaintext and supports equality searches, grouping, and joins; randomized encryption produces different ciphertext each time and hides patterns but blocks rich computation

Always Encrypted with secure enclaves uses VBS enclaves (virtualization-based security) to allow the server to compute on ciphertext in a protected memory region, enabling range comparisons, LIKE, and in-place key rotation without exposing plaintext to the server host

Enclave attestation via Azure Attestation (or admin attestation) verifies the enclave is genuine before the client releases the CEK; without attestation the client driver refuses to release the key

Key rotation replaces the CMK and rewraps the CEK (or, for CEK rotation, re-encrypts every value); with enclaves and randomized encryption, CEK rotation can be performed in-place on the server

Last updated: August 2026

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 :

  • Property Deterministic Randomized
  • Same plaintext -> same ciphertext? Yes No
  • Equality comparisons (=, IN, JOIN, GROUP BY, DISTINCT) Supported Not supported
  • Range comparisons (<, >, BETWEEN), LIKE, sort Not supported without enclave Supported only with enclave
  • Pattern leakage Yes - ciphertext reveals equalities No - hides patterns
  • Recommended for Lookup columns, join keys High-sensitivity columns

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.