last sync: 2021-Mar-03 15:53:01 UTC

Azure Policy definition

Deploy - Configure diagnostic settings for Azure SQL Database server to Log Analytics workspace

Name Deploy - Configure diagnostic settings for Azure SQL Database server to Log Analytics workspace
Azure Portal
Id 7ea8a143-05e3-4553-abfe-f56bef8b0b70
Version 1.0.1
details on versioning
Category SQL
Microsoft docs
Description Deploys the diagnostic settings for Azure SQL Database server to stream resource logs to a Log Analytics workspace when any SQL Server which is missing this diagnostic settings is created or updated.
Mode Indexed
Type BuiltIn
Preview FALSE
Deprecated FALSE
Effect Default: DeployIfNotExists
Allowed: (DeployIfNotExists, Disabled)
Used RBAC Role
Role Name Role Id
SQL Security Manager 056cd41c-7e88-42e1-933e-88ba6a50c9c3
Log Analytics Contributor 92aaf0da-9dab-42b6-94a3-d43ce8d16293
History
Date/Time (UTC ymd) (i) Change type Change detail
2021-02-10 14:43:58 add 7ea8a143-05e3-4553-abfe-f56bef8b0b70
Used in Initiatives none
Json
{
  "properties": {
    "displayName": "Deploy - Configure diagnostic settings for Azure SQL Database server to Log Analytics workspace",
    "policyType": "BuiltIn",
    "mode": "Indexed",
    "description": "Deploys the diagnostic settings for Azure SQL Database server to stream resource logs to a Log Analytics workspace when any SQL Server which is missing this diagnostic settings is created or updated.",
    "metadata": {
      "version": "1.0.1",
      "category": "SQL"
    },
    "parameters": {
      "logAnalyticsWorkspaceId": {
        "type": "String",
        "metadata": {
          "displayName": "Log Analytics workspace",
          "description": "Specify the Log Analytics workspace the server should be connected to.",
          "strongType": "omsWorkspace",
          "assignPermissions": true
        }
      },
      "effect": {
        "type": "String",
        "metadata": {
          "displayName": "Effect",
          "description": "Enable or disable the execution of the policy"
        },
        "allowedValues": [
          "DeployIfNotExists",
          "Disabled"
        ],
        "defaultValue": "DeployIfNotExists"
      }
    },
    "policyRule": {
      "if": {
        "field": "type",
        "equals": "Microsoft.Sql/servers"
      },
      "then": {
      "effect": "[parameters('effect')]",
        "details": {
          "type": "Microsoft.Sql/servers/auditingSettings",
          "name": "Default",
          "existenceCondition": {
            "field": "Microsoft.Sql/auditingSettings.state",
            "equals": "Enabled"
          },
          "roleDefinitionIds": [
            "/providers/Microsoft.Authorization/roleDefinitions/056cd41c-7e88-42e1-933e-88ba6a50c9c3",
            "/providers/Microsoft.Authorization/roleDefinitions/92aaf0da-9dab-42b6-94a3-d43ce8d16293"
          ],
          "deployment": {
            "properties": {
              "mode": "incremental",
              "template": {
                "$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
                "contentVersion": "1.0.0.0",
                "parameters": {
                  "serverName": {
                    "type": "string"
                  },
                  "logAnalyticsWorkspaceId": {
                    "type": "string"
                  }
                },
                "variables": {
                  "diagnosticSettingsName": "SQLSecurityAuditEvents_3d229c42-c7e7-4c97-9a99-ec0d0d8b86c1"
                },
                "resources": [
                  {
                    "type": "Microsoft.Sql/servers/databases/providers/diagnosticSettings",
                  "name": "[concat(parameters('serverName'),'/master/microsoft.insights/',variables('diagnosticSettingsName'))]",
                    "apiVersion": "2017-05-01-preview",
                    "properties": {
                    "name": "[variables('diagnosticSettingsName')]",
                    "workspaceId": "[parameters('logAnalyticsWorkspaceId')]",
                      "logs": [
                        {
                          "category": "SQLSecurityAuditEvents",
                          "enabled": true,
                          "retentionPolicy": {
                            "days": 0,
                            "enabled": false
                          }
                        }
                      ]
                    }
                  },
                  {
                  "name": "[concat(parameters('serverName'), '/Default')]",
                    "type": "Microsoft.Sql/servers/auditingSettings",
                    "apiVersion": "2017-03-01-preview",
                    "dependsOn": [
                    "[concat('Microsoft.Sql/servers/', parameters('serverName'),'/databases/master/providers/microsoft.insights/diagnosticSettings/', variables('diagnosticSettingsName'))]"
                    ],
                    "properties": {
                      "state": "Enabled",
                      "isAzureMonitorTargetEnabled": true
                    }
                  }
                ]
              },
              "parameters": {
                "serverName": {
                "value": "[field('name')]"
                },
                "logAnalyticsWorkspaceId": {
                "value": "[parameters('logAnalyticsWorkspaceId')]"
                }
              }
            }
          }
        }
      }
    }
  },
  "id": "/providers/Microsoft.Authorization/policyDefinitions/7ea8a143-05e3-4553-abfe-f56bef8b0b70",
  "type": "Microsoft.Authorization/policyDefinitions",
  "name": "7ea8a143-05e3-4553-abfe-f56bef8b0b70"
}