Notes on Azure SQL Server Auditting should be enabled policy

Recently I was asked to help a colleague of mine on a policy named “Azure SQL Server auditing should be enabled“. He deployed an ARM template to enable auditing but the deployment didn’t reflect the setting in Azure Portal.

In this article, let’s look into the problem the colleague had. We will also modify the built-in policy to make it more useful.

First, let’s look at the policy that Microsoft provides:

{
  "properties": {
    "displayName": "Auditing on SQL server should be enabled",
    "policyType": "BuiltIn",
    "mode": "Indexed",
    "description": "Auditing on your SQL Server should be enabled to track database activities across all databases on the server and save them in an audit log.",
    "metadata": {
      "version": "2.0.0",
      "category": "SQL"
    },
    "parameters": {
      "effect": {
        "type": "string",
        "defaultValue": "AuditIfNotExists",
        "allowedValues": [
          "AuditIfNotExists",
          "Disabled"
        ],
        "metadata": {
          "displayName": "Effect",
          "description": "Enable or disable the execution of the policy"
        }
      },
      "setting": {
        "type": "String",
        "metadata": {
          "displayName": "Desired Auditing setting"
        },
        "defaultValue": "enabled",
        "allowedValues": [
          "enabled",
          "disabled"
        ]
      }
    },
    "policyRule": {
      "if": {
        "allOf": [
          {
            "field": "type",
            "equals": "Microsoft.Sql/servers"
          },
          {
            "field": "kind",
            "notContains": "analytics"
          }
        ]
      },
      "then": {
        "effect": "[parameters('effect')]",
        "details": {
          "type": "Microsoft.Sql/servers/auditingSettings",
          "name": "default",
          "existenceCondition": {
            "field": "Microsoft.Sql/auditingSettings.state",
            "equals": "[parameters('setting')]"
          }
        }
      }
    }
  },
  "id": "/providers/Microsoft.Authorization/policyDefinitions/a6fb4358-5bf4-4ad7-ba82-2cd2f41ce5e9",
  "name": "a6fb4358-5bf4-4ad7-ba82-2cd2f41ce5e9"
}

The policy targets to audit Microsoft.Sql/servers resource type. It uses AuditIfNotExists to cross-audit the auditingSettings resource’s state to check whether it is set “Enabled“. Well, let’s assume there isn’t any issue with this policy. We will talk about it later in this post.

The colleague was asked to check his ARM template and ensure it had auditing enabled at the server level and the log was sent to a specified Log Analytics workspace. The template “should” look like as follow:

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "baseName": {
      "type": "String",
      "metadata": {
        "description": "Basename of resource deployment"
      }
    },
    "location": {
      "type": "string",
      "defaultValue": "[resourceGroup().location]",
      "metadata": {
        "description": "Location for your resources."
      }
    },
    "sqlAdministratorLogin": {
      "type": "string",
      "metadata": {
        "description": "The admin username of your Azure SQL Server"
      }
    },
    "sqlAdministratorLoginPassword": {
      "type": "securestring",
      "metadata": {
        "description": "The admin password of the SQL Server."
      }
    },
    "logAnalyticsWorkspaceResourceId": {
      "type": "string",
      "metadata": {
        "description": "The resource Id of Log Analytics workspace to store audit log"
      }
    }
  },
  "variables": {
    "sqlServerName": "[concat(parameters('baseName'), 'sqlsrv')]",
    "sampleDbName": "[concat(parameters('baseName'), 'db')]",
    "diagnosticSettingsName": "[concat(parameters('baseName'), 'diagnostic')]"
  },
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2021-02-01-preview",
      "location": "[parameters('location')]",
      "name": "[variables('sqlServerName')]",
      "properties": {
        "administratorLogin": "[parameters('sqlAdministratorLogin')]",
        "administratorLoginPassword": "[parameters('sqlAdministratorLoginPassword')]",
        "version": "12.0"
      },
      "resources": [
        {
          "type": "databases",
          "apiVersion": "2021-02-01-preview",
          "name": "[variables('sampleDbName')]",
          "location": "[parameters('location')]",
          "sku": {
            "name": "Standard",
            "tier": "Standard"
          },
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers', variables('sqlServerName'))]"
          ]
        },
        {
          "type": "auditingSettings",
          "name": "auditing",
          "apiVersion": "2021-02-01-preview",
          "dependsOn": [
              "[resourceId('Microsoft.Sql/servers', variables('sqlServerName'))]"
          ],
          "properties": {
            "state": "Enabled",
            "isAzureMonitorTargetEnabled": true
          }
        },
        {
          "type": "databases/providers/diagnosticSettings",
          "name": "[concat(variables('sampleDbName'), '/microsoft.insights/', variables('diagnosticSettingsName'))]",
          "apiVersion": "2017-05-01-preview",
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers', variables('sqlServerName'))]",
            "[resourceId('Microsoft.Sql/servers/databases', variables('sqlServerName'), variables('sampleDbName'))]"
          ],
          "properties": {
            "name": "[variables('diagnosticSettingsName')]",
            "workspaceId": "[parameters('logAnalyticsWorkspaceResourceId')]",
            "logs": [
              {
                "category": "SQLSecurityAuditEvents",
                "enabled": true
              }
            ]
          }
        }
      ]
    }
  ]
}

