Bug Report - Conflicting policies on "master" databases

[AndersAsp]

Describe the bug
Tried searching for other reports on the issues but was unable to find anything that matches my case.

The policy "Enable logging by category group for SQL databases (microsoft.sql/servers/databases) to Log Analytics" in the initiative "Enable allLogs category group resource logging for supported resources to Log Analytics" is conflicting with the policy "Configure SQL servers to have auditing enabled to Log Analytics workspace"

The first policy "Enable logging by category group for SQL databases (microsoft.sql/servers/databases) to Log Analytics" is configuring the Diagnostic Settings on all databases to send the logs to a central Log Analytics workspace using the name "setbypolicy", this includes any "master" databases. The second policy, "Configure SQL servers to have auditing enabled to Log Analytics workspace", enables SQL Server Auditing which also sends logs to a central Log Analytics workspace, and also applies this as a Diagnostic Settings on the "master" database. This time with a name "SQLSecurityAuditEvents_".

When both policies are assigned as recommended in ALZ, you can't have both policies compliant, as you can't have two settings sending data to the same Log Analytics workspace. When trying to run remediation on either policies, you will get an error like this (or the other way around, depending on which policy which executed first):

Data sink '/subscriptions/42ebad80-2040-43f8-9c2f-cf6de2c131d6/resourcegroups/mgmt-logs-shared-rg/providers/microsoft.operationalinsights/workspaces/mgmt-platform-weeu-s-log' is already used in diagnostic setting 'SQLSecurityAuditEvents_3d229c42-c7e7-4c97-9a99-ec0d0d8b86c1' for category 'SQLSecurityAuditEvents'. Data sinks can't be reused in different settings on the same category for the same resource.

Steps to reproduce

1. Ensure that the policies "Enable logging by category group for SQL databases (microsoft.sql/servers/databases) to Log Analytics" and "Configure SQL servers to have auditing enabled to Log Analytics workspace" is assigned to the same scope (in ALZ the first one is assigned to root, and the second one is assigned to Landing Zones). Ensure they're both configured to send the data to the same Log Analytics workspace.
2. Create a SQL Server resource
3. Check compliance of the both policies above
4. Try to run remediation on the one being non-compliant.

Screenshots

[Springstone]

@AndersAsp thank you for reporting the issue. I'll investigate further, but suspect you've configuring the first initiative to only send Audit logs (not the default AllLogs), in which case this would cause the conflict, and it would make sense to remove the second policy assignment (since it is duplicating what the initiative is logging).

[Springstone]

@AndersAsp I've reviewed the issue and confirm the conflict/issue exactly as you've described it. The policy "Configure SQL servers to have auditing enabled to Log Analytics workspace" configures the Azure SQL Auditing feature at the server level, which would apply to the master database. The "Enable logging by category group for SQL databases (microsoft.sql/servers/databases) to Log Analytics" policy configures the Diagnostic Settings feature of newly created databases. I feel that this policy should not include master, as there is no way to configure Diagnostic Settings on master using standard tools.

I will follow up with PG on how to address this. Currently, from an ALZ perspective you should be covered from a logging perspective, and ignoring that master can't be configured using "Enable logging by category group for SQL databases (microsoft.sql/servers/databases) to Log Analytics".

[AndersAsp]

@Springstone Thank you for the update! Your suggested solution sounds very good and feasible.