Logo of Trend Micro
Use the Conformity Knowledge Base AI to help improve your Cloud Posture

Enable Transparent Data Encryption for SQL Databases

Trend Cloud One™ – Conformity is a continuous assurance tool that provides peace of mind for your cloud infrastructure, delivering over 1000 automated best practice checks.

Risk Level: Medium (should be achieved)
Rule ID: Sql-005

Ensure that Transparent Data Encryption (also known as encryption at rest) is enabled for all SQL databases available within your Microsoft Azure cloud account for protecting your data at rest.

This rule resolution is part of the Conformity Security & Compliance tool for Azure.

Security

Transparent data encryption (TDE) helps protect Azure SQL databases against the threat of malicious activity by encrypting data at rest. It performs real-time encryption and decryption of the database, its associated backups and transaction log files stored at rest, without requiring changes to your database application. The feature encrypts the storage of an entire SQL database by using a symmetric key named the database encryption key. This database encryption key is protected by the TDE protector. The protector is either a service-managed certificate or a customer-managed key (i.e. Bring Your Own Key - BYOK) stored within Azure Key Vault service.

Audit

To determine if encryption at rest is enabled for all your Azure SQL databases, perform the following actions:

Using Azure Console

01 Sign in to Azure Management Console.

02 Navigate to All resources blade at https://portal.azure.com/#blade/HubsExtension/BrowseAll to access all your Microsoft Azure resources.

03 From the Type filter box, select SQL database to list only the SQL databases available in your Azure account.

04 Click on the name of the SQL server that you want to examine.

05 In the navigation panel, under Security, select Transparent data encryption to access the encryption settings for the selected SQL database.

06 On the Transparent data encryption configuration page, check the Encryption status attribute value. If the value is set to Unencrypted, the encryption at rest is not enabled for the selected Microsoft Azure SQL database.

07 Repeat steps no. 4 – 6 for each SQL database available within the current Azure subscription.

08 Repeat steps no. 3 – 7 for each subscription created in your Microsoft Azure cloud account.

Using Azure PowerShell

01 Run sql server list command (Windows/macOS/Linux) using custom query filters to list the identifier for each SQL server provisioned within the current Azure subscription: 

az sql server list
	--query '[*].id'

02 The command output should return the requested SQL server identifiers: 

[
"/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-webapp-sql-server",
"/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-ms-sql-server"
]

03 Run sql db list command (Windows/macOS/Linux) using the name of the Azure SQL server that you want to examine as identifier parameter and custom query filters to list the IDs of the SQL databases created on the selected server: 

az sql db list
	--ids /subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-webapp-sql-server
	--query '[*].id'

04 The command output should return the IDs of the SQL databases provisioned on the selected server. The identifier that ends with "master" should be ignored, as it represents the logical master database available on the verified SQL server. Transparent Data Encryption (TDE) cannot be used to encrypt this logical master database because this database contains objects that are needed to perform the TDE operations on the user databases: 

[
"/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-webapp-sql-server/databases/cc-web-sql-database",
"/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-webapp-sql-server/databases/cc-project5-database",
"/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-webapp-sql-server/databases/master"
]

05 Run sql db tde show command (Windows/macOS/Linux) using the name of the Azure SQL database that you want to examine as identifier parameter to get the Transparent Data Encryption (TDE) status for the selected database: 

az sql db tde show
	--ids /subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-webapp-sql-server/databases/cc-web-sql-database
	--query 'status'

06 The command output should return the TDE configuration status: 

"Disabled"

If the sql db tde show command output returns "Disabled", as shown in the example above, the encryption at rest, also known as Transparent Data Encryption, is not enabled for the selected Microsoft Azure SQL database.

07 Repeat step no. 5 and 6 for each SQL database created on the selected Azure SQL server.

08 Repeat steps no. 3 – 7 for each Azure SQL server provisioned in the selected subscription.

09 Repeat steps no. 1 – 8 for each subscription available within your Microsoft Azure cloud account.

Remediation / Resolution

To enable Transparent Data Encryption (TDE) for your Microsoft Azure SQL databases (including their backups and transaction log files), perform the following actions:

Using Azure Console

01 Sign in to Azure Management Console.

02 Navigate to All resources blade at https://portal.azure.com/#blade/HubsExtension/BrowseAll to access all your Microsoft Azure resources.

03 From the Type filter box, select SQL database to list only the SQL databases available in your Azure account.

04 Click on the name of the SQL database that you want to reconfigure (see Audit section part I to identify the right SQL database).

05 In the navigation panel, under Security, select Transparent data encryption to access the encryption settings for the selected SQL database.

06 On the Transparent data encryption configuration page, select ON for the Data encryption setting to enable encryption at rest for the selected Microsoft Azure SQL database.

07 Click Save to apply the configuration changes.

08 Repeat steps no. 4 – 7 for each SQL database available within the selected subscription.

09 Repeat steps no. 3 – 8 for each subscription created in your Microsoft Azure cloud account.

Using Azure CLI and PowerShell

01 Run sql db tde set command (Windows/macOS/Linux) using the ID of the Azure SQL database that you want to reconfigure as identifier parameter (see Audit section part II to identify the right SQL resource) to enable encryption at rest for the selected SQL database by setting the --status parameter to Enabled: 

az sql db tde set
	--ids /subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-webapp-sql-server/databases/cc-web-sql-database
	--status Enabled

02 The command output should return the metadata for the reconfigured Azure SQL database: 

{
  "id": "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-webapp-sql-server/databases/cc-web-sql-database/transparentDataEncryption/current",
  "location": null,
  "name": "current",
  "resourceGroup": "cloud-shell-storage-westeurope",
  "status": "Enabled",
  "type": "Microsoft.Sql/servers/databases/transparentDataEncryption"
}

03 Repeat step no. 1 and 2 for each SQL database server available in the selected subscription.

04 Repeat steps no. 1 – 3 for each subscription created within your Microsoft Azure cloud account.

References

Publication date Jul 24, 2019

Related Sql rules