Enable Azure SQL Auditing with Azure Policy

7 Min. Read

Azure Policy is a service in Azure that you use to create, assign, and manage policies. These policies enforce different rules and effects over your resources, so those resources stay compliant with your corporate standards and service level agreements. Azure Policy meets this need by continuously evaluating your resources for non-compliance with assigned policies.

In this article, we will look at how to enable Azure SQL Auditing with Azure Policy and writes them to an audit log such as Azure storage account, Log Analytics workspace, or Event Hubs which can help you maintain regulatory compliance, understand database activity, and gain insight into discrepancies and anomalies that could indicate business concerns or suspected security violations.

Introduction

You have a policy in your organization that dictates to audit Azure SQL and centralize the logs into a single go-to-location on all new and existing servers and databases within your subscriptions. As you know very well, you should continuously audit your SQL databases to identify anomalies or any security violations.

In March 2021, Microsoft announced that Auditing for Azure SQL Database and Azure Synapse Analytics to Log Analytics Workspace and Event Hub destinations is generally available. These destinations allow you to centralize the SQL audit logs into a single go-to-location, providing a rich set of tools and capabilities for consuming and analyzing database audit logs at scale.

If we take a quick look at Azure Security Center as shown in the figure below, we do have already a built-in Policy definition that is based on the default Azure Security Benchmark (V2) that requires having auditing enabled on SQL Servers to pass this recommendation.

Auditing on SQL server should be enabled
Auditing on SQL server should be enabled

Based on this built-in policy, when you deploy a new SQL Server, Azure Security Center will evaluate the configuration and recommends enabling SQL Server audit to track database activities across all databases on the server and save them in an audit log.

However, this policy does not auto-remediate the unhealthy SQL resources, therefore, you should manually remediate them as described in the ‘Remediation steps‘, you can also use the ‘Quick fix logic‘ option to remediate all unhealthy resources at once, or you can trigger a logic app if you want. You can also exempt this recommendation if needed, but I don’t recommend doing so even if it’s categorized as ‘Low‘ Severity because it can help you to find suspicious events and unusual activities.

Please note that SQL auditing is available for Azure SQL Database and Azure Synapse Analytics. However, it’s not available for Azure SQL Managed Instance.

Once you have invested time and effort in remediating these resources to improve your security posture, you want to make sure that it will not decrease by a serious percentage, again. The problem is that the remediation is always done for existing resources, but NOT for new ones. You can think of remediating resources as a reactive versus a proactive approach.

As cybersecurity hygiene (practice), you need to have a solid Azure Governance to ensure that the new resources you deploy will have certain security standards, patterns, and configurations.

To ensure proper governance, we need to use Azure Policy. This will allow us to enable and enforce auditing on newly deployed Azure SQL Servers, as well as remediating existing resources at scale, so you can make sure your organization’s policy and security requirements are met.

Prerequisites

For the purpose of this article, we will show you how to enable Azure SQL Auditing with Azure Policy and writes them to Event Hub so you can send the data to external third-party SIEM systems, please note that the same logic can apply if you want to use Log Analytics workspace instead.

Please note that Microsoft does have a built-in policy to “Configure SQL servers to have auditing enabled” which will create a storage account for you and use it as a destination log target but NOT for Event Hub.

To follow this article, you need to have the following:

1) An Azure subscription. If you don’t have an Azure subscription, you can create a free one here.

2) At least one Azure SQL Server is deployed in your subscription.

3) You need an Event Hub namespace and an event hub in your Azure subscription. Learn how to create an event hub.

  • Take a note of the event hub namespace and the shared access policy name called (authorization rule), we will use them when we assign the custom policy definition.
  • The event hub authorization rule Id is in the following format: /subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.EventHub/namespaces/{eventHubNamespaceName}/AuthorizationRules/{authorizationRuleName}

Create Custom Policy definition

A quick overview of Azure Policy effects. Each policy definition that you create in the Azure Policy has a single effect. That effect determines what happens when the policy rule is evaluated to match. The effects behave differently if they are for a new resource, an updated resource, or an existing resource.

In this example, we need to enable auditing for all Azure SQL Servers and on existing resource(s) in our environment. For this to work, we need to use the “DeployIfNotExists” policy.

To understand how the Azure Policy effect works with the “DeployIfNotExists” policy definition, please check the official documentation from Microsoft here.

Open the Azure Portal, click “All services” and then search for “Policy” and then click on “Definitions” → “+ Policy definition”.

