Always Encrypted
Column master keys and column encryption keys per environment, provisioned and rotated by bsm.
Always Encrypted keys are a classic per-environment difference: production's column master key lives in production's Key Vault, test's somewhere else. Change scripts should not contain key paths or encrypted key values. Badger SQL Migrate splits it cleanly:
- Scripts reference a column encryption key name (the same in every environment).
- Each environment maps that name to its own column master key in
bsm.yaml. - Before changes run, bsm creates missing keys and rotates keys whose master key changed.
ALTER TABLE dbo.Customer ADD Ssn char(11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK_Customer, ENCRYPTION_TYPE = Deterministic,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL;
bsm.yamlenvironments:
test:
alwaysEncrypted:
columnMasterKeys:
CMK_Test:
keyStore: AZURE_KEY_VAULT
keyPath: https://myapp-test-kv.vault.azure.net/keys/cmk/1a2b3c4d
columnEncryptionKeys:
CEK_Customer: { columnMasterKey: CMK_Test }
prod:
alwaysEncrypted:
attestation: { protocol: AAS, url: "https://myapp.weu.attest.azure.net/attest/SgxEnclave" }
keyStores:
azureKeyVault:
authentication: entraManagedIdentity # optional: a different identity for Key Vault
entra: { clientId: 9d8c7b6a-0000-0000-0000-000000000000 }
trustedEndpoints: [vault.azure.net, managedhsm.azure.net]
columnMasterKeys:
CMK_Prod_2026:
keyStore: AZURE_KEY_VAULT
keyPath: https://myapp-prod-kv.vault.azure.net/keys/cmk/9f8e7d6c
enclaveComputations: true
columnEncryptionKeys:
CEK_Customer: { columnMasterKey: CMK_Prod_2026 }
Settings
| Key | Meaning |
|---|---|
enabled | Default true: the connection uses Column Encryption Setting=Enabled |
attestation | protocol (AAS, HGS, None) and url for secure enclaves |
keyStores.azureKeyVault | Optional authentication and entra for Key Vault (default: the environment's Entra identity, otherwise entraDefault), trustedEndpoints for sovereign clouds and Managed HSM |
keyStores.custom | List of name, assembly (path relative to the project) and type: your own SqlColumnEncryptionKeyStoreProvider, e.g. an HSM |
columnMasterKeys.<name> | keyStore, keyPath, enclaveComputations |
columnEncryptionKeys.<name> | columnMasterKey: which CMK protects it in this environment |
Key stores: AZURE_KEY_VAULT (Key Vault and Managed HSM), MSSQL_CERTIFICATE_STORE, MSSQL_CNG_STORE, MSSQL_CSP_PROVIDER (these three on Windows only), or a custom name. Short aliases: azureKeyVault, certificateStore, cng, csp.
What deploy does
- Compares the configuration with
sys.column_master_keysandsys.column_encryption_keys. - Creates missing column master keys. Enclave-enabled keys are signed with the key itself.
- Creates missing column encryption keys: a new random 256-bit key, encrypted on your machine with the column master key (RSA-OAEP). The plaintext key never leaves the process and is never logged.
- Runs your changes.
Your identity needs wrap/unwrap/sign rights on the key (Key Vault crypto user) and ALTER ANY COLUMN MASTER KEY / ALTER ANY COLUMN ENCRYPTION KEY in the database.
Rotating a column master key
- Create the new key in Key Vault and add it under
columnMasterKeyswith a new name. - Point the column encryption key at it:
CEK_Customer: { columnMasterKey: CMK_Prod_2027 }. bsm deploy(orbsm env provision) adds a second value to the CEK: the same data key, decrypted with the old CMK and re-encrypted with the new one. Data does not change; both keys work.- When every application can reach the new key, run
bsm env provision --allow-destructiveto drop the value protected by the old CMK. This asks for confirmation.
bsm env status -e prod
= column master key CMK_Prod_2026: AZURE_KEY_VAULT key path matches
+ column master key CMK_Prod_2027: create column master key in AZURE_KEY_VAULT
+ column encryption key CEK_Customer: add value protected by CMK_Prod_2027 (rotation from CMK_Prod_2026)
- column encryption key CEK_Customer: drop value protected by old column master key CMK_Prod_2026 (rotation complete)
Conflicts
SQL Server cannot change a column master key's key path. If the database has CMK_Prod_2026 pointing somewhere other than your configuration, bsm stops with a conflict instead of guessing. Define a new CMK name and rotate. Keys that exist in the database but not in your configuration are reported for information and never dropped.
Fingerprints across environments
The schema fingerprint records which CEK name protects each encrypted column, never key paths or values, so environments with different keys still produce the same fingerprint for the same schema.