Bug Report - SQL Auditing Policy assignment does not have permissions to create Diagnostic Settings for management Log Analytics workspace

[amayfoxen]

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.9.6

azure provider: 3.116

module: 6.1.0

Description

Describe the bug

The Configure SQL servers to have auditing enabled to Log Analytics workspace Policy is assigned at the Landing Zones management group, and when it tries to configure Diagnostic Settings to send logs to the Log Analytics workspace in the management subscription it fails:

The client 'xxx' with object id 'xxx' has permission to perform action 'microsoft.insights/diagnosticSettings/write' on scope '/subscriptions/xxx/resourcegroups/rg-storage-dev/providers/Microsoft.Sql/servers/xxx/databases/master/providers/microsoft.insights/diagnosticSettings/SQLSecurityAuditEvents_3d229c42-c7e7-4c97-9a99-ec0d0d8b86c1'; however, it does not have permission to perform action(s) 'Microsoft.OperationalInsights/workspaces/sharedKeys/action' on the linked scope(s) '/subscriptions/xxx/resourcegroups/xxx-mgmt/providers/microsoft.operationalinsights/workspaces/xxx-la' (respectively) or the linked scope(s) are invalid.

The deployment does partially succeed, and updates the auditingSettings of the SQL Server to enable SQL Auditing, but without the diagnostic settings the logs don't go anywhere.

Because the policy only checks the auditingSettings for compliance, it looks like all the SQL Servers are compliant.

Steps to Reproduce

1. Deploy CAF module with deploy_management_resources = true
2. Create a SQL Server in a subscription under the Landing Zones management group
3. Wait for Policy to apply, check audit settings of SQL Server.

Screenshots

View of the SQL Auditing settings in the Portal after the policy partially applies

Additional context

Originally deployed with 5.x version of CAF module, which is when the SQL Servers were created.

[falowomi]

@amayfoxen If you have create your landing zone using either 5.x or 6.x versions, then you should have a new policy assignment created on the Landing zone MG scope called "Deploy-AzSqlDb-Auditing".

The mentioned policy would create a diagnostic setting with SQLSecurityAuditEvents enabled to Log analytics and then enable the SQL server auditing. You might not see the Log Analytics button checked under Azure SQL Auditing but really diagnostics settings being enabled takes care of this.,

Please close this issue if this helps.

[amayfoxen]

@falowomi Deploy-AzSqlDb-Auditing is Configure SQL servers to have auditing enabled to Log Analytics workspace (see policy_assignment_es_deploy_azsqldb_auditing.tmpl.json) and is the one that is failing to create the Diagnostic Settings because of the scope of the role assignment.

I've gone ahead and implemented these settings directly in Terraform directly rather than relying on the Policy, which has the benefit that I'm also configuring the settings for Microsoft support, which seem like something that CAF should do as well.

[falowomi]

@amayfoxen, you're totally right. Because the Deploy-AzSqlDb-Auditing policy has a dependency on the log analytics workspace but the system identity used in the policy assignment doesn't have authorization on the oms scope, this fails. Yes, I believe your workaround is good.

Thanks for pointing this out. I also believe that this would also affect all the diagnostics settings policies that will apply on other MG outside of the Management MG.

[amayfoxen]

A lot of the other diagnostic setting policies are assigned at a higher level, so they have access to the management subscription and the landing zones. I wonder if this could just be shifted to that level.