Always Encrypted with Secure Enclaves (VBS) — Deep Dive
Always Encrypted (AE) protects sensitive columns by performing encryption and decryption client-side, so the database engine never sees the plaintext or the column encryption keys. The classic limitation: with the data encrypted, the engine could only do equality matches on deterministic columns — no LIKE, no range scans, no ORDER BY, no in-place updates.
Always Encrypted with secure enclaves removes that limitation. A trusted execution environment (TEE) runs inside the SQL engine process; the client securely shares the column keys into the enclave, and the enclave performs limited cryptographic operations on plaintext without exposing it to the rest of SQL Server. This unlocks rich queries (range, LIKE, BETWEEN) and in-place encryption.
The April 24, 2026 outline explicitly lists "Configure Always Encrypted with secure enclaves" under Domain 2 (Implement a Secure Environment). VBS enclaves on Azure SQL Database is the modern, default enclave technology — Intel SGX (used by older DC-series MI/SQL Server 2019 implementations) is now legacy.
Setup order — memorize this sequence
The exam loves drag-and-drop questions on AE ordering. Burn this in:
The app connection string flag is last — but if you change it before the CMK exists, queries fail with column encryption key not found. The CMK/CEK must exist before clients connect with Column Encryption Setting=Enabled.
Two enclave technologies — pick the right one
| Property | VBS enclaves (current default) | Intel SGX enclaves (legacy) |
|---|---|---|
| Where supported | Azure SQL DB (all regions, all SLOs incl. Hyperscale) and SQL Server 2022+ | Azure SQL DB DC-series only; SQL Server 2019 |
| Hardware required | None — uses Windows hypervisor + virtualization-based security | Intel processors with SGX |
| Attestation provider | Microsoft Azure Attestation (MAA) or none (for SQL Server you can run "no attestation") | Microsoft Azure Attestation (mandatory) |
| Performance overhead | Low | Higher (SGX paging cost) |
| Status | GA — recommended | Legacy, do not deploy new |
| Supported by | SSMS 19.2+, .NET Data Provider for SQL Server (Microsoft.Data.SqlClient ≥ 5.2) | Same drivers, older versions |
If a question mentions "Always Encrypted enclaves on Azure SQL Database in West Europe with no DC-series VM size", the answer is VBS enclaves with Microsoft Azure Attestation — VBS works on every SLO, SGX does not.
Architecture
Prerequisites
| # | Requirement |
|---|---|
| 1 | Azure SQL Database in any region (or SQL Server 2022 / 2025 on Windows Server 2019+) |
| 2 | Azure Key Vault (Standard or Premium) with the user/MI granted Key Vault Crypto User |
| 3 | Microsoft Azure Attestation (MAA) provider (one per region recommended) |
| 4 | Client tooling: SSMS 19.2+ or Microsoft.Data.SqlClient ≥ 5.2 (.NET) / ≥ 5.x (Python/Node via ODBC ≥ 18.3) |
| 5 | Connection string flag: Column Encryption Setting=Enabled;Attestation Protocol=AAS;Enclave Attestation Url=https://<maa>.attest.azure.net/attest/SgxEnclave |
Step 1 — Enable VBS enclaves on the database
-- Run on master (Azure SQL DB)
ALTER DATABASE SCOPED CONFIGURATION
SET PREFERRED_ENCLAVE_TYPE = 'VBS';
GO
For Azure SQL DB the setting is per database. To check the current state:
SELECT name, value
FROM sys.database_scoped_configurations
WHERE name = 'PREFERRED_ENCLAVE_TYPE';
Step 2 — Create the attestation provider (one-time, per region)
az attestation create \
--name ehabmaa1 \
--resource-group rg-security \
--location westeurope
Then capture the URL (you'll need it in connection strings):
az attestation show -n ehabmaa1 -g rg-security --query attestUri -o tsv
# https://ehabmaa1.weu.attest.azure.net
Important: for VBS enclaves the attestation policy can stay at the default (Microsoft-signed). For SQL Server on-prem you may also choose no attestation by setting Attestation Protocol=None in the connection string — but on Azure SQL DB attestation is required.
Step 3 — Create the enclave-enabled keys
3a — Column Master Key (CMK) in Key Vault
CREATE COLUMN MASTER KEY [CMK_PII_Enclave]
WITH (
KEY_STORE_PROVIDER_NAME = 'AZURE_KEY_VAULT',
KEY_PATH = 'https://kv-ehab-sec.vault.azure.net/keys/cmk-pii/abcdef0123456789',
ENCLAVE_COMPUTATIONS (SIGNATURE = 0x...) -- generated by SSMS / PowerShell
);
The ENCLAVE_COMPUTATIONS clause tells SQL "this key may be used inside an enclave." The signature is computed by your client over the CMK metadata and signed using your CMK in Key Vault — preventing a malicious DBA from silently swapping the key.
The easy path is SSMS → Always Encrypted Keys → New Column Master Key with the Allow enclave computations checkbox.
3b — Column Encryption Key (CEK)
CREATE COLUMN ENCRYPTION KEY [CEK_PII]
WITH VALUES (
COLUMN_MASTER_KEY = [CMK_PII_Enclave],
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = 0x01700000016... -- generated by tooling
);
Step 4 — Encrypt columns in place (the killer feature)
Before VBS enclaves you had to export, re-encrypt, and re-import the column. Now you can do it in place with a single DDL statement:
ALTER TABLE dbo.Patients
ALTER COLUMN [SSN] CHAR(11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (
COLUMN_ENCRYPTION_KEY = [CEK_PII],
ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
) NOT NULL
WITH (ONLINE = ON);
Requirements for in-place encryption:
- The connection must have
Column Encryption Setting=Enabledand a valid attestation URL. - The CMK must be marked enclave-enabled.
- The user must have
ALTER ANY COLUMN ENCRYPTION KEY+VIEW ANY COLUMN ENCRYPTION KEYplus Key Vault crypto permissions.
The SQL engine ships ciphertext to the enclave, which decrypts, re-encrypts under the new key/type, and writes back — all without plaintext leaving the enclave.
Step 5 — Run rich queries
-- Range query on a RANDOMIZED encrypted column — only possible with enclaves
SELECT FirstName, LastName
FROM dbo.Patients
WHERE Salary BETWEEN @minSalary AND @maxSalary
ORDER BY Salary;
-- LIKE on an encrypted column
SELECT *
FROM dbo.Patients
WHERE SSN LIKE '123-45-%';
The driver ships parameterized ciphertext + a plan that defers the predicate to the enclave; the enclave decrypts, evaluates, and returns matched ciphertext rows.
Indexes on enclave columns are limited. Only enclave-enabled indexes support range/LIKE seeks; build them with CREATE INDEX ... WITH (COLUMN_ENCRYPTION_KEY = ...). Without an enclave-enabled index, rich queries fall back to a scan-and-decrypt-in-enclave, which is correct but slow on large tables.
Operational model — what each role can / cannot see
| Role | Can read plaintext? | Can run rich queries? | Can rotate keys? |
|---|---|---|---|
| Application user with valid AE driver + KV access | ✅ | ✅ | ❌ |
| DBA (no KV access) | ❌ | ✅ (against ciphertext via enclave) | ❌ |
| Cloud operator (Microsoft) | ❌ | ❌ | ❌ |
| Key admin in Azure Key Vault | ✅ (if also has DB access) | n/a | ✅ |
This separation of duties is exactly the value proposition: DBAs operate the database, key admins control the keys, neither alone sees plaintext.
Limitations (memorize these)
- Computed columns, full-text indexes, sparse columns, change tracking on encrypted columns — not supported.
- Triggers can reference encrypted columns but only via deterministic-style equality unless inside an enclave-aware connection.
INSTEAD OFtriggers are unsupported on tables with enclave-enabled encrypted columns.- In-memory OLTP tables cannot use AE with enclaves.
- Enclave-enabled indexes have a single-column limitation; composite enclave indexes are not supported (as of April 2026).
- Cross-database queries with enclave columns require both DBs on the same server with matching CMK definitions.
Troubleshooting cheatsheet
| Symptom | Likely cause | Fix |
|---|---|---|
Failed to open the attestation token | Missing/wrong Enclave Attestation Url | Use the exact MAA URL from az attestation show |
The parameter could not be encrypted because the column encryption key for the column is not enclave enabled | CMK not marked ENCLAVE_COMPUTATIONS | Recreate CMK with the enclave option |
| Range query returns full scan | No enclave-enabled index on the column | CREATE INDEX ... WITH (COLUMN_ENCRYPTION_KEY=...) |
Operation not allowed: 'Column Encryption Setting' is set to Disabled | Connection string missing AE flag | Add Column Encryption Setting=Enabled; |
In-place ALTER COLUMN fails with enclave not available | Database scoped config not set to VBS | ALTER DATABASE SCOPED CONFIGURATION SET PREFERRED_ENCLAVE_TYPE='VBS' |
Oracle DBA parallel: The Oracle equivalent of Always Encrypted is TDE column encryption combined with Data Redaction — but Oracle decrypts at the server and only redacts on output. Always Encrypted with enclaves is conceptually closer to Oracle Confidential Computing on OCI with vTEEs: predicate evaluation happens inside an isolated, attested execution environment so the server administrator can never see plaintext.
End-to-end PowerShell setup (copy-paste reference)
# Pre-reqs: Az.Sql 5.x+, SqlServer 22.x+
Import-Module SqlServer
$server = 'ehab-sqlsrv01.database.windows.net'
$database = 'patients'
$kvName = 'kv-ehab-sec'
$cmkName = 'cmk-pii'
# 1. Create CMK in Key Vault
$cmk = Add-AzKeyVaultKey -VaultName $kvName -Name $cmkName -Destination 'Software'
# 2. Open AE-enabled connection to SQL
$conn = Connect-SqlInstance `
-ServerInstance $server -Database $database `
-AccessToken (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token `
-ColumnEncryptionSetting Enabled `
-AttestationProtocol AAS `
-EnclaveAttestationUrl 'https://ehabmaa1.weu.attest.azure.net/attest/SgxEnclave'
# 3. Register CMK metadata in DB (signed for enclave use)
$cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyUrl $cmk.Id `
-AllowEnclaveComputations
New-SqlColumnMasterKey -Name 'CMK_PII_Enclave' `
-InputObject $database -ColumnMasterKeySettings $cmkSettings
# 4. Create CEK
New-SqlColumnEncryptionKey -Name 'CEK_PII' `
-InputObject $database -ColumnMasterKey 'CMK_PII_Enclave'
# 5. Encrypt columns in-place (rich operation, runs inside the enclave)
$encryptionChanges = @(
New-SqlColumnEncryptionSettings -ColumnName 'dbo.Patients.SSN' `
-EncryptionType Randomized -EncryptionKey 'CEK_PII'
New-SqlColumnEncryptionSettings -ColumnName 'dbo.Patients.Salary' `
-EncryptionType Randomized -EncryptionKey 'CEK_PII'
)
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $encryptionChanges
Anti-Patterns
- "Use Intel SGX (DC-series) for Always Encrypted with Enclaves on Azure SQL DB." Legacy. Microsoft now recommends VBS enclaves for new deployments. SGX is going into maintenance mode.
- "Encrypt every column with Always Encrypted." AE blocks
LIKE, range comparisons, joins, ORDER BY (without Enclaves) and adds CPU + driver complexity. Encrypt PII/PHI/financial columns — not the whole table. - "Use deterministic encryption everywhere because we need equality lookups." Deterministic enables equality joins but leaks frequency (low-cardinality columns are guessable). For high-sensitivity columns, use randomized + Enclaves so range/equality run inside the enclave.
- "Skip attestation — we trust the host." Azure SQL DB requires Microsoft Azure Attestation (MAA) for Enclaves. Without it, the Enclave doesn't get released keys. SQL Server on-prem allows attestation = None for dev/test only.
- "Old client driver — we'll add
Column Encryption Setting=Enabledand pray." AE requires modern drivers (.NET 4.6.2+, ODBC 17+, JDBC with mssql-jdbc-12.x+, OLEDB provider 19+). Old drivers send plaintext. - "Store the Column Master Key (CMK) in the local Windows Certificate Store." Works for dev. Production = Azure Key Vault so the CMK is HSM-backed, rotatable, and revocable centrally.
Always Encrypted with Secure Enclaves needs Attestation URL in the connection string for Azure SQL DB. Forgetting this is the #1 reason "queries that should run in the enclave silently fall back to the client and fail with Operand type clash."
Migration Between Encryption Models
| From → To | Path | Cost |
|---|---|---|
| Plaintext column → AE (no Enclaves) | Configure CMK in AKV, CEK in DB, encrypt via SSMS / PowerShell | Offline migration of column data; online for new inserts |
| Plaintext → AE with Enclaves | Same as above + ENABLE ENCLAVE TYPE + attestation URL | In-place encryption inside enclave — no client roundtrip |
| AE (no Enclaves) → AE with Enclaves | Add Enclave to server config + attestation URL; re-encrypt rich columns | In-place; gain LIKE / range / sort on encrypted columns |
| Intel SGX Enclaves → VBS Enclaves | Switch DC-series → standard SKU + change enclaveType setting | Brief reconfig; SGX going legacy |
| AE Deterministic → AE Randomized | Set-SqlColumnEncryption with EncryptionType Randomized | Re-encryption per row; loses deterministic equality outside Enclave |
| AE Randomized → AE Deterministic | Same operation, reverse | Loses entropy; gains equality joins outside Enclave |
| AE → plaintext | Decrypt via Set-SqlColumnEncryption | Removes encryption protection; only for legitimate rollback |
| CMK in Cert Store (dev) → CMK in AKV (prod) | Add CMK to AKV, re-encrypt CEK with new CMK, drop old CMK | Online; gains HSM-backing + central rotation |
Most expensive moves: plaintext → AE on a populated table (full re-encryption write storm) and CMK rotation while live (must re-encrypt all CEKs).
Real Scenarios
- Healthcare app, PHI columns, need range queries on
date_of_birth→ AE with VBS Enclaves + MAA + Randomized encryption + AKV-backed CMK. Driver: HIPAA + range queries. Trade-off: VBS-capable SQL DB tier + driver upgrade across all clients. - Financial app, encrypted SSN, equality lookup only → AE Deterministic, no Enclaves, AKV CMK. Driver: simple, broad client support. Trade-off: SSN is unique-cardinality so frequency leak is tolerable.
- Legacy .NET 4.5 app, can't upgrade driver → DDM + RLS instead of AE. Driver: AE driver minimum not met. Trade-off: confidentiality vs hostile DBA is weaker.
- Multi-region prod, key custody required → AE + Enclaves + CMK in AKV Premium HSM with multi-region replication. Driver: regulator key custody + cross-region failover. Trade-off: HSM pricing + multi-region key sync runbook.
- Dev environment for the same regulated app → AE with Enclaves + attestation = None (SQL Server local) + CMK in Cert Store. Driver: full feature parity, no AKV cost. Trade-off: must NOT promote dev keys to prod.
Flashcards
Practice quiz
Sources & further reading
- Always Encrypted with secure enclaves — Microsoft Learn
- Configure VBS enclaves on Azure SQL Database
- Microsoft Azure Attestation overview
- Manage keys for Always Encrypted with enclaves (PowerShell)
- DP-300 study guide (April 24, 2026)
Last verified against MS Learn: April 28, 2026.