Ensure SQL server's TDE protector is encrypted with Customer Managed Keys (CMK)

Overview

Transparent Data Encryption (TDE) with Customer-managed key support provides increased transparency and control over the TDE Protector, increased security with an HSM-backed external service, and promotion of separation of duties.

With TDE, data is encrypted at rest with a symmetric key (called the database encryption key) stored in the database or data warehouse distribution. Only a certificate that the Azure SQL Service managed could be used to protect this data encryption key (DEK) in the past. With Customer-managed key support for TDE, the DEK can be protected with an asymmetric key stored in the Azure Key Vault. The Azure Key Vault is a highly available and scalable cloud-based key store that offers central key management, leverages FIPS 140-2 Level 2 validated hardware security modules (HSMs), and allows separation of management of keys and data for additional security.

Based on business needs or the criticality of data/databases hosted on an SQL server, it is recommended that the TDE protector be encrypted by a key managed by the data owner (Customer-managed key).

Rationale

Customer-managed key support for Transparent DataEncryption (TDE) allows user control of TDE encryption keys and restricts who can access them and when. Azure Key Vault, Azure's cloud-based external key management system is the first key management service where TDE has integrated support for Customer-managed keys. With Customer-managed key support, the database encryption key is protected by an asymmetric key stored in the Key Vault. The asymmetric key is set at the server level and inherited by all the databases under that server.

Impact

Once TDE protector is encrypted with a Customer-managed key, it transfers the entire responsibility of key management on to the user, hence the user should be careful about doing any operations on the particular key in order to keep data from corresponding SQL server and Databases hosted accessible.

When deploying Customer-Managed Keys, it is also prudent to ensure that an automated toolset for managing these keys is deployed (this should include discovery and key rotation). Keys should be stored in an HSM or hardware-backed keystore such as Azure Keyvault.

Check with your cryptographic key provider for toolsets. They may well offer one as an add-on to their service.

Remediation guidance

From Azure Console

Open the SQL server using the Open in Azure button
Under Security, click Transparent data encryption
Select Customer-managed key
Click Select a key and Change key to browse through existing keys or Enter a key identifier
After filling in all the necessary information, check Make this key the default TDE protector
Select Save

Using Azure CLI

Use the below command to encrypt the SQL server's TDE protector with a Customer-managed key:

az sql server tde-key set --resource-group <resourceName> --server <dbServerName> --server-key-type AzureKeyVault --kid '<keyIdentifier>'

Using Azure PowerShell

Use the below command to encrypt the SQL server's TDE protector with a Customer-managed key vault key:

Set-AzSqlServerTransparentDataEncryptionProtector -Type AzureKeyVault -KeyId '<keyIdentifier>' -ServerName  -ResourceGroupName <resourceGroup>

Select Y when prompted.

Default Value

By default, a Microsoft-managed TDE protector is enabled for an SQL server.

References

Additional Information

This configuration is audited or can be done only on an SQL server. The same configuration will affect SQL Databases hosted on SQL Server. Ensuring TDE is protected by a customer-managed key on SQL Server does not ensure the encryption of SQL Databases. The Transparent Data Encryption: Data Encryption (ON/OFF) setting on each SQL Database decides whether the database is encrypted or not.

Multiple Remediation Paths

Azure

SERVICE-WIDE (RECOMMENDED when many resources are affected): Assign Azure Policy initiatives at management group/subscription scope and trigger remediation tasks.

az policy assignment create --name <assignment-name> --scope /subscriptions/<subscription-id> --policy-set-definition <initiative-id>
az policy remediation create --name <remediation-name> --policy-assignment <assignment-id>

ASSET-LEVEL: Apply the resource-specific remediation steps above to the listed non-compliant resources.

PREVENTIVE: Embed Azure Policy checks into landing zones and IaC workflows to block or auto-remediate drift.

References for Service-Wide Patterns

Azure Policy overview: https://learn.microsoft.com/en-us/azure/governance/policy/overview
Azure Policy remediation: https://learn.microsoft.com/en-us/azure/governance/policy/how-to/remediate-resources
Azure Policy initiative structure: https://learn.microsoft.com/en-us/azure/governance/policy/concepts/initiative-definition-structure

Operational Rollout Workflow

Use this sequence to reduce risk and avoid repeated drift.

1. Contain at Service-Wide Scope First (Recommended)

Azure: assign policy initiatives at management group/subscription scope and run remediation tasks.

az policy assignment create --name <assignment-name> --scope /subscriptions/<subscription-id> --policy-set-definition <initiative-id>
az policy remediation create --name <remediation-name> --policy-assignment <assignment-id>

2. Remediate Existing Affected Assets

Execute the control-specific Console/CLI steps documented above for each flagged resource.
Prioritize internet-exposed and production assets first.

3. Validate and Prevent Recurrence

Re-scan after each remediation batch.
Track exceptions with owner and expiry date.
Add preventive checks in IaC/CI pipelines.

Query logic

These are the stored checks tied to this control.

Azure SQL Servers without TDE protector key encrypted with CMK

Connectors

Microsoft Azure

Covered asset types

SQLServer

Expected check: eq []

{
  sqlServers(
    where: {
      OR: [
        { encryptionProtector: null }
        { encryptionProtector: { serverKeyType: "ServiceManaged" } }
      ]
    }
  ) {
    ...AssetFragment
  }
}