Azure Audit SQL Server Tde Protector Encrypted Cmk Fix

Triage and Remediation

Remediation

Remediation

Using Console

To remediate the misconfiguration "Ensure SQL Server TDE Protector Is Encrypted With CMK" in Azure, follow the below steps:

Open the Azure portal and navigate to the Azure SQL database that you want to remediate.
Click on the "Transparent data encryption" option under the Security section on the left-hand side menu.
In the "Transparent data encryption" blade, check if the "TDE Protector" is set to "Service Managed". If it is set to "Service Managed", then it means that the TDE protector is not encrypted with a customer-managed key (CMK).
To encrypt the TDE protector with a CMK, click on the "Set" button next to the "TDE Protector" option.
In the "Set TDE Protector" blade, select the "Customer-managed key" option and choose the CMK that you want to use to encrypt the TDE protector.
Click on the "Save" button to save the changes.
Once the changes are saved, the TDE protector will be encrypted with the selected CMK.
Verify the remediation by checking the "Transparent data encryption" blade again and ensure that the "TDE Protector" is now set to "Customer-managed key".

By following these steps, you can remediate the misconfiguration "Ensure SQL Server TDE Protector Is Encrypted With CMK" in Azure using the Azure console.

Using CLI

To remediate the misconfiguration "Ensure SQL Server TDE Protector Is Encrypted With CMK" in AZURE using AZURE CLI, follow the below steps:

Step 1: Open the Azure CLI and log in to your Azure account using the command:

az login

Step 2: Once you are logged in, set the default subscription to the one where the SQL Server is located using the command:

az account set --subscription <subscription_id>

Step 3: Check if the TDE Protector is encrypted with CMK using the below command:

az sql db tde-key show --resource-group <resource_group_name> --server <server_name> --database <database_name>

Step 4: If the TDE Protector is not encrypted with CMK, create a new CMK using the command:

az keyvault create --name <keyvault_name> --resource-group <resource_group_name> --location <location>

Step 5: After creating the CMK, enable the TDE Protector encryption with the CMK using the command:

az sql db tde-key set --resource-group <resource_group_name> --server <server_name> --database <database_name> --server-key-type AzureKeyVault --server-key-name <keyvault_name> --server-key-vault <keyvault_uri>

Note: Replace <keyvault_name>, <keyvault_uri>, <resource_group_name>, <server_name>, and <database_name> with the actual values.

Step 6: Verify the TDE Protector is encrypted with CMK using the below command:

az sql db tde-key show --resource-group <resource_group_name> --server <server_name> --database <database_name>

Step 7: Once verified, you have successfully remediated the misconfiguration "Ensure SQL Server TDE Protector Is Encrypted With CMK" in AZURE using AZURE CLI.

Using Python

To remediate the misconfiguration "Ensure SQL Server TDE Protector Is Encrypted With CMK" for Azure using Python, follow these steps:

First, ensure that you have the Azure SDK for Python installed on your system.
Next, use the following Python code to remediate the misconfiguration:

from azure.identity import DefaultAzureCredential
from azure.mgmt.sql import SqlManagementClient
from azure.mgmt.sql.models import ServerSecurityAlertPolicy, ServerSecurityAlertPolicyProperties, ServerSecurityAlertPolicyStatelessPolicy, ServerSecurityAlertPolicyStatelessPolicyCustomAlerts, ServerSecurityAlertPolicyStatelessPolicyCustomAlertsEmails, ServerSecurityAlertPolicyStatelessPolicyCustomAlertsWebhooks, ServerSecurityAlertPolicyStatelessPolicyCustomAlertsActions, ServerSecurityAlertPolicyStatelessPolicyCustomAlertsSuppressions

# Set the credentials for the Azure account
credential = DefaultAzureCredential()

# Set the subscription ID and resource group name
subscription_id = 'YOUR_SUBSCRIPTION_ID'
resource_group_name = 'YOUR_RESOURCE_GROUP_NAME'

