Always Encrypted

The Finch Rewards app stores a little personal information about each member: name, email, phone number and date of birth. Since 2025 it has also stored one more sensitive item for members who opt in to age-restricted promotions in some stores, a scanned identification number. Carmen Ortiz, the privacy officer, asks the question that transparent data encryption cannot answer: "Who at Harrow & Finch, or at Microsoft, can read that number?" With TDE alone, the answer is anyone who can query the table, including every DBA. Elena's answer for that column is Always Encrypted.

What Always Encrypted changes

Always Encrypted encrypts chosen columns on the client, in the database driver, before the data is sent to the server. The server stores and returns only ciphertext. The keys that decrypt the data never reach the database engine; they stay with the client application, in a key store such as Azure Key Vault. A DBA who queries the table sees binary values. A copy of the database restored elsewhere shows the same binary values. Even Microsoft operators, or an attacker who controls the server, cannot read the column without access to the key store. This separation of the people who manage data from the people who may read it is the main reason to use it.

The driver does the work. When an application with access to the keys runs a parameterized query, the driver asks the server which parameters target encrypted columns, encrypts those parameter values, sends the query, and decrypts encrypted values in the results. The application code changes very little: the connection string gains the setting Column Encryption Setting=Enabled, and queries must use parameters rather than literal values for encrypted columns. Microsoft's drivers for .NET, Java, ODBC and others support it, as do SQL Server Management Studio and the PowerShell module, for administrators who are allowed the keys.

Two keys

Always Encrypted uses two kinds of key. A column encryption key encrypts the data in one or more columns. The database stores it, but only in encrypted form. A column master key protects the column encryption keys. It is stored outside the database, in Azure Key Vault, Managed HSM, the Windows certificate store or a hardware security module, and the database holds only metadata saying where it is. To read an encrypted column, a client must be able to reach the column master key, use it to decrypt the column encryption key, and use that to decrypt the data. Access to the key store is therefore access to the data, and granting the Finch Rewards API's managed identity permission to use the master key in Key Vault is the decision that matters.

Deterministic and randomized encryption

Each encrypted column uses one of two encryption types. Deterministic encryption always produces the same ciphertext for the same plaintext. That allows equality comparisons: the server can find rows where the encrypted IdNumber equals an encrypted parameter, can join on it, group by it and index it. The cost is that patterns are visible: someone looking at the ciphertext can tell which rows share a value, and for columns with few possible values, such as a yes or no flag, that can reveal a lot. Randomized encryption produces different ciphertext each time, even for the same value. It hides patterns completely, but the server can do nothing with the column except store and return it.

Deterministic encryption of a text column also requires a binary collation with a code-point comparison, a collation ending in BIN2, because comparisons happen on encrypted bytes. Hana's choice for Finch Rewards is deterministic encryption for IdNumber, because the age-check process looks members up by it, and randomized encryption for DateOfBirth, which is only ever displayed back to the member. Email stays unencrypted by Always Encrypted, because the store systems search it by partial matches, which neither encryption type can support without enclaves; it is protected by TDE, permissions and masking instead.

Secure enclaves

Always Encrypted with secure enclaves extends what the server can do with encrypted data. A secure enclave is a protected region of memory inside the database engine's process that the rest of the process, the operating system and administrators cannot read. When the keys are marked as enclave-enabled, the driver can securely hand a column encryption key to the enclave, and the enclave can then evaluate comparisons such as ranges and pattern matching, sort data and even encrypt existing data in place, all on plaintext that never leaves the enclave. Without enclaves, encrypting an existing column means moving all its data out to a client tool and back.

Azure SQL Database supports two enclave technologies. Virtualization-based security enclaves are software-based and need no special hardware; they work on the standard hardware options and the DTU model, and in Azure SQL Database they do not support attestation. Intel SGX enclaves are hardware-based, available only on the DC-series hardware, and require attestation through Microsoft Azure Attestation, a service that proves to the client that the enclave is genuine before keys are sent to it. Microsoft has announced the end of support for SGX enclaves on October 31, 2027, after which databases still on DC-series will be moved to standard hardware with virtualization-based enclaves, so new designs should start with virtualization-based security. SQL Server 2019 and later also support virtualization-based enclaves, with optional attestation through Host Guardian Service.

Setting it up

The wizard in SQL Server Management Studio walks through the steps: choose the columns, choose the encryption type, create or select a column master key and a column encryption key, and encrypt the existing data. Without an enclave, the wizard reads the data out, encrypts it on the machine running the wizard and writes it back, so it needs a maintenance window for large tables and a machine close to the database. PowerShell cmdlets do the same in scripts. Behind the scenes, two metadata statements describe the keys, and the column definitions gain an ENCRYPTED WITH clause.

CREATE COLUMN MASTER KEY CMK_FinchRewards
WITH (KEY_STORE_PROVIDER_NAME = 'AZURE_KEY_VAULT',
      KEY_PATH = 'https://kv-hf-rewards-test-cc.vault.azure.net/keys/ae-cmk/7d1c2a',
      ENCLAVE_COMPUTATIONS (SIGNATURE = 0x0170A3...));

CREATE TABLE rewards.MemberId (
    MemberId int NOT NULL PRIMARY KEY,
    IdNumber varchar(20) COLLATE Latin1_General_BIN2
        ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK_Rewards,
                        ENCRYPTION_TYPE = DETERMINISTIC,
                        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
    DateOfBirth date
        ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK_Rewards,
                        ENCRYPTION_TYPE = RANDOMIZED,
                        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL);

Permissions are split deliberately. Creating and managing keys needs the database permissions ALTER ANY COLUMN MASTER KEY and ALTER ANY COLUMN ENCRYPTION KEY, plus access to the key store. Reading encrypted data needs VIEW ANY COLUMN MASTER KEY DEFINITION and VIEW ANY COLUMN ENCRYPTION KEY DEFINITION in the database, plus permission in Key Vault to unwrap with the master key. Elena gives Walt the database permissions to manage the metadata but not the Key Vault permission to unwrap, so Walt can administer the column without ever reading it. Carmen's privacy team holds the Key Vault role that allows the key to be used, and grants it to the API's managed identity.

Limits to plan for

Always Encrypted is powerful but not free. Encrypted columns take more space, because ciphertext is longer than plaintext and includes an authentication tag. Not every data type or feature works with encrypted columns: columns with default constraints, computed columns based on them, full-text search and some replication scenarios are restricted. Queries must be parameterized, so ad hoc reports that type literal values fail with an error about operand type clash. Rotating a column master key is a metadata operation that re-encrypts the column encryption keys, which is quick; rotating a column encryption key means re-encrypting the data, which is slow without an enclave. Hana keeps the list of restrictions next to her schema design and moves every query on the encrypted table into stored procedures with parameters.

Rotating keys and planning access

Key rotation needs a plan before it is needed. Rotating the column master key is the routine case: a new master key is created in Key Vault, the column encryption keys are re-encrypted with it, the old key's metadata is removed once every client can use the new one, and the old key is disabled. The data itself is untouched, so the work takes seconds. The SQL Server Management Studio wizard and the PowerShell cmdlets both support it, and they can run while the application is online, provided every client application has permission to use both the old and new keys during the switch. Hana writes that into the runbook as a two-step change a week apart: grant the API access to the new key first, rotate second.

Rotating a column encryption key is rare and heavy. It is needed only if a key may have been exposed, because it means decrypting and re-encrypting every value in every column the key protects. Without an enclave, that work happens on a client machine, which reads the column out and writes it back. With an enclave-enabled key, the server can do it in place. For Finch Rewards, with fewer than 200,000 members, either way would take minutes; for a table with hundreds of millions of rows it would need careful scheduling, which is one more argument for enclaves on large encrypted tables.

Access to the master key is the real control, so it should be reviewed like database permissions. In Key Vault, Carmen's team uses role-based access with the Key Vault Crypto User role on the single key, not on the whole vault, and grants it to two principals only: the API's managed identity and a small Entra group for support staff who must sometimes read an ID number to resolve a complaint. Key Vault logs every unwrap operation, so the privacy team can see exactly when the key was used and by whom, which is a better record than any database audit could give for that column.