Azure SQL Database Compliance: Automating Transparent Data Encryption (TDE) with Azure Policy and ARM Templates

Question  :- 

You have an Azure subscription that contains 50 Azure SQL databases.

You create an Azure Resource Manager (ARM) template named Template1 that enables Transparent Data Encryption (TDE).

You need to create an Azure Policy definition named Policy1 that will use Template1 to enable TDE for any noncompliant Azure SQL databases.

How should you configure Policy1? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Answer Area:

Set available effects to:

1.      DeployIfNotExists (This would be the correct option)
2.      EnforceRegoPolicy
3.      Modify

Include in the definition:

1.     The identity required to perform the remediation task (This would be the correct option)
2.     The scopes of the policy assignments
3.     The role-based access control (RBAC) roles required to perform the remediation task (This would be the correct option)

Introduction:

Maintaining security compliance for databases in the cloud is essential for organizations, especially with the increasing demand for data privacy and regulatory standards such as GDPR and HIPAA. Azure Policy allows you to enforce and automate security measures like Transparent Data Encryption (TDE) on your Azure SQL Databases. 

In this blog, we will break down how to create and apply an Azure Policy that ensures TDE is enabled for any non-compliant Azure SQL databases, using an ARM template and the DeployIfNotExists effect.

---

Table of Contents:

1. Understanding the Problem Scenario
2. Breaking Down the Solution Requirements
   • Set Available Effects: DeployIfNotExists
   • Identity and Role-Based Access Control (RBAC)
3. Writing the Azure Policy Definition
4. Applying the Policy in the Azure Portal and via CLI
5. Practical Use Cases and Benefits
6. Conclusion

---

1. Understanding the Problem Scenario:

You have an Azure subscription containing 50 Azure SQL databases. To ensure security and compliance, you want to enforce Transparent Data Encryption (TDE) on all databases. While some databases may already have TDE enabled, others may not comply with this standard.

You created an Azure Resource Manager (ARM) template to enable TDE. Now, you need to create an Azure Policy that will automatically enable TDE on any non-compliant Azure SQL databases by deploying this template.

---

2. Breaking Down the Solution Requirements:

A. Set Available Effects: DeployIfNotExists:

The key to this solution is using the DeployIfNotExists effect, which is part of Azure Policy's remediation actions. This effect triggers the deployment of an ARM template to ensure resources meet the specified policy. In our case, it will automatically apply TDE to non-compliant databases.

• Effect Description: DeployIfNotExists ensures that if a condition is not met (e.g., TDE not enabled), an ARM template is deployed to bring the resource into compliance.

B. Identity and Role-Based Access Control (RBAC):

• Identity for Remediation: Azure assigns a managed identity to perform the remediation task. This identity needs the appropriate permissions (via RBAC) to deploy the ARM template.

• RBAC Roles: The Contributor role is required for the identity to deploy ARM templates, ensuring it has sufficient permissions to perform the task.

---

3. Writing the Azure Policy Definition:

Here’s the Azure Policy definition that checks whether TDE is enabled and, if not, deploys an ARM template to enable it.

json

{
  "mode": "All",
  "policyRule": {
    "if": {
      "allOf": [
        {
          "field": "type",
          "equals": "Microsoft.Sql/servers/databases"
        },
        {
          "field": "Microsoft.Sql/servers/databases/transparentDataEncryption.status",
          "notEquals": "Enabled"
        }
      ]
    },
    "then": {
      "effect": "DeployIfNotExists",
      "details": {
        "type": "Microsoft.Sql/servers/databases/transparentDataEncryption",
        "name": "current",
        "deployment": {
          "properties": {
            "mode": "incremental",
            "template": {
              "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
              "contentVersion": "1.0.0.0",
              "resources": [
                {
                  "type": "Microsoft.Sql/servers/databases/transparentDataEncryption",
                  "apiVersion": "2014-04-01",
                  "name": "[concat(parameters('serverName'), '/', parameters('databaseName'), '/current')]",
                  "properties": {
                    "status": "Enabled"
                  }
                }
              ]
            },
            "parameters": {
              "serverName": {
                "value": "[field('name')]"
              },
              "databaseName": {
                "value": "[field('name')]"
              }
            }
          }
        },
        "roleDefinitionIds": [
          "/providers/microsoft.authorization/roleDefinitions/00000000-0000-0000-0000-000000000000" // Contributor role
        ],
        "existenceCondition": {
          "allOf": [
            {
              "field": "Microsoft.Sql/servers/databases/transparentDataEncryption.status",
              "equals": "Enabled"
            }
          ]
        }
      }
    }
  },
  "parameters": {},
  "displayName": "Enable Transparent Data Encryption (TDE) on SQL Databases",
  "description": "Ensures Transparent Data Encryption (TDE) is enabled on all Azure SQL databases.",
  "metadata": {
    "version": "1.0.0",
    "category": "SQL"
  }
}

---

4. Applying the Policy in the Azure Portal and via CLI:

A. Applying in the Azure Portal:

1. Create the Policy Definition:

   • Navigate to the Azure Portal > Policy > Definitions.
   • Click on + Policy Definition.
   • Paste the JSON code above into the Policy Rule section.
   • Set the Display Name (e.g., "Enable TDE on SQL Databases") and Category (e.g., SQL).
   • Save the policy definition.

2. Assign the Policy:

   • After creating the policy definition, go to Assignments.
   • Click + Assign Policy.
   • Select the policy you just created.
   • Choose the scope (subscription or resource group level).
   • Click Assign to enforce the policy.

B. Applying the Policy Using Azure CLI:

You can automate the policy assignment using Azure CLI.

bash

az policy assignment create \
  --name "EnableTDEonSQLDatabases" \
  --policy "/subscriptions/{subscription-id}/providers/Microsoft.Authorization/policyDefinitions/{policy-definition-id}" \
  --scope "/subscriptions/{subscription-id}" \
  --display-name "Enable Transparent Data Encryption on SQL Databases"

Replace the placeholders:

• {subscription-id}: Your Azure subscription ID.
• {policy-definition-id}: The ID of the policy definition created earlier.

---

5. Practical Use Cases and Benefits:

• Security Compliance: Automatically enabling TDE ensures that your databases meet data protection standards, such as GDPR, HIPAA, and ISO 27001.
• Cost Savings: Automating the process saves time by eliminating manual compliance checks, reducing the risk of data breaches.
• Operational Efficiency: Ensuring consistent security policies across all databases.

---

6. Conclusion:

By using Azure Policy with the DeployIfNotExists effect, you can automate the enforcement of Transparent Data Encryption (TDE) across your Azure SQL databases, ensuring compliance and security with minimal manual effort. The Contributor role and managed identity ensure that the deployment tasks have the necessary permissions to make changes to your databases, allowing for smooth and automated remediation.

---

Memory Technique:

Think of Azure Policy as the guard at a factory. It checks whether everything is secure (i.e., whether TDE is enabled). If it finds a weak point, it immediately deploys a solution (ARM template) to reinforce the protection (enable TDE). This analogy helps you understand how Azure Policy ensures compliance with security policies.