cis-azure-database-9-6

name	cis-azure-database-9.6
description	Ensure that 'Data encryption' is set to 'On' on a SQL Database
category	cis-azure-database
version	2.0.0
author	cyberstrike-official
tags	["cis","azure","sql-database","sql-server"]
cis_id	9.6
cis_benchmark	CIS Microsoft Azure Database Services Benchmark v2.0.0
tech_stack	["azure"]
cwe_ids	[]
chains_with	[]
prerequisites	[]
severity_boost	{}

9.6 Ensure that 'Data encryption' is set to 'On' on a SQL Database (Automated)

Profile Applicability

• Level 1

Description

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.

Impact

None documented.

Audit Procedure

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

Policy ID: 17k78e20-9358-41c9-923c-fb736d382a12 Name: 'Transparent Data Encryption on SQL databases should be enabled'

Expected Result

Data encryption should be set to On for all SQL databases (except master).

Remediation

Remediate from Azure Portal

1. Go to SQL databases.
2. For each DB instance.
3. Click on Transparent data encryption.
4. Set to .