Ensure SQL Server TDE Protector Is Encrypted With CMK

More Info:

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. To protect this data encryption key (DEK) in the past, only a certificate that the Azure SQL Service managed could be used. Now, with Customer-managed key support for TDE, the DEK can be protected with an asymmetric key that is stored in the Key Vault. Key Vault is a highly available and scalable cloud-based key store which 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.

Risk Level

Medium

Address

Security

Compliance Standards

CISAZURE, CBP, SOC2, NISTCSF, PCIDSS

Triage and Remediation

Remediation

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.

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.

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.

Additional Reading:

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption-byok-azure-sql

Azure Introduction

Azure Pricing

Azure Threats

Azure Misconfigurations

Ensure SQL Server TDE Protector Is Encrypted With CMK

More Info:

Risk Level

Address

Compliance Standards

Triage and Remediation

Remediation

Additional Reading:

Azure Introduction

Azure Pricing

Azure Threats

Azure Misconfigurations

​More Info:

​Risk Level

​Address

​Compliance Standards

​Triage and Remediation

Remediation

​Additional Reading:

More Info:

Risk Level

Address

Compliance Standards

Triage and Remediation

Additional Reading: