Request a sample Diagnostics setting for enabling auditing for SQL server to send logs to existing Loganalytics workspace(aso v2)

[Blueberrish]

Describe the current behavior

_A sample code is shared where the audit logs are redirected to storage account:https://github.com/Azure/azure-service-operator/blob/main/v2/samples/sql/v1api20211101/v1api20211101_serversauditingsetting.yaml

Can you please share the edited version where audit logs for sql server are redirected to existing loganalytics workspace
I went through the documentation , fields are not clearly explaining which field to use for enabling log. As part of my understanding I have enabled auditing settings with 'Enabled' state and set 'IsAzureMonitorTargetEnabled . Currently am seeing the destination of audit logs is not set.
In page https://learn.microsoft.com/en-us/samples/azure/azure-quickstart-templates/sql-auditing-server-policy-to-oms/ : it is mentioned to configure Diagnostic Settings as well with 'SQLSecurityAuditEvents' diagnostic logs category on the 'master' database (for server level auditing).Please elaborate on this.

Describe the improvement

Please share a sample with combination of auditsetting and diagnosticssettings for enabling audit logs for SQL server and send the destination logs to existing log analytics workspace using aso v2 helm chart.

Additional context

I tried this combination but some information on the master database seemed little unclear when i want to enable currently for sqlserver. I get some errors when category or category groups are mentioned as not existing e.g allLogs as value. Please share a sample aso v2 helm chart for this.
Thanks in advance .

[Blueberrish]

Hi , is it possible to set this with combination of auditsetting and diagnosticssettings for enabling audit logs for SQL server and send the destination logs to existing log analytics workspace using aso v2 helm chart ?

[matthchr]

Unfortunately, the ASO maintainers (myself included) are not experts in every Azure resource, so I don't fully know how to accomplish what you want.

With that said, it does seem like you're on the right track trying to use a BICEP/ARM template sample that does what you want, and translate that into ASO.

That template seems to create a DiagnosticSettings resource with the following settings:

      "properties": {
        "workspaceId": "[resourceId('Microsoft.OperationalInsights/workspaces', parameters('omsWorkspaceName'))]",
        "logs": [
          {
            "category": "SQLSecurityAuditEvents",
            "enabled": true,
            "retentionPolicy": {
              "days": 0,
              "enabled": false
            }
          },
          {
            "category": "DevOpsOperationsAudit",
            "enabled": true,
            "retentionPolicy": {
              "days": 0,
              "enabled": false
            }
          }
        ]
      },

Translating that into ASO, it would look something like this:

apiVersion: insights.azure.com/v1api20210501preview
kind: DiagnosticSetting
metadata:
  name: aso-sample-group
  namespace: default
spec:
  workspaceReference:
    group: operationalinsights.azure.com
    kind: workspace
    name: myworkspace
    # Or specify armId instead
  logs:
    - category: SQLSecurityAuditEvents
      enabled: true
    - category: DevOpsOperationsAudit
      enabled: true
  owner:
    group: Microsoft.Sql
    kind: ServersDatabase
    name: mydatabase

If the above doesn't work, I recommend you raise a request to support (via the Azure portal) or file feedback on the ARM template/BICEP documentation for the example you found (either via GitHub issue or via the feedback button on this page

[Blueberrish]

Hi Matthchr,
Thank you for responding for the request. I have tried the suggested template .I get below error:
Error: UPGRADE FAILED: failed to create resource: admission webhook "validate.v1api20210501preview.diagnosticsettings.insights.azure.com" denied the request: the 'ARMID' field is mutually exclusive with 'Group', 'Kind', 'Namespace', and 'Name' for ResourceReference: Group: "operationalinsights.azure.com", Kind: "workspace", Name: "", ARMID:

I have done one change with reference to your template is the owner is mentioned as Sql server instead of database.
Any input on this ?
Owner template used by me :
owner:
group: sql.azure.com
kind: Server
name: xyz-sql-server

[matthchr]

Can you share the fully formatted yaml you submitted?

[Blueberrish]

Yaml file sample :

apiVersion: insights.azure.com/v1api20210501preview
kind: DiagnosticSetting
metadata:
  name: sample-diag-setting1
  namespace: abc
spec:
  workspaceReference:
    group: operationalinsights.azure.com
    kind: workspace
    armId: "/subscriptions/subscriptionid/resourceGroups/my-rg/providers/Microsoft.OperationalInsights/workspaces/workspaceid"
  logs:
    - category: SQLSecurityAuditEvents
      enabled: true
      retentionPolicy:
        enabled: true
        days: 2
  metrics: []
  owner:
    group: sql.azure.com  
    kind: Server
    name: xyz

[matthchr]

You're not allowed to set group+kind if you also set ARMID, for this one:

  workspaceReference:
    group: operationalinsights.azure.com
    kind: workspace
    armId: "/subscriptions/subscriptionid/resourceGroups/my-rg/providers/Microsoft.OperationalInsights/workspaces/workspaceid"

Either set armId, or set group + kind + name.

[Blueberrish]

Hi @matthchr ,
Thanks for the reply. I have tried by adding only armid in workspaceReference. After the deployment I could see that the Auditing is not enabled. Checked the activity log I could see that create or update resource Diagnostics setting failed with below error:

.

Any thoughts on this?