For the “Definition location“, select the location by clicking the ellipsis [] and select either a management group or a subscription.

In the “Name“ field, give a descriptive name for the policy definition such as – “Enable Auditing for SQL Servers to Event Hub” and add a Description.

In the “Category” section, select the appropriate category for this policy. In this example, we will choose “SQL” as shown in the figure below.

New Policy definition
New Policy definition

For the “POLICY RULE“, paste the following policy definition in JSON format and then click “Save“.

For the “Role definitions“, make sure that the Contributor is selected. The contributor role is required to enable auditing on the SQL resources.

Here is the Policy definition in JSON format:

{
  "mode": "All",
  "policyRule": {
    "if": {
      "allOf": [
        {
          "field": "type",
          "equals": "Microsoft.Sql/servers"
        },
        {
          "field": "location",
          "equals": "[parameters('resourceLocation')]"
        }
      ]
    },
    "then": {
      "effect": "deployIfNotExists",
      "details": {
        "type": "Microsoft.Sql/servers/auditingSettings",
        "roleDefinitionIds": [
          "/providers/Microsoft.Authorization/roleDefinitions/b24988ac-6180-42a0-ab88-20f7382dd24c"
        ],
        "existenceCondition": {
          "allOf": [
            {
              "field": "Microsoft.Sql/servers/auditingSettings/state",
              "equals": "Enabled"
            },
            {
              "field": "Microsoft.Sql/servers/auditingSettings/isAzureMonitorTargetEnabled",
              "equals": "true"
            },
            {
              "field": "Microsoft.Insights/diagnosticSettings/eventHubAuthorizationRuleId",
              "matchInsensitively": "[parameters('eventHubAuthorizationRuleId')]"
            }
          ]
        },
        "deployment": {
          "properties": {
            "mode": "incremental",
            "template": {
              "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
              "contentVersion": "1.0.0.0",
              "parameters": {
                "resourceName": {
                  "type": "string"
                },
                "eventHubAuthorizationRuleId": {
                  "type": "string"
                },
                "location": {
                  "type": "string"
                }
              },
              "variables": {
                "diagnosticSettingsName": "SQLSecurityAuditEvents_3d229c42-c7e7-4c97-9a99-ec0d0d8b86c1"
              },
              "resources": [
                {
                  "type": "Microsoft.Sql/servers/databases/providers/diagnosticSettings",
                  "apiVersion": "2017-05-01-preview",
                  "name": "[concat(parameters('resourceName'), '/master/', 'Microsoft.Insights/',variables('diagnosticSettingsName'))]",
                  "location": "[parameters('location')]",
                  "dependsOn": [],
                  "properties": {
                    "eventHubAuthorizationRuleId": "[parameters('eventHubAuthorizationRuleId')]",
                    "logs": [
                      {
                        "category": "SQLSecurityAuditEvents",
                        "enabled": true,
                        "retentionPolicy": {
                          "days": 0,
                          "enabled": false
                        }
                      }
                    ]
                  }
                },
                {
                  "type": "Microsoft.Sql/servers/auditingSettings",
                  "name": "[concat(parameters('resourceName'), '/Default')]",
                  "apiVersion": "2017-03-01-preview",
                  "properties": {
                    "State": "Enabled",
                    "isAzureMonitorTargetEnabled": true
                  }
                }
              ],
              "outputs": {}
            },
            "parameters": {
              "eventHubAuthorizationRuleId": {
                "value": "[parameters('eventHubAuthorizationRuleId')]"
              },
              "location": {
                "value": "[field('location')]"
              },
              "resourceName": {
                "value": "[field('fullName')]"
              }
            }
          }
        }
      }
    }
  },
  "parameters": {
    "resourceLocation": {
      "type": "String",
      "metadata": {
        "displayName": "Resource Location",
        "description": "Resource Location must be the same as the Event Hub Location",
        "strongType": "location"
      }
    },
    "eventHubAuthorizationRuleId": {
      "type": "String",
      "metadata": {
        "displayName": "Event Hub Shared Access Policy Authorization Rule Id",
        "description": "Specify Event Hub Shared Access Policy Authorization Rule Id"
      }
    }
  }
}

In this custom policy, I am looking for all Microsoft SQL Servers, and then I am checking if the auditing setting is enabled.

The ExistenceCondition option in the template above is super useful for the Azure SQL Servers that have already auditing settings enabled, as well as Azure Monitor Target is enabled. If this condition is true, the policy definition will skip the deployment defined in the “DeployIfNotExists” section.

