Azure SQL Server Auditing EventHub Bicep

Question

I am trying to replicate the following settings in Bicep: 1

When I manually add and inspect the ARM for a guide, there is nothing in there other than:

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

This refers only to a storage account, which I haven't set up and the Sub ID of 0000 is what is presented, not my masking.

Reviewing this Microsoft article on the API, there are no properties to set the Event Hub, only a storage account. Is this possible therefore via IaC?

Thanks in advance

Answer 1

To configure eventhub or log analytics, don't specify the storageAccountSubscriptionId property:

param sqlServerName string = ''

// Get a reference to sql server
resource sqlServer 'Microsoft.Sql/servers@2020-11-01-preview' existing =  {
  name: sqlServerName
}

// Enable SQL Server auditing
resource enableSqlAuditing 'Microsoft.Sql/servers/auditingSettings@2021-02-01-preview' = {
  name: 'auditing'
  parent: sqlServer
  properties: {
    isAzureMonitorTargetEnabled: true
    state: 'Enabled'
    auditActionsAndGroups: [
      'SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP'
      'FAILED_DATABASE_AUTHENTICATION_GROUP'
      'BATCH_COMPLETED_GROUP'
    ]
  }
}

Then you can send SQLSecurityAuditEvents using diagnostic settings:

resource masterDb 'Microsoft.Sql/servers/databases@2020-08-01-preview' existing = {
  name: 'master'
  parent: sqlServer
}

// Create diagnostic settings
resource SqlSecurityAuditLogs 'Microsoft.Insights/diagnosticSettings@2017-05-01-preview' = {
  scope: masterDb
  name: 'SQLSecurityAuditLogs'
  properties: {
    eventHubName: '...'
    eventHubAuthorizationRuleId: '...'
    logs: [
      {
        category: 'SQLSecurityAuditEvents'
        enabled: true
        ...
      }
    ]
  }
}