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

Enable Transparent Data Encryption for SQL Managed Instance using Customer-Managed Keys

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

Ensure that Transparent Data Encryption (TDE) with Customer-Managed Keys (CMKs) is enabled for your Microsoft Azure SQL managed instances. The TDE protector configured for your Azure SQL managed instances must be encrypted with a Customer-Managed Key in order to protect your managed SQL databases with a key from your own Azure key vault. This enables you to have full control over the encryption and decryption process and meet strict compliance requirements.

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

Security
Cost
optimisation
Operational
excellence

The Customer-Managed Key (CMK) support for Transparent Data Encryption (TDE) allows user access control over TDE encryption keys, i.e. decide who can access the TDE keys and when. With CMK-based encryption, the SQL database encryption key is protected by an asymmetric key stored in your Microsoft Azure key vault. The asymmetric key is configured at the SQL server level and inherited by all databases created on the managed server.

Audit

To determine the type of the encryption key used by the Transparent Data Encryption (TDE) protector, perform the following operations:

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 SQL managed instance and choose Apply to list only the SQL managed instances available in your Azure account.

05 Click on the name (link) of the Azure SQL managed instance that you want to examine.

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

07 Check the Transparent data encryption configuration setting value to determine the type of the encryption key used for the TDE protector. The protector key is either a service-managed key or a Customer-Managed Key (CMK). If Transparent data encryption is set to Service-managed key, the Transparent Data Encryption (TDE) feature is using a service-managed key instead of a Customer-Managed Key (CMK) for the selected Microsoft Azure SQL managed instance.

08 Repeat steps no. 5 – 7 for each managed SQL instance deployed in the selected Azure subscription.

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

Using Azure PowerShell

01 Run sql mi list command (Windows/macOS/Linux) with custom query filters to list the identifier (ID) of each Azure SQL managed instance available in the current Azure subscription: 

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

02 The command output should return the requested SQL instance IDs: 

[
	"/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/managedInstances/cc-prod-managed-instance",
	"/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/managedInstances/cc-project5-sql-managed"
]

03 Run sql mi tde-key show command (Windows/macOS/Linux) using the name of the managed SQL instance that you want to examine as the identifier parameter and custom query filters to describe the type of the encryption key used by Transparent Data Encryption (TDE) feature for the selected SQL instance: 

az sql mi tde-key show
  --ids "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/managedInstances/cc-prod-managed-instance"
  --query 'serverKeyType'

04 The command output should return the type of the encryption key used by TDE for the selected SQL instance: 

"ServiceManaged"

If the sql mi tde-key show command output returns "ServiceManaged", as shown in the output example above, the Transparent Data Encryption (TDE) feature is using a service-managed key instead of a Customer-Managed Key (CMK) for the selected Microsoft Azure SQL managed instance.

05 Repeat steps no. 3 and 4 for each managed SQL instance provisioned in the selected Azure subscription.

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

Remediation / Resolution

To enable Transparent Data Encryption (TDE) for your Microsoft Azure SQL managed instances using Customer-Managed Keys (CMKs), perform the following operations:

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 SQL managed instance and choose Apply to list only the SQL managed instances available in your Azure account.

05 Click on the name (link) of the Azure SQL managed instance that you want to examine.

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

07 On the Transparent data encryption configuration page, perform the following actions:

  1. For Transparent data encryption select Customer-managed key.
  2. For Key selection method choose Select a key.
  3. ForKeychoose Change key under Select a key.
  4. For Select a key, choose the key vault, the encryption key, and the key version that you want to use for Transparent Data Encryption (TDE). If you don't have these resources already available, follow the setup wizard provided by Azure Portal to create them.
  5. Select the Make this key the default TDE protector checkbox to set the selected Customer-Managed Key (CMK) as the default key for the Transparent Data Encryption (TDE) protector.
  6. Select the Auto-rotate key checkbox to enable automatic rotation of the TDE protector to the latest key version in your key vault.
  7. Choose Save to apply the configuration changes.

08 Repeat steps no. 5 – 7 for each managed SQL instance available within the selected Azure 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 mi tde-key set command (Windows/macOS/Linux) using the ID of the Azure SQL managed instance that you want to configure as the identifier parameter, to configure Transparent Data Encryption (TDE) to use a Customer-Managed Key (CMK) for the selected SQL instance. For example, the following command request applies an existing Customer-Managed Key (CMK), identified by the URI "https://cc-customer-vault.vault.azure.net/keys/cc-managed-key/01234123412341234123412341234123" to a managed SQL instance identified by the ID "/subscriptions/abcdabcd-abcd-abcd-abcd-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/servers/cc-project5-sql-server": 

az sql mi tde-key set
  --ids "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/managedInstances/cc-prod-managed-instance"
  --server-key-type AzureKeyVault
  --kid "https://cc-customer-vault.vault.azure.net/keys/cc-managed-key/01234123412341234123412341234123"

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

{
	"id": "/subscriptions/abcdabcd-1234-abcd-1234-abcdabcdabcd/resourceGroups/cloud-shell-storage-westeurope/providers/Microsoft.Sql/managedInstances/cc-prod-managed-instance",
	"kind": "azurekeyvault",
	"name": "current",
	"resourceGroup": "cloud-shell-storage-westeurope",
	"serverKeyName": "cc-customer-vault_cc-managed-key_01234123412341234123412341234123",
	"serverKeyType": "AzureKeyVault",
	"type": "Microsoft.Sql/servers/encryptionProtector",
	"uri": "https://cc-customer-vault.vault.azure.net/keys/cc-managed-key/01234123412341234123412341234123"
}

03 Repeat steps no. 1 and 2 for each managed SQL instance provisioned in the selected Azure subscription.

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

References

Publication date Aug 30, 2023

Related Sql rules