Always Encrypted in SQL Server and Azure SQL: deterministic vs randomised encryption

Always Encrypted encrypts selected columns inside the client driver, so plaintext data and keys never appear in the Database Engine. It separates data owners from data managers such as on-premises DBAs and cloud operators. It’s available in SQL Server, Azure SQL Database and Azure SQL Managed Instance; it isn’t available in SQL database in Fabric. Figure 7.1 shows where the keys live and what each side sees.

Keys and key stores

Always Encrypted uses two kinds of keys:

• A column encryption key (CEK) encrypts the data in one or more columns.
• A column master key (CMK) is a key-protecting key that encrypts CEKs. It’s stored outside the database, in Azure Key Vault, the Windows certificate store or a hardware security module.

The database stores only metadata: the CMK’s location and the encrypted value of each CEK. Because the engine must never see keys in plaintext, T-SQL can’t provision keys or encrypt existing data; SSMS, PowerShell or SqlPackage do it outside the database. Four database permissions govern the feature.

In SQL Server the public role holds the two VIEW ANY permissions by default; in Azure SQL Database it doesn’t, so they must be granted explicitly before users can work with encrypted columns.

Deterministic versus randomized encryption

Both types use the AEAD_AES_256_CBC_HMAC_SHA_256 algorithm; the choice is about what queries remain possible.

Joining two deterministically encrypted columns needs both to use the same CEK. The documented operand clash error (Msg 206) appears when a query compares an encrypted column with a literal or a plaintext column, or copies data between encrypted and plaintext columns with INSERT...SELECT, SELECT INTO, UPDATE or BULK INSERT. Applications must pass values as parameters; in SSMS, turn on Parameterization for Always Encrypted.

CREATE TABLE dbo.Patients (
    PatientId int IDENTITY PRIMARY KEY,
    NationalId char(11) COLLATE Latin1_General_BIN2
        ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK1,
                        ENCRYPTION_TYPE = DETERMINISTIC,
                        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
    BirthDate date
        ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK1,
                        ENCRYPTION_TYPE = RANDOMIZED,
                        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
);

NationalId supports point lookups and an index; BirthDate can only be stored and returned. The client turns the feature on with Column Encryption Setting=Enabled in a Microsoft.Data.SqlClient connection string (ODBC uses ColumnEncryption=Enabled), and needs access to the CMK in its key store; the Azure Key Vault provider for .NET ships as a separate NuGet package.

Limitations to design around

Always Encrypted can’t be applied to columns that are xml, rowversion, sql_variant, hierarchyid, spatial or vector types; that have IDENTITY or a DEFAULT constraint; that are referenced by CHECK constraints, used as partitioning columns, included in full-text indexes, masked with DDM, or captured by change data capture. Queries using FOR JSON or FOR XML aren’t supported, nor are table-valued parameters targeting encrypted columns. Randomized columns can’t be primary keys, index keys or unique-constraint columns. Transactional, merge and snapshot replication don’t work on encrypted columns, although availability groups do. After changing an encrypted column, run sp_refresh_parameter_encryption for dependent modules.

Always Encrypted with secure enclaves

A secure enclave is a protected memory region inside the engine process where the driver can share CEKs so the server can compute on plaintext. It adds rich confidential queries and in-place encryption: encrypting, re-encrypting (key rotation or type change) and decrypting a column with ALTER TABLE ... ALTER COLUMN without moving data to the client.

Key facts:

• Rich operations need randomized encryption with an enclave-enabled CEK (a CEK protected by a CMK with ENCLAVE_COMPUTATIONS); deterministic columns still allow only equality.
• The database compatibility level should be 160 or higher.
• Platforms: SQL Server 2019 and later on Windows (VBS enclaves) and Azure SQL Database (VBS enclaves, or Intel SGX on DC-series hardware). Azure SQL Managed Instance isn’t listed.
• Intel SGX enclaves reach end of support on 31 October 2027; databases still on DC-series are then moved to standard-series hardware with VBS enclaves.
• Attestation: Microsoft Azure Attestation is mandatory for SGX; VBS enclaves in Azure SQL Database don’t support attestation; SQL Server VBS enclaves use Host Guardian Service or no attestation.
• Enable accelerated database recovery before creating the first index on an enclave-enabled randomized column (it’s on by default in Azure SQL).
• Only Windows Certificate Store and Azure Key Vault can hold enclave-enabled CMKs.

Common trap: Expecting range or LIKE queries on a deterministically encrypted column once enclaves are enabled - enclave computations require randomized encryption; deterministic columns remain limited to equality.

Common trap: Planning Always Encrypted for SQL database in Microsoft Fabric - the Fabric limitations page lists Always Encrypted as not supported and says Always Encrypted tables can’t be created there; use cell-level encryption or keep the data in Azure SQL Database.

Get the whole book

This note is one section of Ultra Transcenders DP-800: Developing AI-Enabled Database Solutions, an independent study guide that explains every topic the exam covers by technology, with comparison tables, diagrams and the common traps, plus a glossary linked to Microsoft Learn.

About the book · DP-800 terms in the glossary · All DP-800 study notes