|   Trend Micro Cloud One™
Open menu

Enable Transparent Data Encryption for SQL Databases

Cloud Conformity allows you to automate the auditing process of this resolution page. Register for a 14 day evaluation and check your compliance level for free!

Start a Free Trial Product features
Last updated: 26 March 2020
Risk level: Medium (should be achieved)

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.

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

Unlock the Remediation Steps

Gain free unlimited access to our full Knowledge Base


Over 600 rules & best practices for and

Get started for FREE

A verification email will be sent to this address
We keep your information private. Learn more.

Thank you!

Please click the link in the confirmation email sent to

You are auditing:

Enable Transparent Data Encryption for SQL Databases

Risk level: Medium