Please note that this template will enable auditing at the server level and NOT at the database level. A server policy applies to all existing and newly created databases on the server. And once server auditing is enabled, it always applies to the database. The database will be audited, regardless of the database auditing settings.

To learn more about the ExistenceCondition option for the “DeployIfNotExists” properties, please check the official documentation here.

Assign Custom Policy definition

To assign the custom policy definition, take the following steps:

If you are looking to automate the deployment and assignment of Azure Policies via Azure DevOps instead of using the Azure Portal, then please check the following step-by-step guide.

Open the Azure Portal, click “All services” and then search for “Policy” and then click on “Assignments”. An assignment is a policy that has been assigned to take place within a specific scope.

Select “Assign policy” from the top of the “Policy | Assignments” page.

On the “Assign Policy” page, select the Scope by clicking the ellipsis [] and select either a management group or subscription. You can optionally select a resource group if you want. A scope determines what resources or grouping of resources the policy assignment gets enforced on. Then click Select at the bottom of the Scope page.

Select the Policy definition ellipsis [] to open the list of available definitions. Choose the custom policy that we created in the previous step. The Policy enforcement is Enabled by default. Click Next to continue.

On the Parameters page, specify the parameters for this policy assignment (Resource Location, and Event Hub Shared Access Policy Authorization Rule Id). Please refer to the prerequisites section to see how to get the Event Hub Authorization Rule Id. Click Next to continue.

Assign Policy
Assign Policy

In the Remediation page, by default “Create a Managed Identity” is selected for you because existing resources can be updated via a remediation task after the policy is assigned only. Choose the default location for the “Managed Identity“, this is required because policies with the “deployIfNotExists” and “Modify” effect types need the ability to modify resources and edit the configuration on existing resources respectively. To do this, a managed identity will be created automatically for each policy assignment. This identity will also be given the “Contributor” permissions on the scope that you have selected. Click Next to continue.

Create a Managed Identity
Create a Managed Identity

On the Non-compliance messages page, set the desired message. Then click the “Review + create” button.

Finally, click “Create” to create the assignment.

Verify auditing on SQL Servers is enabled

To verify that the custom policy is deployed successfully, you need to wait for at least 30 minutes after a resource has been created or updated. The policy won’t be triggered immediately, this is by design. To trigger the policy compliance scan manually (immediately), open the cloud shell and run the following command:

$job = Start-AzPolicyComplianceScan -AsJob
$job | ft -AutoSize

The scan job will run in the background.

Behind the scene, Azure Policy will create a Remediation task as shown in the figure below. To remediate existing resources, select the desired policy and then click on the “Remediate” button.

Policy Remediation | Enable SQL Servers auditing
Policy Remediation | Enable SQL Servers auditing

Please note that during an evaluation cycle, the policy definition with a “DeployIfNotExists” effect that matches resources is marked as non-compliant only, but no action is taken on that resource. For this reason, the remediation task was created when you assign the policy definition as described in the previous section.

Open the Azure Portal, click “All services” and then search for “Policy” and then click on “Remediation”. In the Policy | Remediation page, select the Remediation tasks tab, and check the status of the auto-remediation task. It’s completed successfully without any error.

If you look at the Azure SQL Server under Security | Auditing, you can see that Azure SQL Auditing is enabled and Event Hub is set as a target log destination.

Azure SQL Auditing is enabled
Azure SQL Auditing is enabled to Event Hub

That’s it there you have it!

Summary

In this article, we showed you how to enable and enforce Azure SQL auditing with Azure Policy, so you can make sure your organization’s policy and security requirements are met.

Azure Policies are becoming increasingly popular, as they provide a wide range of management capabilities over Azure resources. Not only do they allow us to enforce policy rules to control Azure resources, but they can also enable us to automatically evaluate compliance and remediate non-compliant resources.

__
Thank you for reading my blog.

If you have any questions or feedback, please leave a comment.

-Charbel Nemnom-

Related Posts

Previous

How To Rename an Azure Virtual Machine

Enable Vulnerability Assessment on SQL Servers with Azure Policy

Next

Let me know what you think, or ask a question...

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe to Stay in Touch

Never miss out on your favorite posts and our latest announcements!

The content of this website is copyrighted from being plagiarized!

You can copy from the 'Code Blocks' in 'Black' by selecting the Code.

Please send your feedback to the author using this form for any 'Code' you like.

Thank you for visiting!