Skip to main content

lacework-global-540

4.1.5 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.

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

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

From Azure 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.

Remediation​

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

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
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.

References​

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption-with-azure-sql-database
https://docs.microsoft.com/en-us/security/benchmark/azure/security-controls-v3-data-protection#dp-4-enable-data-at-rest-encryption-by-default

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.