SQL managed instances should use customer-managed keys to encrypt data at rest

What does the recommendation "SQL managed instances should use customer-managed keys to encrypt data at rest" check?

Flags any Azure SQL Managed Instance whose Transparent Data Encryption protector is the Microsoft-managed service key rather than a customer-managed key (CMK) held in your own Azure Key Vault or Managed HSM. It is an audit of who controls the root key, not of whether encryption is on: TDE is always enabled by default, so an instance using the service-managed protector is still encrypted at rest, it just does not flag here. The control does not check key rotation cadence, the strength or expiry of the key, whether soft-delete and purge protection are set on the vault, or whether the underlying databases have database-level CMK applied.

Why does "SQL managed instances should use customer-managed keys to encrypt data at rest" matter?

With the default service-managed protector, Microsoft holds the key that unwraps your data encryption key, so you cannot independently revoke access, prove key custody to an auditor, or cryptographically erase data by destroying the key. Customer-managed TDE moves the protector into a vault you own, giving you separation of duties and a hard kill switch: disable or delete the key and every database on the instance becomes unreadable within minutes, regardless of database permissions. For organisations under regulatory regimes that mandate customer-held keys, running on the service-managed key can mean a failed audit, blocked data residency sign-off, or an inability to honour a contractual right-to-erasure, none of which can be fixed retroactively after an incident.

How do I fix "SQL managed instances should use customer-managed keys to encrypt data at rest"?

1. Assign a managed identity to the managed instance and grant it key access on a soft-delete and purge-protection enabled vault. On a standard vault grant the key permissions get, wrapKey and unwrapKey via 'az keyvault set-policy --name <kvname> --object-id <miPrincipalId> --key-permissions get wrapKey unwrapKey'; on a Managed HSM assign the 'Managed HSM Crypto Service Encryption User' role to the identity instead.
2. Register the key with the instance and switch the protector to it: 'az sql mi key create --managed-instance <miname> --resource-group <rg> --kid <keyID>' then 'az sql mi tde-key set --managed-instance <miname> --resource-group <rg> --server-key-type AzureKeyVault --kid <keyID> --auto-rotation-enabled true', where <keyID> is the full Key Vault key identifier. Enabling auto rotation keeps the protector on the latest key version without a manual step.
3. Enforce the baseline going forward by assigning the built-in policy by display name rather than a hardcoded GUID: 'pid=$(az policy definition list --query "[?displayName=='SQL managed instances should use customer-managed keys to encrypt data at rest'].name" -o tsv)' then 'az policy assignment create --name mi-cmk-tde --policy "$pid" --scope <scope>'. The definition defaults to Audit; set it to Deny if you want to block new instances that ship without CMK.

# Move ONE regulated Azure SQL logical server onto a customer-managed key.
# Run against a test server first and confirm databases stay online.
RG=prod-rg
SERVER=custrecords-sql
VAULT=custrecords-kv
KEY=tde-protector

# 1. Vault needs soft-delete + purge protection before Azure accepts a TDE key.
az keyvault update --name "$VAULT" --resource-group "$RG" \
  --enable-purge-protection true

# 2. Create the RSA key (2048 or 3072 bit).
az keyvault key create --name "$KEY" --vault-name "$VAULT" --size 2048

# 3. Give the server a managed identity and read its principal id.
az sql server update --name "$SERVER" --resource-group "$RG" --assign-identity
PRINCIPAL=$(az sql server show --name "$SERVER" --resource-group "$RG" \
  --query identity.principalId -o tsv)

# 4. Grant the identity the exact key permissions TDE needs.
az keyvault set-policy --name "$VAULT" --object-id "$PRINCIPAL" \
  --key-permissions get wrapKey unwrapKey

# 5. Add the key to the server and set it as the TDE protector.
KID=$(az keyvault key show --name "$KEY" --vault-name "$VAULT" \
  --query key.kid -o tsv)
az sql server key create --server "$SERVER" --resource-group "$RG" --kid "$KID"
az sql server tde-key set --server "$SERVER" --resource-group "$RG" \
  --server-key-type AzureKeyVault --kid "$KID"

# 6. Backstop the regulated scope: assign the built-in policy by display name
#    (look up the definition id at runtime, never hardcode the GUID).
PID=$(az policy definition list \
  --query "[?displayName=='SQL servers should use customer-managed keys to encrypt data at rest'].name" \
  -o tsv)
az policy assignment create --name require-cmk-sql --policy "$PID" \
  --scope "/subscriptions/<regulated-subscription-id>"