[AVM Module Issue]: avm/res/sql/server should allow setting Microsoft.Insights/diagnosticSettings for master database

[jikuja]

Check for previous/existing GitHub issues

• I have checked for previous/existing GitHub issues

Issue Type?

Feature Request

Module Name

avm/res/sql/server

(Optional) Module Version

No response

Description

Microsoft recommends enabling auditing only on server level:

You should avoid enabling both server auditing and database blob auditing together, unless:

You want to use a different storage account, retention period or Log Analytics Workspace for a specific database.
You want to audit event types or categories for a specific database that differ from the rest of the databases on the server. For example, you might have table inserts that need to be audited only for a specific database.
Otherwise, we recommended that you enable only server-level auditing and leave the database-level auditing disabled for all databases.

---

To enable log analytics workspace logging three configs are required:

• server level-audit setting. Already supported on server-level by the AVM module. (But not on database level!)
• SQLSecurityAuditEvents category logging
• optionally DevOpsOperationsAudit category logging

SQLSecurityAuditEvents

{
  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/RgName//providers/Microsoft.Sql/servers/server-server/databases/master/providers/microsoft.insights/diagnosticSettings/SQLSecurityAuditEvents_3d229c42-c7e7-4c97-9a99-ec0d0d8b86c1",
  "name": "SQLSecurityAuditEvents_3d229c42-c7e7-4c97-9a99-ec0d0d8b86c1",
  "properties": {
    "logs": [
      {
        "category": "SQLSecurityAuditEvents",
        "enabled": true,
        "retentionPolicy": {
          "days": 0,
          "enabled": false
        }
      }
    ],
    "metrics": [],
    "workspaceId": "/subscriptions/00000000-0000-0000-0000-000000000000/resourcegroups/RgName/providers/microsoft.operationalinsights/workspaces/law-law"
  }
}

DevOpsOperationsAudit for "devops"

{
  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/RgName/providers/microsoft.insights/diagnosticSettings/SQLSecurityAuditEvents_3d229c42-c7e7-4c97-9a99-ec0d0d8b86c1_0",
  "name": "SQLSecurityAuditEvents_3d229c42-c7e7-4c97-9a99-ec0d0d8b86c1_0",
  "properties": {
    "logs": [
      {
        "category": "DevOpsOperationsAudit",
        "enabled": true,
        "retentionPolicy": {
          "days": 0,
          "enabled": false
        }
      }
    ],
    "metrics": [],
    "workspaceId": "/subscriptions/00000000-0000-0000-0000-000000000000/resourcegroups/RgName/providers/microsoft.operationalinsights/workspaces/law-law"
  }
}

---

Current module does not really support master database diagnosticssettings management: At least I could find any way to create working master entry on databases array. Most of the tries failed because database already exists and is teechnical database.

Quickstart template as a reference: https://github.com/Azure/azure-quickstart-templates/blob/master/quickstarts/microsoft.sql/sql-auditing-server-policy-to-oms/main.bicep#L63

This might be hard to implement because master database is automatically created after logical server resource creation.

I implemented this with following, just to make things work:

resource masterDb 'Microsoft.Sql/servers/databases@2023-08-01-preview' existing = {
  name: '${ServerName}/master'
}

resource masterAuditLogs 'Microsoft.Insights/diagnosticSettings@2021-05-01-preview' = {
  name: 'SQLSecurityAuditEvents_3d229c42-c7e7-4c97-9a99-ec0d0d8b86c1'
  scope: masterDb
  properties: {
    workspaceId: law.outputs.resourceId
    logs: [
      {
        category: 'DevOpsOperationsAudit'
        enabled: true
      }
      {
        category: 'SQLSecurityAuditEvents'
        enabled: true
      }
    ]
  }
  dependsOn: [
    server
  ]
}

module server 'br/public:avm/res/sql/server:0.8.0' = {
// ...
}

(Optional) Correlation Id

No response

[microsoft-github-policy-service]

Important

The "Needs: Triage 🔍" label must be removed once the triage process is complete!

Tip

For additional guidance on how to triage this issue/PR, see the BRM Issue Triage documentation.

[avm-team-linter]

@jikuja, thanks for submitting this issue for the avm/res/sql/server module!

Important

A member of the @Azure/avm-res-sql-server-module-owners-bicep or @Azure/avm-res-sql-server-module-contributors-bicep team will review it soon!

[peterbud]

Hi @jikuja , the current AVM module supports only server level auditing.

Please note that there are two different APIs:

• For server auditing (supported): https://learn.microsoft.com/en-us/azure/templates/microsoft.sql/servers/auditingsettings?pivots=deployment-language-bicep
• For database auditing: https://learn.microsoft.com/en-us/azure/templates/microsoft.sql/servers/databases/auditingsettings?pivots=deployment-language-bicep

This database level auditing is not (yet) supported by AVM. PRs are always welcome!

[jikuja]

the current AVM module supports only server level auditing.

Only to storage accounts. Log analytics workspace or event hub is not supported because it is impossible to configure diagnosticssettings for master database.