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

Description

Enable Transparent Data Encryption on every SQL server.

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.

Remediation

From Azure Console

Go to SQL databases.
For each DB instance.
Click on Transparent data encryption.
Set Data encryption to On.

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

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.

Default Value

By default, Data encryption is set to On.

Usage

Run the control in your terminal:

powerpipe control run azure_compliance.control.cis_v210_4_1_5

Snapshot and share results via Turbot Pipes:

powerpipe login
powerpipe control run azure_compliance.control.cis_v210_4_1_5 --share

SQL

This control uses a named query:

select
  s.database_id resource,
  case
    when transparent_data_encryption ->> 'status' = 'Enabled' or transparent_data_encryption ->> 'state' = 'Enabled' then 'ok'
      else 'alarm'
  end as status,
  case
    when transparent_data_encryption ->> 'status' = 'Enabled' or transparent_data_encryption ->> 'state' = 'Enabled' then s.title || ' transparent data encryption enabled.'
      else s.title || ' transparent data encryption disabled.'
  end as reason
  
  , s.resource_group as resource_group
  , sub.display_name as subscription
from
  azure_sql_database as s,
  azure_subscription as sub
where
  sub.subscription_id = s.subscription_id
  and s.name <> 'master';