Skip to main content
emnode
Sign up
Compliance Low severity MCSB DP-4

Microsoft Defender for Cloud · SQL

Transparent Data Encryption on SQL databases should be enabled

Written and reviewed by Emnode · Last reviewed

What does the recommendation "Transparent Data Encryption on SQL databases should be enabled" check?

Flags any Azure SQL Database, SQL Managed Instance or Synapse dedicated SQL pool where Transparent Data Encryption (TDE) is not set to 'Enabled', so the data files, log files and backups are written to disk unencrypted at rest. It only checks that the TDE state is on. It does not verify that you use a customer-managed key (BYOK) rather than the default service-managed key, nor does it cover encryption in transit, column-level Always Encrypted, or whether the underlying Key Vault is hardened.

Why does "Transparent Data Encryption on SQL databases should be enabled" matter?

TDE protects against one specific threat: someone gaining access to the raw storage, a stolen backup file, a detached .mdf, or a disk image, and reading your data offline without ever authenticating to the database. Without it, a single leaked backup hands an attacker the full dataset in clear text, including any columns you assumed were safe because the application gated access to them. Because most regulated workloads (PCI DSS, HIPAA, GDPR) treat encryption at rest as a baseline obligation, a database flagged here is often also an audit finding, not just a technical gap, and it can block a certification sign-off. Microsoft enables TDE by default on databases created from May 2017 onward, so a failure usually means an older database, a copy restored from an unencrypted source, or a deliberate opt-out that nobody revisited.

How do I fix "Transparent Data Encryption on SQL databases should be enabled"?

  1. Turn TDE on for the database with 'az sql db tde set --resource-group <rg> --server <server> --database <db> --status Enabled', or set the 'Transparent data encryption' toggle to On under the database's Security blade in the portal. Encryption runs as a background scan, so there is no downtime.
  2. Decide on the key. The default service-managed key needs no setup and satisfies the control. If your policy requires you to control rotation and revocation, configure a customer-managed TDE protector against an Azure Key Vault key (with purge protection enabled) and grant the server's managed identity the Get, wrapKey and unwrapKey permissions.
  3. Stop the regression at source: default 'Transparent Data Encryption: Enabled' in the Bicep or Terraform module you provision SQL servers with, then back it with Azure Policy. The built-in audit policy this control maps to, 'Transparent Data Encryption on SQL databases should be enabled', only supports the AuditIfNotExists and Disabled effects, so it flags non-compliant databases but cannot block or remediate them. To auto-remediate, assign the built-in DeployIfNotExists policy 'Deploy SQL DB transparent data encryption' (definition 86a912f6-9a06-4e26-b447-11b16ba8659f) and run a remediation task to turn TDE on for existing databases. There is no built-in Deny policy for TDE because the property is effectively read-only at create time, so if you want hard prevention you must author a custom Deny definition yourself.

Remediation script · bash

# 1. Free, universal fix: enable Transparent Data Encryption on a SQL database.
az sql db tde set \
  --resource-group rg-data \
  --server sql-fintech-prod \
  --database payments \
  --status Enabled

# 2. Free, universal fix: encrypt VM temp disks, caches and data flows.
az feature register --namespace Microsoft.Compute --name EncryptionAtHost
az vm deallocate --resource-group rg-app --name vm-tooling-01
az vm update --resource-group rg-app --name vm-tooling-01 \
  --set securityProfile.encryptionAtHost=true
az vm start --resource-group rg-app --name vm-tooling-01

# 3. Only where required: customer-managed key on a storage account.
az storage account update \
  --name stbankingprod \
  --resource-group rg-data \
  --encryption-key-source Microsoft.Keyvault \
  --encryption-key-vault "https://kv-banking-cmk.vault.azure.net" \
  --encryption-key-name sa-cmk

echo "TDE on, host encryption on, CMK applied to the one regulated store."

Full walkthrough (console steps, edge cases and verification) in the lesson Encrypt Azure data at rest, including customer-managed keys.

Is "Transparent Data Encryption on SQL databases should be enabled" a false positive?

A genuine exception is rare. The most defensible one is a transient, non-production database, such as a short-lived restore used to extract a single table, that you intend to drop. Even then, enabling TDE costs nothing and removes the finding, so prefer fixing over excepting. There is no legitimate steady-state reason to leave a production SQL database unencrypted at rest.

Full lesson Encrypt Azure data at rest, including customer-managed keys Step-by-step remediation with CLI, code and persona-specific context.
Defender for Cloud recommendations ↗ Check your own account →
Part of the learning path Encrypt across Azure

More SQL controls