Ensure 'Data encryption' is set to 'On' on SQL Databases

Overview

Enable Transparent Data Encryption on every SQL server.

Rationale

Azure SQL Database transparent data encryption helps protect against the threat of malicious activity by performing real-time encryption and decryption of the database, associated backups, and transaction log files at rest without requiring changes to the application.

Default Value

By default, Data encryption is set to On.

Additional Information

Transparent Data Encryption (TDE) can be enabled or disabled on individual SQL Database level and not on the SQL Server level.
TDE cannot be used to encrypt the logical master database in SQL Database. The master database contains objects that are needed to perform the TDE operations on the user databases.

References

Remediation guidance

Remediate from Azure Portal

Go to SQL databases
For each DB instance, under Security, click Data Encryption
Under Transparent data encryption, set Data encryption to On
Click Save

Remediate from Azure CLI

Use the below command to enable Transparent data encryption for SQL DB instance.

az sql db tde set --resource-group <resourceGroup> --server <dbServerName> --database <dbName> --status Enabled

Remediate from PowerShell

Use the below command to enable Transparent data encryption for the SQL DB instance.

Set-AzSqlDatabaseTransparentDataEncryption -ResourceGroupName <rg_name> -ServerName  -DatabaseName  -State 'Enabled'

Note

TDE cannot be used to encrypt the logical master database in SQL Database. The master database contains objects that are needed to perform the TDE operations on the user databases.
Azure Portal does not show master databases per SQL server. However, CLI/API responses will show master databases.

Multiple Remediation Paths

Azure

SERVICE-WIDE (RECOMMENDED when many resources are affected): Assign Azure Policy initiatives at management group/subscription scope and trigger remediation tasks.

az policy assignment create --name <assignment-name> --scope /subscriptions/<subscription-id> --policy-set-definition <initiative-id>
az policy remediation create --name <remediation-name> --policy-assignment <assignment-id>

ASSET-LEVEL: Apply the resource-specific remediation steps above to the listed non-compliant resources.

PREVENTIVE: Embed Azure Policy checks into landing zones and IaC workflows to block or auto-remediate drift.

References for Service-Wide Patterns

Azure Policy overview: https://learn.microsoft.com/en-us/azure/governance/policy/overview
Azure Policy remediation: https://learn.microsoft.com/en-us/azure/governance/policy/how-to/remediate-resources
Azure Policy initiative structure: https://learn.microsoft.com/en-us/azure/governance/policy/concepts/initiative-definition-structure

Operational Rollout Workflow

Use this sequence to reduce risk and avoid repeated drift.

1. Contain at Service-Wide Scope First (Recommended)

Azure: assign policy initiatives at management group/subscription scope and run remediation tasks.

az policy assignment create --name <assignment-name> --scope /subscriptions/<subscription-id> --policy-set-definition <initiative-id>
az policy remediation create --name <remediation-name> --policy-assignment <assignment-id>

2. Remediate Existing Affected Assets

Execute the control-specific Console/CLI steps documented above for each flagged resource.
Prioritize internet-exposed and production assets first.

3. Validate and Prevent Recurrence

Re-scan after each remediation batch.
Track exceptions with owner and expiry date.
Add preventive checks in IaC/CI pipelines.

Query logic

These are the stored checks tied to this control.

'Data encryption' is set to 'On' on a SQL Database

Connectors

Microsoft Azure

Covered asset types

SQLDatabase

Expected check: eq []

{sqlDatabases(where: {encrypted: false}){...AssetFragment}}