Lone Badger Tech

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.
changes/add-customer-ssn/deploy.sql
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.yaml
environments:
  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

KeyMeaning
enabledDefault true: the connection uses Column Encryption Setting=Enabled
attestationprotocol (AAS, HGS, None) and url for secure enclaves
keyStores.azureKeyVaultOptional authentication and entra for Key Vault (default: the environment's Entra identity, otherwise entraDefault), trustedEndpoints for sovereign clouds and Managed HSM
keyStores.customList 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

  1. Compares the configuration with sys.column_master_keys and sys.column_encryption_keys.
  2. Creates missing column master keys. Enclave-enabled keys are signed with the key itself.
  3. 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.
  4. 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

  1. Create the new key in Key Vault and add it under columnMasterKeys with a new name.
  2. Point the column encryption key at it: CEK_Customer: { columnMasterKey: CMK_Prod_2027 }.
  3. bsm deploy (or bsm 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.
  4. When every application can reach the new key, run bsm env provision --allow-destructive to 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.