This ARM deployment template looks cool. If you deploy it and check the audit setting from Azure Portal you wouldn’t see the feature is enabled.

You might wonder if that is a bug. In fact, this is not a bug.  Although there is nothing wrong with the above template, it did miss the important part for the auditing feature to be persistently set. The missing part is the deployment of the diagnostic setting on the master database. Here is how we can solve the problem to enable the Auditing feature at the server level:

{
  "type": "Microsoft.Sql/servers",
  "apiVersion": "2021-02-01-preview",
  "location": "[parameters('location')]",
  "name": "[variables('sqlServerName')]",
  "properties": {
    "administratorLogin": "[parameters('sqlAdministratorLogin')]",
    "administratorLoginPassword": "[parameters('sqlAdministratorLoginPassword')]",
    "version": "12.0"
  },
  "resources": [
    {
      "type": "databases",
      "apiVersion": "2017-03-01-preview",
      "name": "master",
      "location": "[parameters('location')]",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers', variables('sqlServerName'))]"
      ],
      "properties": {}
    },
    {
      "type": "auditingSettings",
      "name": "auditing",
      "apiVersion": "2021-02-01-preview",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers', variables('sqlServerName'))]"
      ],
      "properties": { "state": "Enabled", "isAzureMonitorTargetEnabled": true }
    },
    {
      "type": "databases/providers/diagnosticSettings",
      "name": "[concat('master/microsoft.insights/', variables('diagnosticSettingsName'))]",
      "apiVersion": "2017-05-01-preview",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers', variables('sqlServerName'))]"
      ],
      "properties": {
        "name": "[variables('diagnosticSettingsName')]",
        "workspaceId": "[parameters('logAnalyticsWorkspaceResourceId')]",
        "logs": [{ "category": "SQLSecurityAuditEvents", "enabled": true }]
      }
    }
  ]
}

If you want to create a separate diagnostic setting here is the way:

"resources": [
  {
    "type": "Microsoft.Sql/servers",
    "apiVersion": "2021-02-01-preview",
    "location": "[parameters('location')]",
    "name": "[variables('sqlServerName')]",
    "properties": {
      "administratorLogin": "[parameters('sqlAdministratorLogin')]",
      "administratorLoginPassword": "[parameters('sqlAdministratorLoginPassword')]",
      "version": "12.0"
    },
    "resources": [
      {
        "type": "auditingSettings",
        "name": "auditing",
        "apiVersion": "2021-02-01-preview",
        "dependsOn": [
          "[resourceId('Microsoft.Sql/servers', variables('sqlServerName'))]"
        ],
        "properties": {
          "state": "Enabled",
          "isAzureMonitorTargetEnabled": true
        }
      },
      {
        "type": "databases",
        "apiVersion": "2021-02-01-preview",
        "name": "master",
        "location": "[parameters('location')]",
        "dependsOn": [
          "[resourceId('Microsoft.Sql/servers', variables('sqlServerName'))]"
        ],
        "properties": {}
      }
    ]
  },
  {
    "type": "microsoft.sql/servers/databases/providers/diagnosticSettings",
    "name": "[concat(variables('sqlServerName'),'/master/microsoft.insights/', variables('diagnosticSettingsName'))]",
    "apiVersion": "2017-05-01-preview",
    "dependsOn": [
      "[resourceId('Microsoft.Sql/servers', variables('sqlServerName'))]",
      "[resourceId('Microsoft.Sql/servers/databases', variables('sqlServerName'), 'master')]"
    ],
    "properties": {
      "name": "[variables('diagnosticSettingsName')]",
      "workspaceId": "[parameters('logAnalyticsWorkspaceResourceId')]",
      "logs": [
        {
          "category": "SQLSecurityAuditEvents",
          "enabled": true
        }
      ]
    }
  }
]

Notes: If your Azure SQL Server doesn’t have any database you must create a master one and set a diagnostic setting for it. Otherwise, you can create an empty database. The master database will be created automatically during that empty database creation. In case creating the master database isn’t allowed (in the future), you should create an empty database for testing. In a real-world scenario you shouldn’t have any worry because no Azure SQL Server doesn’t have a database.

You can go to the Azure Portal and verify the auditing feature:

Azure Policy

Alright now let’s revisit the Azure SQL Server auditing should be enabled policy again. Obviously from our experiment, the policy condition isn’t sufficient. In a real-world scenario, you would want to audit Azure SQL Server to ensure the audit feature is enabled at the server level and the log is configured to be sent to a central Log Analytics workspace right?

You would need a policy initiative to audit both auditSettings and diagnosticSettings because they are two different resource types that couldn’t be put in a single policy.

Below are some good references:

This entry was posted in Secure Development, Security Automation and tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *