Skip to main content

lacework-global-540

Set 'Data encryption' to 'On' on a SQL Database (Automated)​

Profile Applicability​

• Level 1

Description​

Enable Transparent Data Encryption (TDE) on every SQL server database.

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 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
  • It is not possible to use TDE to encrypt the logical master database in SQL Database. The master database contains objects needed to perform the TDE operations on the user databases.
  • Azure Portal does not show master databases per SQL server. However, CLI/API responses 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​

  • You can enable and disable Transparent Data Encryption (TDE) on individual SQL Database level and not on the SQL Server level.
  • It is not possible to use TDE to encrypt the logical master database in SQL Database. The master database contains objects needed to perform the TDE operations on the user databases.
Last updated on