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

How it is checked

Audit method: Manual review

This recommendation cannot be checked automatically. A scan lists it with these instructions when you pass --include-manual.

Audit From Azure Portal 1. Go to SQL databases 2. For each DB instance 3. Click on Transparent data encryption 4. Ensure that Data encryption is set to On Audit From Azure CLI Ensure the output of the below command is Enabled az sql db tde show --resource-group <resourceGroup> --server <dbServerName> - -database <dbName> --query status Audit From PowerShell Get a list of SQL Servers. Get-AzSqlServer For each server, list the databases. Get-AzSqlDatabase -ServerName <SQL Server Name> -ResourceGroupName <Resource Group Name> For each database not listed as a Master database, check for Transparent Data Encryption. Get-AzSqlDatabaseTransparentDataEncryption -ResourceGroupName <Resource Group Name> -ServerName <SQL Server Name> -DatabaseName <Database Name> Make sure DataEncryption is Enabled for each database except the Master database. Audit From Azure Policy If referencing a digital copy of this Benchmark, clicking a Policy ID will open a link to the associated Policy definition in Azure. If referencing a printed copy, you can search Policy IDs from this URL: https://portal.azure.com/#view/Microsoft_Azure_Policy/PolicyMenuBlade/~/Definitions - Policy ID: 17k78e20-9358-41c9-923c-fb...

How it is fixed

Fix method: Guidance or infrastructure patch

Remediate From Azure Portal 1. Go to SQL databases 2. For each DB instance 3. Click on Transparent data encryption 4. Set Data encryption to On 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 SQL DB instance. Set-AzSqlDatabaseTransparentDataEncryption -ResourceGroupName <Resource Group Name> -ServerName <SQL Server Name> -DatabaseName <Database Name> -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.