SQL Server Always Encrypted
Client-side column encryption — the SQL engine never sees plaintext for protected columns, not even as an administrator.
This is not SQL EKM
DuoKey documents two different SQL Server integrations, and they solve different problems:
- SQL EKM (Extensible Key Management) protects SQL Server's Transparent Data Encryption. The SQL engine's own database master key is wrapped by a key held in DuoKey; the engine handles all table/tablespace encryption transparently. The SQL engine sees plaintext at query time — TDE only protects data files at rest.
- Always Encrypted (this page) is client-side column encryption. DuoKey only custodies the Column Master Key (CMK) that wraps per-column Column Encryption Keys (CEKs). The client driver — not the SQL engine — fetches and unwraps the CEK through DuoKey's key provider, and encrypts/decrypts specific columns locally. Plaintext for those columns never reaches the SQL engine at all — not in memory, not in a query plan, not to a DBA running an ad hoc
SELECT.
If your requirement is "the SQL Server admin must never be able to see a column's plaintext," you need Always Encrypted, not SQL EKM. If your requirement is "data files on disk must be encrypted," SQL EKM's TDE integration is the right tool.
| SQL EKM (TDE) | Always Encrypted (this page) | |
|---|---|---|
| What is encrypted | Data and log files at rest | Specific column values, end to end |
| Who can see plaintext | The SQL engine — and anyone with the right database permissions — sees plaintext at query time | Only the client application holding the CEK; the SQL engine and DBAs never see plaintext for protected columns |
| DuoKey's role | Wraps the database master key via a cryptographic provider DLL, transparently to the engine | Custodies the Column Master Key; the client driver fetches and unwraps CEKs through it |
| Where decryption happens | Inside the SQL Server engine | Inside the client driver, before/after the network round-trip to SQL Server |
Overview
SQL Server Always Encrypted protects specific columns end to end. Each protected column is encrypted with a Column Encryption Key (CEK); every CEK is itself wrapped by a Column Master Key (CMK). The CMK never appears in the database at all — SQL Server only ever stores the CMK's key store provider name and key path as metadata, plus the CEK in its wrapped (encrypted) form.
This app makes DuoKey that CMK store provider: the DuoKey key provider (CNG/CSP, or an Azure-Key-Vault-style endpoint) holds the actual CMK, and the client driver calls it to unwrap a CEK on demand.
SQL Server only ever stores the CMK's key-store-provider name and key path as metadata, plus each CEK in its wrapped form.
| Property | Value |
|---|---|
| Encryption model | Client-side column encryption (CMK wraps per-column CEKs) |
| Key custody | DuoKey vault key acts as the CMK; the SQL engine never holds CEK or column plaintext |
| Onboarding | App detail page and catalog entry — no wizard entry |
| Proof status | CMK path and T-SQL setup generation are real; the self-test currently returns simulated results — see below |
Configuration
| Field | Purpose | ||
|---|---|---|---|
sql_host | sql_port | database | Target SQL Server instance and database (default port 1433). |
cmk_store_provider | Provider name advertised to the SQL driver: `DUOKEY_CNG` (default), `AZURE_KEY_VAULT`, or a certificate-store provider. | ||
cmk_name | Logical name of the Column Master Key object in SQL Server. Defaults to `DuoKey_CMK`. | ||
linked_key_id | The DuoKey vault key backing the CMK, wrapping every CEK. | ||
enclave_enabled | Whether secure-enclave (VBS or Intel SGX) column encryption is requested. A secure enclave is a protected, attested memory region inside the SQL Server engine process where the engine can perform richer operations — comparisons, `BETWEEN`, `IN`, `LIKE`, joins, and (on SQL Server 2022+ / Azure SQL Database) `GROUP BY` / `ORDER BY` — directly on randomized-encrypted columns, and re-encrypt columns in place, without ever exposing plaintext or keys outside the enclave. Requires enclave-enabled CMK/CEK objects and driver-side attestation configuration — a documented follow-up here. |
How a column is encrypted or decrypted
Steps 2–4 happen entirely in the client driver; the SQL engine never holds the CEK or column plaintext.
SQL Server itself never performs the unwrap or the encrypt/decrypt — steps 2–4 above happen entirely on the client. This is why, without a secure enclave, Always Encrypted only supports equality-family operations (=, IN, GROUP BY, DISTINCT) on DETERMINISTIC columns, and no operations at all on RANDOMIZED columns: the engine has no way to compare values it cannot see.
Setup T-SQL
Deployment generates the Column Master Key and a first Column Encryption Key, plus an example of encrypting a column.
CREATE COLUMN MASTER KEY [DuoKey_CMK]
WITH (
KEY_STORE_PROVIDER_NAME = N'DUOKEY_CNG',
KEY_PATH = N'DUOKEY_CNG/<tenant-id>/DuoKey_CMK'
);-- ENCRYPTED_VALUE is produced by the DuoKey key provider wrapping
-- a fresh CEK with the CMK above; the create-CEK action generates it.
CREATE COLUMN ENCRYPTION KEY [CEK_Auto1]
WITH VALUES (
COLUMN_MASTER_KEY = [DuoKey_CMK],
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = 0x<wrapped-cek-hex>
);ALTER TABLE <schema>.<table>
ALTER COLUMN <column> <type>
ENCRYPTED WITH (
COLUMN_ENCRYPTION_KEY = [CEK_Auto1],
ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
);DETERMINISTIC allows equality lookups and joins on the encrypted column, at the cost of leaking which rows share a value. RANDOMIZED gives stronger protection but cannot be used in WHERE, JOIN, GROUP BY, or indexes on that column.
Creating and rotating Column Encryption Keys
Beyond the CEK created at deploy time, the app exposes a dedicated action to add or rotate a CEK under the same CMK — useful for encrypting additional columns or for CEK rotation without touching the CMK.
| Field | Purpose |
|---|---|
cek_name | Logical CEK name in SQL Server, e.g. `CEK_Auto2`. |
The response returns the fully-qualified CMK key path the driver resolves (<provider>/<tenant>/<cmk_name> — a non-secret, opaque coordinate) along with the CREATE COLUMN ENCRYPTION KEY statement to run.
Health and self-test
| Check | What it reports | Live probe today |
|---|---|---|
| Health | SQL reachability, CMK accessibility, CEK count | No — returns a fixed healthy status envelope |
| Self-test | SQL connection, CMK accessible, CEK unwrap round-trip, deterministic column encrypt/decrypt | No — all four checks return a hardcoded pass |
Until the self-test drives a live SQL Server session, confirm column encryption is genuinely active by querying the protected column through a connection without the Column Encryption Setting=Enabled driver flag — you should see ciphertext (varbinary), not plaintext.