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

Description

It is recommended to enable Transparent Data Encryption on every SQL database. Azure SQL database transparent data encryption helps to 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.

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.

Default setting for Transparent data encryption is set to On.

Remediation

From Console

Login to Azure console and navigate to SQL Databases.
For each DB instance, go to Security section from left pane.
Click on Transparent data encryption.
Set Transparent data encryption to On.
Click Save.

From Command Line

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

Usage

Run the control in your terminal:

powerpipe control run azure_compliance.control.cis_v140_4_1_2

Snapshot and share results via Turbot Pipes:

powerpipe login
powerpipe control run azure_compliance.control.cis_v140_4_1_2 --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';