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