CODEWITHSUNDEEP
azureazure-sql-databaseazure-resource-managerazure-bicep
Raymondo
Raymondo

Reputation: 587

Azure SQL Server Auditing EventHub Bicep

I am trying to replicate the following settings in Bicep: enter image description here1

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

Upvotes: 0

Views: 576

Answers (1)

Thomas
Thomas

Reputation: 29736

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
        ...
      }
    ]
  }
}

Upvotes: 1

Related Questions