Under the Hood of Azure SQL Always Encrypted and Secure Enclaves
Azure SQL Database Always Encrypted allows the database engine to execute queries against encrypted data without holding the keys. Recent updates have expanded capabilities, introduced VBS enclaves, and added support for advanced operations like JOIN, GROUP BY, and ORDER BY.
What changed From the source
Azure SQL Database Always Encrypted, detailed in a SIGMOD 2020 paper, enables SQL Server to run real queries such as range comparisons and LIKE patterns against data it cannot decrypt on its own. The architecture relies on a column encryption key (CEK) to encrypt column data and a column master key (CMK) stored in a secure location like Azure Key Vault or a managed hardware security module (HSM) controlled by the user. Client drivers transparently handle encryption and decryption by calling stored procedures like sp_describe_parameter_encryption, keeping the application code working with plaintext.
Encryption comes in deterministic and randomized varieties. Deterministic mapping enables equality comparisons and B-tree indexing by mapping identical inputs to identical ciphertexts, though it leaks frequency information. Randomized encryption uses a random initialization vector, leaking only data length while rendering direct queries impossible unless paired with secure enclaves. Secure enclaves provide a protected memory region inside the SQL Server process where expressions are evaluated securely. Attestation verifies the enclave's integrity before client drivers share keys.
Since the SIGMOD 2020 paper, hardware requirements have shifted. VBS enclaves are now available across every Azure SQL Database offering and elastic pools in most regions by configuring the preferredEnclaveType property, removing the requirement for specific Intel SGX hardware (DC-series). However, VBS enclaves currently lack attestation and protect against high-privileged internal users rather than an attacker replacing the enclave binary, whereas Intel SGX paired with Microsoft Azure Attestation provides the stronger model. Additionally, SQL Server 2022 added support for JOIN, GROUP BY, and ORDER BY on encrypted columns inside the enclave, and Microsoft recommends enabling Accelerated Database Recovery before creating indexes on encrypted columns.
- CEK is a 32-byte AES key encrypting column data, while CMK encrypts the CEK and remains outside the server.
- TPC-C benchmarks with six encrypted columns showed plaintext over Always Encrypted running at 64% of baseline, and randomized encryption with enclave computation running at about 50% of baseline.
- Always Encrypted with secure enclaves supports in-place encryption, confidential queries, and index support on randomized columns.
- VBS enclaves replaced the Host Guardian Service with Azure Attestation used alongside Intel SGX.
At a glance · SQL Server & Data
What changedWhy it mattersWho should careUpgrade and migration considerations
Why it matters Analysis
For regulated industries managing health records or financial data, cloud adoption often stalls due to threat models that include the cloud operator. Traditional workarounds involve tokenization or application-side encryption libraries that break native query capabilities. Always Encrypted shifts cryptographic guarantees directly into the database engine while preserving the standard T-SQL query surface and indexing capabilities.
The separation of duties between database administrators and data readers becomes an architectural property rather than an administrative policy. By isolating key management within Azure Key Vault or a managed HSM, developers can secure sensitive columns without sacrificing database features or rewriting application data access layers.
- Enables cloud migration for workloads requiring protection against administrative server access.
- Preserves developer productivity by maintaining standard T-SQL queries and application-level plaintext visibility.
Who should care Analysis
Database administrators, cloud architects, and software engineers working with sensitive or regulated data in Azure SQL Database will find this feature directly applicable. It is particularly relevant for teams evaluating cloud security compliance where database operators or infrastructure administrators must be excluded from viewing plaintext data.
Developers aiming to minimize custom cryptographic code in their applications benefit from a transparent driver implementation that encrypts parameters and decrypts results automatically.
- Architects designing security models for multi-tenant or regulated cloud environments.
- Developers seeking to encrypt specific sensitive columns without redesigning data access patterns.
Upgrade and migration considerations Analysis
Enabling Always Encrypted incurs performance trade-offs, such as additional metadata round trips and enclave computation overhead, meaning teams should apply encryption selectively to columns that require strict protection rather than entire schemas. Benchmarking custom workloads against the specific tier and enclave configuration is recommended.
When creating indexes on encrypted columns, enabling Accelerated Database Recovery is advised to avoid long waits for applications to provide keys during index changes. Furthermore, teams must weigh the deployment flexibility of VBS enclaves against the stricter threat model and attestation capabilities offered by Intel SGX hardware.
- Target specific columns rather than whole schemas due to performance overhead.
- Enable Accelerated Database Recovery prior to creating indexes on encrypted columns.
- Evaluate the security trade-off between VBS enclaves and Intel SGX with attestation.
Sources and further reading
- Original post on Azure SQL Dev Corner (original source)
Original: “How do you run a query against data the database can’t read?”, Azure SQL Dev Corner, published 9 Nov 2026, retrieved 9 Nov 2026. This article was drafted with AI assistance from the official source and passed automated fact and quality checks. Spotted a mistake? See our corrections policy.