azureazure-sql-databaseazure-resource-managerazure-log-analyticsazure-bicep

How to use a bicep template to deploy a SQL Server with auditing to log analytics enabled?


I'm trying to deploy a SQL Server through CI/CD, with auditing to Log Analytics enabled. When looking in azure portal, this can be enabled manually in the Settings<Auditing tab of the SQL server: Exact setting I'm trying to mimic in bicep

Is it possible to deploy the server with auditing to log analytics enabled, or is this something I need to do with an azure powershell/cli task after the deployment succeeds?

I've tried to follow this link but it doesn't enable the auditing on the server, only on the database.

I've also tried to deploy one without, export the template, then enable the auditing to log analytics workspace in the server and export again, then compare the two templates. Unfortunately the difference in the code doesn't help.


Solution

  • In the sample you followed, the auditingSettings resource is missing the auditActionsAndGroups property.

    This should work:

    param location string = resourceGroup().location
    param sqlServerName string = 'sqls-thomas-test-001'
    param logAnalyticsWorkspaceName string = 'log-thomastest-001'
    
    // Get a reference to the existing infra log analytics
    resource logAnalyticsWorkspace 'Microsoft.OperationalInsights/workspaces@2023-09-01' existing = {
      name: logAnalyticsWorkspaceName
    }
    
    // Create the Server
    resource sqlServer 'Microsoft.Sql/servers@2023-08-01-preview' = {
      name: sqlServerName
      location: location
      identity: {
        type: 'SystemAssigned'
      }
      properties: {
        ...
      }
    }
    
    // Enable audit settings
    resource auditingSettings 'Microsoft.Sql/servers/auditingSettings@2023-08-01-preview' = {
      parent: sqlServer
      name: 'default'
      properties: {
        state: 'Enabled'
        isAzureMonitorTargetEnabled: true
        isManagedIdentityInUse: false
        storageAccountSubscriptionId: '00000000-0000-0000-0000-000000000000'
        retentionDays: 0
        auditActionsAndGroups: [
          'SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP'
          'FAILED_DATABASE_AUTHENTICATION_GROUP'
          'BATCH_COMPLETED_GROUP'
        ]
      }
    }
    
    // Get a reference to the master DB
    resource masterDb 'Microsoft.Sql/servers/databases@2023-08-01-preview' existing = {
      name: 'master'
      parent: sqlServer
    }
    
    // Create destination for audit settings
    resource SqlSecurityAuditLogs 'Microsoft.Insights/diagnosticSettings@2021-05-01-preview' = {
      scope: masterDb
      name: 'SQLSecurityAuditLogs'
      properties: {
        workspaceId: logAnalyticsWorkspace.id
        logAnalyticsDestinationType: 'Dedicated'
        logs: [
          {
            category: 'SQLSecurityAuditEvents'
            enabled: true
            retentionPolicy: {
              enabled: false
              days: 0          
            }
          }
        ]
      }
    }