Azure SQL Server Auditing: Setting Log Analytics Destination

Azure SQL Server Auditing: Setting Log Analytics Destination

Recently, I was having some trouble getting Log Analytics set as the destination for Azure SQL Server auditing via the ARM Template for server-level auditing. Early attempts would enable the auditing, but the Log Analytics workspace would be blank. Eventually I discovered the solution, so I'm documenting the process here to help others (and future Shawn πŸ‘‹).

Using the Portal, this trivial to configure:

The key Β to configure via ARM template is that you must enable Diagnostic Settings on the master database.

Here are the three required resources for your ARM template:

1. Obviously, SQL Server itself:

{
  "type": "Microsoft.Sql/servers",
  "apiVersion": "2015-05-01-preview",
  "name": "[parameters('serverName')]",
  "location": "northcentralus",
  "kind": "v12.0",
  "properties": {
    "administratorLogin": "Azure_DBA",
    "administratorLoginPassword": "[parameters('sqlServerAdminPassword')]",
    "version": "12.0"
  },
  "identity": {
    "type": "SystemAssigned"
  }
}

2. Then the auditingSettings for SQL Server to enable auditing:

{
  "type": "Microsoft.Sql/servers/auditingSettings",
  "apiVersion": "2017-03-01-preview",
  "name": "[concat(parameters('serverName'),'/Default')]",
  "dependsOn": [
    "[resourceId('Microsoft.Sql/servers',parameters('serverName'))]"
  ],
  "properties": {
    "state": "Enabled",
    "retentionDays": 0,
    "auditActionsAndGroups": [
      "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP",
      "FAILED_DATABASE_AUTHENTICATION_GROUP",
      "BATCH_COMPLETED_GROUP"
    ],
    "storageAccountSubscriptionId": "00000000-0000-0000-0000-000000000000",
    "isAzureMonitorTargetEnabled": true
  }
}

πŸ”‘ 3. Finally, the key to persist the Log Analytics destination is enabling diagnosticSettings on the master database:

{
  "type": "microsoft.sql/servers/databases/providers/diagnosticSettings",
  "apiVersion": "2017-05-01-preview",
  "name": "[concat(parameters('serverName'),'/',parameters('dbName'),'/microsoft.insights/',variables('settingName'))]",
  "dependsOn": [
    "[resourceId('Microsoft.Sql/servers',parameters('serverName'))]"
  ],
  "properties": {
    "workspaceId": "[resourceId('microsoft.operationalinsights/workspaces', parameters('workspaceName'))]"
    "logAnalyticsDestinationType": "Dedicated",
    "logs": [
      {
        "category": "SQLSecurityAuditEvents",
        "enabled": true
      }
    ],
    "metrics": []
  }
}

With any luck, this saved you a few minutes of headache πŸ™Œ.