# Set the SQL server name and database name
server_name = 'YOUR_SQL_SERVER_NAME'
database_name = 'YOUR_SQL_DATABASE_NAME'

# Instantiate the SQL management client
sql_client = SqlManagementClient(credential, subscription_id)

# Get the current security alert policy for the SQL server
security_alert_policy = sql_client.server_security_alert_policies.get(resource_group_name, server_name)

# Set the encryption protector to use CMK
security_alert_policy.properties.encryption_protector = {
    "id": "/subscriptions/{}/resourceGroups/{}/providers/Microsoft.KeyVault/vaults/{}/keys/{}/{}".format(
        subscription_id,
        resource_group_name,
        "YOUR_KEY_VAULT_NAME",
        "YOUR_KEY_NAME",
        "YOUR_KEY_VERSION"
    )
}

# Update the security alert policy with the new encryption protector
sql_client.server_security_alert_policies.create_or_update(resource_group_name, server_name, security_alert_policy)

print("Security alert policy updated successfully.")

Replace the placeholders in the code (e.g. YOUR_SUBSCRIPTION_ID, YOUR_RESOURCE_GROUP_NAME, YOUR_SQL_SERVER_NAME, etc.) with the appropriate values for your Azure environment.
Run the Python code to update the security alert policy for the SQL server and ensure that the TDE protector is encrypted with CMK.

Note: You will need to have appropriate permissions to update the security alert policy for the SQL server.

Using Terraform

# Existing SQL Server
resource "azurerm_mssql_server" "SQL_SERVER" {
  name                         = "SQL_SERVER_NAME"          # substitute: your SQL Server name
  resource_group_name          = "RESOURCE_GROUP_NAME"      # substitute: your resource group
  location                     = "AZURE_REGION"             # substitute: e.g. "eastus"
  version                      = "12.0"
  administrator_login          = "ADMIN_USERNAME"           # substitute
  administrator_login_password = "ADMIN_PASSWORD"           # substitute
}

# Key Vault that holds the Customer-Managed Key (CMK)
resource "azurerm_key_vault" "TDE_KV" {
  name                       = "KEY_VAULT_NAME"           # substitute
  location                   = azurerm_mssql_server.SQL_SERVER.location
  resource_group_name        = azurerm_mssql_server.SQL_SERVER.resource_group_name
  tenant_id                  = "TENANT_ID"                # substitute: Azure AD tenant GUID
  sku_name                   = "premium"

  purge_protection_enabled   = true
  soft_delete_retention_days = 90

  access_policy {
    tenant_id = "TENANT_ID"                               # same as above
    object_id = "SQL_SERVER_IDENTITY_OBJECT_ID"           # substitute: SQL Server’s managed identity objectId

    key_permissions = [
      "get",
      "wrapKey",
      "unwrapKey",
    ]
  }
}

# CMK inside Key Vault
resource "azurerm_key_vault_key" "TDE_CMK" {
  name         = "TDE-CMK-NAME"                           # substitute
  key_vault_id = azurerm_key_vault.TDE_KV.id
  key_type     = "RSA"
  key_size     = 2048

  key_opts = [
    "wrapKey",
    "unwrapKey",
  ]
}

# Associate CMK with SQL Server as the TDE protector
resource "azurerm_mssql_server_key" "SQL_SERVER_TDE_PROTECTOR" {
  server_id        = azurerm_mssql_server.SQL_SERVER.id
  key_vault_key_id = azurerm_key_vault_key.TDE_CMK.id
}

This change switches the TDE protector to a customer-managed key but does not force replacement of the azurerm_mssql_server resource; SQL transparently rotates the protector and re-encrypts data in place.

Verification with terraform plan should show:

creation of azurerm_mssql_server_key.SQL_SERVER_TDE_PROTECTOR (and azurerm_key_vault / azurerm_key_vault_key if they are new),
no destroy/replace for the existing azurerm_mssql_server.SQL_SERVER.

Triage and Remediation​

Remediation​

Triage and Remediation

Remediation