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

Enable Transparent Data Encryption for Azure Synapse Analytics Dedicated SQL Pools

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: High (not acceptable risk)
Rule ID: Synapse-001

Ensure that Transparent Data Encryption (TDE) is enabled for all dedicated SQL pools within Microsoft Azure Synapse Analytics workspaces in order to protect your data at rest and help meet compliance requirements.

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

Security
Cost
optimisation
Operational
excellence

Transparent Data Encryption (TDE) helps protect any dedicated SQL pools provisioned inside Azure Synapse Analytics workspaces against the threat of malicious activity by encrypting data at rest. It performs real-time encryption and decryption of the associated SQL databases, backups, and logs, without requiring changes to your application. TDE encrypts your SQL database resources by using a symmetric key protected by a TDE protector.

Audit

To determine if Transparent Data Encryption (TDE) is enabled for dedicated SQL pools in Azure Synapse Analytics, perform the following actions:

Using Azure Console

01 Sign in to the Azure Management Console.

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

03 From the Subscription equals filter box, select the Azure account subscription that you want to examine.

04 From the Type equals filter box, select Synapse workspace and choose Apply to list only the Synapse Analytics workspaces available in your Azure account.

05 Click on the name (link) of the Synapse Analytics workspace that you want to examine.

06 In the resource navigation panel, under Analytics pools, choose SQL pools to access the SQL pools provisioned for the selected workspace.

07 Click on the name of the dedicated SQL pool that you want to examine. A dedicated SQL pool has the Type value set to Dedicated.

08 In the resource navigation panel, under Security, choose Transparent data encryption to access the encryption settings available for the selected SQL pool.

09 On the Transparent data encryption configuration page, check the Encryption status value to determine the encryption status available for the selected SQL resource. If Encryption status is set to Unencrypted, the Transparent Data Encryption (TDE) feature is not enabled for the selected Azure Synapse Analytics dedicated SQL pool.

10 Repeat steps no. 7 – 9 for each dedicated SQL pool provisioned within the selected Azure Synapse Analytics workspace.

11 Repeat steps no. 5 – 10 for each Synapse Analytics workspace available in the selected Azure subscription.

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

Using Azure PowerShell

01 Run synapse workspace list command (Windows/macOS/Linux) with custom query filters to list the identifier of each Synapse Analytics workspace available in the current Azure subscription: 

az synapse workspace list 
  --query '[*].id'

02 The command output should return the requested Synapse Analytics workspace identifiers. Each Synapse Analytics workspace identifier contains the ID of the subcription, the name of the associated resource group, and the name of the workspace: 

[
	"/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Synapse/workspaces/cc-prod-analytics-workspace",
	"/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Synapse/workspaces/cc-project5-synapse-workspace"
]

03 Run synapse sql pool list command (Windows/macOS/Linux) using the name of the Synapse Analytics workspace that you want to examine as the identifier parameter and custom query filters to list the ID of each dedicated SQL pool provisioned for the selected Synapse Analytics workspace: 

az synapse sql pool list 
  --workspace-name cc-prod-analytics-workspace 
  --resource-group cloud-shell-storage-westeurope 
  --query '[*].id'

04 The command output should return the requested SQL pool IDs: 

[
	"/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Synapse/workspaces/cc-prod-analytics-workspace/sqlPools/prodSQLpool001",
	"/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Synapse/workspaces/cc-prod-analytics-workspace/sqlPools/prodSQLpool002"
]

05 Run synapse sql pool tde show command (Windows/macOS/Linux) using the name of the dedicated SQL pool that you want to examine as the identifier parameter and custom query filters to describe the Transparent Data Encryption (TDE) feature status for the selected SQL resource: 

az synapse sql pool tde show 
  --ids "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Synapse/workspaces/cc-prod-analytics-workspace/sqlPools/prodSQLpool001" 
  --transparent-data-encryption-name current 
  --query 'status'

06 The command output should return the TDE status for the selected SQL pool: 

"Disabled"

If the synapse sql pool tde show command output returns "Disabled", as shown in the output example above, Transparent Data Encryption (TDE) is not enabled for the selected Azure Synapse Analytics dedicated SQL pool.

07 Repeat steps no. 5 and 6 for each dedicated SQL pool provisioned within the selected Azure Synapse Analytics workspace

08 Repeat steps no. 3 – 7 for each Synapse Analytics workspace available in the selected Azure subscription.

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

Remediation / Resolution

To enable Transparent Data Encryption (TDE) for the dedicated SQL pools within your Microsoft Azure Synapse Analytics workspaces, perform the following actions:

Using Azure Console

01 Sign in to the Azure Management Console.

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

03 From the Subscription equals filter box, select the Azure account subscription that you want to examine.

04 From the Type equals filter box, select Synapse workspace and choose Apply to list only the Synapse Analytics workspaces available in your Azure account.

05 Click on the name (link) of the Synapse Analytics workspace that you want to access.

06 In the resource navigation panel, under Analytics pools, choose SQL pools to view the SQL pools provisioned for the selected workspace.

07 Click on the name of the dedicated SQL pool that you want to configure. A dedicated SQL pool has the Type value set to Dedicated.

08 In the resource navigation panel, under Security, choose Transparent data encryption to access the encryption settings available for the selected SQL pool.

09 On the Transparent data encryption configuration page, select ON under Data encryption to enable Transparent Data Encryption (TDE) for the selected SQL pool, and choose Save to apply the changes.

10 Repeat steps no. 7 – 9 for each dedicated SQL pool provisioned within the selected Azure Synapse Analytics workspace.

11 Repeat steps no. 5 – 10 for each Synapse Analytics workspace available in the selected Azure subscription.

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

Using Azure CLI and PowerShell

01 Run synapse sql pool tde set command (Windows/macOS/Linux) using the name of the dedicated SQL pool that you want to configure as the identifier parameter, to enable the Transparent Data Encryption (TDE) feature for the selected SQL pool: 

az synapse sql pool tde set 
  --ids "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Synapse/workspaces/cc-prod-analytics-workspace/sqlPools/prodSQLpool001" 
  --transparent-data-encryption-name current 
  --status Enabled

02 The command output should return the TDE's configuration information for the modified SQL pool: 

{
	"id": "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Synapse/workspaces/cc-prod-analytics-workspace/sqlPools/prodSQLpool001/transparentDataEncryption/current",
	"location": "West Europe",
	"name": "current",
	"resourceGroup": "cloud-shell-storage-westeurope",
	"status": "Enabled",
	"type": "Microsoft.Synapse/workspaces/sqlPools/transparentDataEncryption"
}

03 Repeat steps no. 1 and 2 for each dedicated SQL pool provisioned within the selected Azure Synapse Analytics workspace.

04 Repeat steps no. 1 – 3 for each Synapse Analytics workspace available in the selected Azure subscription.

05 Repeat steps no. 1 – 4 for each subscription created in your Microsoft Azure cloud account.

References

Publication date Aug 30, 2023

Related Synapse rules