Azure policy to configure a particular set of security audit actions and groups for an Azure SQL Server which may or may not include all of the default action groups. It includes the following conditions: (a) resource tag does not exist, (b) SQL Server Auditing is enabled, (c) Azure Monitor is the target, (d) default Audit action group exists. N…

ModifySQLServerAuditingActionsAndGroups.json

{
  "name": "863c7086-0e44-b000-1ced-c0ff33d00d11",
  "type": "Microsoft.Authorization/policyDefinitions",
  "properties": {
      "displayName": "Modify SQL Server Auditing Groups",
      "description": "Configures a specific set of Audit actions and groups for Azure SQL Server",
      "metadata": {
          "version": "1.0.0",
          "category": "SQL Server",
      }
  },
  "mode": "All",
  "policyRule": {
      "if": {
          "allOf": [
              {
                  "field": "type",
                  "equals": "Microsoft.Sql/servers/auditingSettings"
              },
              {
                  "field": "tags['SQLserverDefaultAuditing']",
                  "notIn": [
                      "1",
                      "true",
                      "enabled"
                  ]
              },
              {
                  "field": "Microsoft.Sql/servers/auditingSettings/state",
                  "matchInsensitively": "Enabled"
              },
              {
                  "field": "Microsoft.Sql/servers/auditingSettings/isAzureMonitorTargetEnabled",
                  "equals": true
              },
              {
                  "count": {
                      "field": "Microsoft.Sql/servers/auditingSettings/auditActionsAndGroups[*]",
                      "where": {
                          "field": "Microsoft.Sql/servers/auditingSettings/auditActionsAndGroups[*]",
                          "equals": "BATCH_COMPLETED_GROUP"
                      }
                  },
                  "equals": 1
              }
          ]
      },
      "then": {
          "details": {
              "conflictEffect": "audit",
              "operations": [
                  {
                      "field": "Microsoft.Sql/servers/auditingSettings/auditActionsAndGroups",
                      "operation": "addOrReplace",
                      "value": "[parameters('SQLserverAuditActionsAndGroups')]"
                  }
              ],
              "roleDefinitionIds": []
          },
          "effect": "[parameters('effect')]"
      }
  },
  "parameters": {
      "SQLserverAuditActionsAndGroups": {
          "type": "Array",
          "metadata": {
              "displayName": "SQL Audit Action Groups",
          },
          "defaultValue": [
              "APPLICATION_ROLE_CHANGE_PASSWORD_GROUP",
              "DATABASE_OWNERSHIP_CHANGE_GROUP",
              "DATABASE_PERMISSION_CHANGE_GROUP",
              "DATABASE_PRINCIPAL_CHANGE_GROUP",
              "DATABASE_ROLE_MEMBER_CHANGE_GROUP",
              "FAILED_DATABASE_AUTHENTICATION_GROUP",
              "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP",
              "USER_CHANGE_PASSWORD_GROUP"
          ]
      },
      "effect": {
          "type": "String",
          "metadata": {
              "displayName": "Effect"
          },
          "allowedValues": [
              "Disabled",
              "Audit",
              "Modify"
          ],
          "defaultValue": "Audit"
      }
  }
}