Always Encrypted Concepts, Client-Side Protection, Secure Enclaves, and Application Implications

Learning outcomes

ServiceHub now needs to store a regulatory identifier that database administrators should not be able to read merely because they manage backups, indexes and availability. TDE cannot solve that requirement because authorized queries are decrypted inside the Database Engine. Always Encrypted moves column encryption/decryption to an enabled client driver so plaintext keys and ordinary plaintext values do not need to be available to the Database Engine.

That stronger separation also moves complexity into application architecture: key stores, driver configuration, parameter metadata, supported operators, deployment pipelines and—when secure enclaves are used—Windows/VBS and attestation become part of correctness and availability.

1. The database stores key metadata, not the plaintext column master key

SELECT SERVERPROPERTY('ProductVersion') AS product_version,       SERVERPROPERTY('ProductUpdateLevel') AS update_level,       SERVERPROPERTY('Edition') AS edition,       SERVERPROPERTY('EngineEdition') AS engine_edition,       SERVERPROPERTY('IsIntegratedSecurityOnly') AS windows_auth_only;GOSELECT name,value_in_useFROM sys.configurationsWHERE name IN  (N'contained database authentication',   N'column encryption enclave type',   N'clr enabled',N'xp_cmdshell');GOSELECT DB_NAME() AS database_name,       ORIGINAL_LOGIN() AS original_login,       SUSER_SNAME() AS execution_login,       USER_NAME() AS database_user;GO

USE master;GOIF DB_ID(N'ServiceHubSecurityLab') IS NULL  CREATE DATABASE ServiceHubSecurityLab;GOALTER DATABASE ServiceHubSecurityLab SET COMPATIBILITY_LEVEL = 170;GOUSE ServiceHubSecurityLab;GOIF SCHEMA_ID(N'ops') IS NULL EXEC(N'CREATE SCHEMA ops AUTHORIZATION dbo;');IF SCHEMA_ID(N'api') IS NULL EXEC(N'CREATE SCHEMA api AUTHORIZATION dbo;');IF SCHEMA_ID(N'sec') IS NULL EXEC(N'CREATE SCHEMA sec AUTHORIZATION dbo;');IF SCHEMA_ID(N'lab14') IS NULL EXEC(N'CREATE SCHEMA lab14 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'ops.WorkOrderSecure',N'U') IS NULLBEGIN  CREATE TABLE ops.WorkOrderSecure  (    work_order_id bigint IDENTITY(14001,1) NOT NULL      CONSTRAINT PK_ops_WorkOrderSecure PRIMARY KEY,    tenant_code char(3) NOT NULL,    customer_code varchar(16) NOT NULL,    customer_name nvarchar(100) NOT NULL,    customer_email varchar(200) NOT NULL,    status varchar(16) NOT NULL,    priority tinyint NOT NULL,    amount decimal(12,2) NOT NULL,    opened_at datetime2(0) NOT NULL,    notes nvarchar(400) NULL,    CONSTRAINT CK_ops_WorkOrderSecure_tenant      CHECK (tenant_code IN ('N01','W02','E03')),    CONSTRAINT CK_ops_WorkOrderSecure_status      CHECK (status IN ('OPEN','ASSIGNED','CLOSED','ESCALATED')),    CONSTRAINT CK_ops_WorkOrderSecure_priority CHECK (priority BETWEEN 1 AND 5)  );  INSERT ops.WorkOrderSecure    (tenant_code,customer_code,customer_name,customer_email,status,priority,amount,opened_at,notes)  VALUES    ('N01','CUST-1401',N'Mina Rahimi','mina@example.test','OPEN',2,240.00,'2026-08-01T09:00:00',N'North queue'),    ('N01','CUST-1402',N'Owen Brooks','owen@example.test','ASSIGNED',3,480.00,'2026-08-01T10:15:00',N'North dispatch'),    ('W02','CUST-1403',N'Sara Chen','sara@example.test','ESCALATED',5,860.00,'2026-08-01T11:30:00',N'West escalation'),    ('W02','CUST-1404',N'Ali Reza','ali@example.test','OPEN',1,120.00,'2026-08-02T08:20:00',N'West queue'),    ('E03','CUST-1405',N'Nora Bell','nora@example.test','CLOSED',4,735.00,'2026-08-02T09:40:00',N'East closed'),    ('E03','CUST-1406',N'David Kim','david@example.test','OPEN',2,315.00,'2026-08-02T12:05:00',N'East queue');  CREATE INDEX IX_ops_WorkOrderSecure_tenant_status    ON ops.WorkOrderSecure(tenant_code,status,opened_at)    INCLUDE(customer_code,priority,amount);END;GO

Always Encrypted uses two key layers. A column encryption key (CEK) encrypts the actual column values. A column master key (CMK) encrypts CEKs and is controlled outside the database in a trusted key store such as the Windows certificate store, Azure Key Vault or a hardware security module. SQL Server stores metadata describing the CMK location plus encrypted CEK values; it is not supposed to receive the plaintext CMK.

USE ServiceHubSecurityLab;GOSELECT name,column_master_key_id,key_store_provider_name,key_path,       allow_enclave_computations,signatureFROM sys.column_master_keys;GOSELECT cek.name,cek.column_encryption_key_id,       cekv.column_master_key_id,cekv.encryption_algorithm_name,       DATALENGTH(cekv.encrypted_value) AS encrypted_cek_bytesFROM sys.column_encryption_keys AS cekJOIN sys.column_encryption_key_values AS cekv  ON cekv.column_encryption_key_id=cek.column_encryption_key_id;GOSELECT t.name AS table_name,c.name AS column_name,       c.encryption_type_desc,c.encryption_algorithm_name,       cek.name AS column_encryption_keyFROM sys.columns AS cJOIN sys.tables AS t ON t.object_id=c.object_idLEFT JOIN sys.column_encryption_keys AS cek  ON cek.column_encryption_key_id=c.column_encryption_key_idWHERE c.encryption_type IS NOT NULL;GO

An empty result is a valid baseline: it means this disposable database has not provisioned Always Encrypted metadata. Catalog access proves metadata exists; it never proves the caller can open the external key store or decrypt data.

2. Deterministic and randomized encryption trade query capability for information leakage

Choose encryption type from the access pattern and threat model. Do not use deterministic encryption for a boolean-like or tiny category domain simply because equality search is convenient; frequency can reveal the underlying business meaning.

3. The client driver is part of the trusted computing base

An Always Encrypted-capable driver reads column-encryption metadata, uses the configured key-store provider to unwrap the CEK, encrypts parameter values before they are sent, and decrypts encrypted result values after they return. Applications must use parameters whose types/lengths match encrypted columns. Concatenating plaintext into SQL text bypasses that parameter-encryption workflow and reintroduces SQL-injection risk.

var cs = new SqlConnectionStringBuilder{    DataSource = "db01.servicehub.example",    InitialCatalog = "ServiceHubSecurityLab",    Encrypt = SqlConnectionEncryptOption.Strict,    TrustServerCertificate = false,    ColumnEncryptionSetting = SqlConnectionColumnEncryptionSetting.Enabled};await using var cn = new SqlConnection(cs.ConnectionString);await cn.OpenAsync();await using var cmd = cn.CreateCommand();cmd.CommandText = "SELECT customer_code FROM ops.SensitiveCustomer WHERE national_id=@national_id";var p = cmd.Parameters.Add("@national_id", SqlDbType.NVarChar, 24);p.Value = userSuppliedNationalId;  // driver encrypts after metadata discoveryvar customer = (string?) await cmd.ExecuteScalarAsync();

The exact driver/API version is a production prerequisite, not trivia. Key-store providers can require additional packages, credentials or platform configuration. Keep CMK access out of connection strings and source control. A deployment that can create encrypted-column metadata but cannot retrieve keys from the client environment is incomplete.

SELECT name,value,value_in_use,is_dynamic,is_advancedFROM sys.configurationsWHERE name=N'column encryption enclave type';GO

If the row is absent or value_in_use is zero, that alone does not say whether ordinary Always Encrypted works; secure enclaves are an additional capability.

4. Secure enclaves move selected computations into protected server memory

Always Encrypted with secure enclaves allows richer operations while keeping plaintext confined to a protected enclave. On SQL Server 2019 and later, the supported SQL Server enclave technology is virtualization-based security (VBS) on Windows; Intel SGX is not the SQL Server boxed path. The client driver must support enclave computations and, when configured, perform attestation before sending sensitive values for enclave processing.

-- Optional Windows SQL Server 2019+ lab after VBS prerequisites are met.EXEC sys.sp_configure N'column encryption enclave type',1;RECONFIGURE;GO-- Restart the SQL Server instance, then verify:SELECT name,value,value_in_useFROM sys.configurationsWHERE name=N'column encryption enclave type';GO

Changing the enclave type is an instance-wide configuration and requires restart. The mandatory lesson therefore observes but does not change it. If attestation is disabled for a development lab, document that the client is intentionally not validating enclave integrity and do not quietly carry that assumption into production.

5. Failure modes reveal where the security boundary really lives

If an application connects without Always Encrypted enabled, SQL Server sees ciphertext columns as encrypted binary payloads and cannot transparently transform ordinary plaintext parameters into compatible encrypted values. If the client cannot access the CMK, metadata discovery succeeds but decryption/encryption fails at the client. If a query uses an unsupported operation, the server cannot “just decrypt it” to satisfy the request.

USE ServiceHubSecurityLab;GOSELECT DB_NAME() AS database_name,       (SELECT COUNT(*) FROM sys.column_master_keys) AS cmk_metadata_count,       (SELECT COUNT(*) FROM sys.column_encryption_keys) AS cek_metadata_count,       (SELECT COUNT(*) FROM sys.columns WHERE encryption_type IS NOT NULL) AS encrypted_columns;GO-- For a real deployment, inventory outside SQL Server too:-- driver + version, CMK provider, key-store object/version,-- application identity allowed to open the CMK, rotation runbook,-- enclave/attestation settings, and recovery path.

The aggregate above is intentionally only a database metadata probe. A real readiness check must include external key-store permissions and application driver configuration, because those critical dependencies are outside SQL Server metadata.

6. Production judgment

Production judgment. Prefer randomized encryption where query requirements allow it. Test parameter types, collation, indexes and all query patterns under the exact client driver. Model CMK rotation and application rollout as a coordinated deployment. For secure enclaves, record Windows/VBS state, SQL Server configuration, restart/change-control, driver version and attestation policy. Always Encrypted protects against privileged database operators only if key-store administration remains separated from them.

Verification checklist

• Inventory CMK/CEK/encrypted-column metadata and confirm the external key store is intentionally outside SQL Server.
• List every query that must filter, join, group, sort or pattern-match the sensitive column.
• Verify the production client driver supports the required Always Encrypted/enclave behavior.
• If considering VBS enclaves, verify Windows/VBS and attestation prerequisites before enabling the server option.
• Test key rotation and application recovery; do not treat a successful table definition as proof of operational readiness.

Authoritative references

• Always Encrypted — architecture, encryption types and query limitations
• Always Encrypted cryptography — CMK/CEK hierarchy and algorithms
• Always Encrypted with secure enclaves — VBS enclave and attestation models
• Develop applications using secure enclaves — client-driver and attestation responsibilities
• Plan SQL Server secure enclaves without attestation — Windows/VBS requirements and security tradeoff
• Microsoft.Data.SqlClient — Always Encrypted and secure-enclave client support
• SQL Server 2025 editions and supported features — Always Encrypted/secure-enclave edition support
• SQL Server security best practices — column-security guidance