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

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

Flags any logical SQL server (Microsoft.Sql/servers) whose Transparent Data Encryption protector is still a service-managed certificate rather than a customer-managed key (CMK) held in Azure Key Vault. It reads the server's encryption protector and passes only when serverKeyType is AzureKeyVault. The check is about who owns the key, not whether encryption is on: TDE is enabled by default on Azure SQL, so a flagged server is still encrypted, just with a Microsoft-managed key. It scopes to logical servers only. SQL Managed Instance has its own separate recommendation, and database-level CMK is not assessed here.

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

With a service-managed key, Microsoft holds and rotates the TDE protector, so you cannot independently revoke access, prove key custody to an auditor, or cryptographically shred the data on demand. For regulated workloads under PCI DSS, HIPAA or contractual data-residency clauses, that lack of tenant control is often the finding that fails the audit. Moving the protector into your Key Vault means a single key disable or access revocation renders every database under the server unreadable, which is the lever you want during an incident or a confirmed breach, and it cleanly separates the duty of the database admin from the key custodian.

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

1. Give the server an identity and the vault the right permissions: create or update the server with 'az sql server update --name <server> --resource-group <rg> --assign-identity', then on a Key Vault that has soft-delete and purge protection enabled grant that identity 'get', 'wrapKey' and 'unwrapKey' on keys with 'az keyvault set-policy --name <vault> --object-id <serverPrincipalId> --key-permissions get wrapKey unwrapKey'.
2. Register the key and promote it to the TDE protector: 'az sql server key create --server <server> --resource-group <rg> --kid <keyId>' followed by 'az sql server tde-key set --server <server> --resource-group <rg> --server-key-type AzureKeyVault --kid <keyId>'. Use a versionless key identifier so rotation in the vault flows through without a config change.
3. Enforce it going forward by assigning the built-in audit policy, looked up by display name rather than a hardcoded 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 sql-cmk-tde --policy "$pid" --scope <scope>.

# 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>"