Redundant/conflicting policy assignments at different scopes, relating to SQL database

[eehret]

Community Note

• Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request
• Please do not leave "+1" or "me too" comments, they generate extra noise for issue followers and do not help prioritize the request
• If you are interested in working on this issue or have submitted a pull request, please leave a comment

Versions

terraform: 1.8.5

azure provider: 3.109.0

module: 5.2.1

Description

Describe the bug

There are two different policies assigned at different scopes that appear to be conflicting and resulting in errors in the deployment/activity logs.

They are:

1. The 'Deploy - Configure diagnostic settings for SQL Databases to Log Analytics workspace' policy definition, which is included in the 'deploy-resource-diag' assignment done at landing zone root management group.
2. The 'Configure SQL servers to have auditing enabled to Log Analytics workspace' policy definition, which is assigned directly in the 'deploy-azsqldb-auditing' assignment done at the 'landing-zones' management group.

Both of these policies attempt to write some diagnostic settings under the 'SQLSecurityAuditEvents' category and then we get an error like this when the second deployment fails (not sure if the order is deterministic or not, I haven't looked into it that far):

Data sink '/subscriptions/<redacted>/resourceGroups/lzroot-mgmt/providers/Microsoft.OperationalInsights/workspaces/lzroot-la' is already used in diagnostic setting 'setByPolicy-LogAnalytics' for category 'SQLSecurityAuditEvents'. Data sinks can't be reused in different settings on the same category for the same resource. (Code: Conflict)

Steps to Reproduce

1. Deploy an instance of Azure SQL database in a scope underneath 'landing-zones' management group
2. Wait some time
3. Look at the activity logs and deployment logs on the target resource group and observe deployment errors

Screenshots

n/a

Additional context

We've used CAF module 5.2.1 with default settings as much as possible. The configuration for these policy assignments hasn't been modified.

[matt-FFFFFF]

@Springstone are you able to comment as to whether deploy-resource-diag and deploy-azsqldb-auditing would result in this issue with duplicate data sinks?