Back to Checks
azure

SQL server uses a customer-managed key for the TDE protector and all databases have TDE enabled

sqlserver_tde_encrypted_with_cmk
Service: sqlserverSeverity: critical
OfficialBuilt-inVerified
Categories:encryption
Resource Type:microsoft.sql/servers

Azure SQL Server uses Transparent Data Encryption with a customer-managed key in Azure Key Vault, and each database has TDE Enabled

Prowler logoProwlerprowler-provider-azure

Risk

Without TDE with CMK, data at rest may be unencrypted or controlled by service keys, weakening confidentiality and key custody. Attackers or insiders could read backups, snapshots, or stolen disks, and you cannot enforce rotation, revocation, or separation of duties, raising compliance and incident response risks.

Run this check with Prowler

Run in Prowler Cloud
Run
prowler azure --checks sqlserver_tde_encrypted_with_cmk

Recommendation

Use a customer-managed TDE protector in Azure Key Vault or Managed HSM and ensure TDE is Enabled for every database.

  • Apply least privilege to key access
  • Enable rotation and monitor key use
  • Protect keys with soft-delete and purge protection
  • Enforce via policy and maintain key backups for restores

Remediation

CLI

az rest --method PUT --url "https://management.azure.com/subscriptions/<SUBSCRIPTION_ID>/resourceGroups/<RESOURCE_GROUP>/providers/Microsoft.Sql/servers/<SERVER_NAME>/encryptionProtector/current?api-version=2021-11-01" --body '{"properties":{"serverKeyType":"AzureKeyVault","serverKeyName":"<KEY_VAULT_NAME>_<KEY_NAME>_<KEY_VERSION>"}}' && az sql db tde set --resource-group <RESOURCE_GROUP> --server <SERVER_NAME> --database <DATABASE_NAME> --status Enabled

Terraform

# Set the SQL Server TDE protector to a Key Vault CMK
resource "azurerm_mssql_server_transparent_data_encryption" "<example_resource_name>" {
  server_id        = "<example_resource_id>"
  key_vault_key_id = "<key_vault_key_id>" # critical: KID of the Key Vault key to use as TDE protector
}

# Ensure TDE is enabled on the database
resource "azurerm_mssql_database" "<example_resource_name>" {
  name      = "<example_resource_name>"
  server_id = "<example_resource_id>"

  transparent_data_encryption_enabled = true # critical: turns on TDE for the database
}

NativeIaC

// Add the Key Vault key to the SQL server
resource serverKey 'Microsoft.Sql/servers/keys@2021-11-01' = {
  name: '<example_resource_name>/<example_resource_name>'
  properties: {
    serverKeyType: 'AzureKeyVault' // critical: use a customer-managed key from Azure Key Vault
    uri: 'https://<example_resource_name>.vault.azure.net/keys/<example_resource_name>/<example_resource_id>' // critical: KID of the Key Vault key
  }
}

// Set the server TDE protector to the Key Vault key (CMK)
resource encryptionProtector 'Microsoft.Sql/servers/encryptionProtector@2021-11-01' = {
  name: '<example_resource_name>/current'
  properties: {
    serverKeyType: 'AzureKeyVault' // critical: switches protector from service-managed to CMK
    serverKeyName: '<example_resource_name>' // critical: reference the key added above
  }
}

// Ensure TDE is enabled on the database
resource dbTde 'Microsoft.Sql/servers/databases/transparentDataEncryption@2014-04-01' = {
  name: '<example_resource_name>/<example_resource_name>/current'
  properties: {
    status: 'Enabled' // critical: turns on TDE for the database
  }
}

Other

1In the Azure portal, go to SQL servers > select <server>
2Under Security, open Transparent data encryption
3Select Customer-managed key and choose the key from Azure Key Vault, then Save
4For each database on this server: go to SQL databases > select <database> > Transparent data encryption
5Set Status to On and Save

Check Code

from prowler.lib.check.models import Check, Check_Report_Azure
from prowler.providers.azure.services.sqlserver.sqlserver_client import sqlserver_client


class sqlserver_tde_encrypted_with_cmk(Check):
    def execute(self) -> Check_Report_Azure:
        findings = []
        for subscription, sql_servers in sqlserver_client.sql_servers.items():
            subscription_name = sqlserver_client.subscriptions.get(
                subscription, subscription
            )
            for sql_server in sql_servers:
                databases = [
                    database
                    for database in (
                        sql_server.databases if sql_server.databases is not None else []
                    )
                    if database.name.lower() != "master"
                ]
                if len(databases) > 0:
                    report = Check_Report_Azure(
                        metadata=self.metadata(), resource=sql_server
                    )
                    report.subscription = subscription
                    found_disabled = False
                    if (
                        sql_server.encryption_protector.server_key_type
                        == "AzureKeyVault"
                    ):
                        for database in databases:
                            if found_disabled:
                                break
                            if database.tde_encryption.status == "Enabled":
                                report.status = "PASS"
                                report.status_extended = f"SQL Server {sql_server.name} from subscription {subscription_name} ({subscription}) has TDE enabled with CMK."
                            else:
                                report.status = "FAIL"
                                report.status_extended = f"SQL Server {sql_server.name} from subscription {subscription_name} ({subscription}) has TDE disabled with CMK."
                                found_disabled = True
                    else:
                        report.status = "FAIL"
                        report.status_extended = f"SQL Server {sql_server.name} from subscription {subscription_name} ({subscription}) has TDE disabled without CMK."
                    findings.append(report)

        return findings

References