Built-in Policy "Deploy - Configure diagnostic settings for SQL Databases to Log Analytics workspace" is not working for "Dedicated SQL pool" resources

Vik 45

Built-in policy "Deploy - Configure diagnostic settings for SQL Databases to Log Analytics workspace" applies to all the resources of type "Microsoft.Sql/servers/databases".

Although it has been working fine for the actual SQL databases as provided below

User's image

but keep failing for databases "Dedicated SQL pool" which also shares the same type as Microsoft.Sql/servers/databases but has a completely different log categories. User's image

The error I am getting with Remediation is "Category 'SQLInsights' is not supported. (Code: BadRequest)". Which makes sense as it's actually a different a resource but sharing the same resource type "Microsoft.Sql/servers/databases" 🙂

How can I resolve this compliance issue?

Vik 45 Reputation points

2023-08-25T00:26:44.7133333+00:00

I just found that the Log categories for the Dedicated SQL pool (formerly SQL DW) match exactly with the resource type "Microsoft.Synapse/workspaces/sqlPools"

https://learn.microsoft.com/en-us/azure/azure-monitor/reference/supported-logs/microsoft-synapse-workspaces-sqlpools-logs

Looks like an implementation gap by the Azure team while moving SQL DW to the Dedicated SQL Pool.

In my opinion, either "Dedicated SQL pool" needs to have the same log categories as "Microsoft.Sql/servers/databases" or should have a different resource type may be "Microsoft.Sql/servers/sqlpools"
ShaikMaheer-MSFT 38,456 Reputation points Microsoft Employee

2023-09-07T17:45:43.8+00:00

Hi Vik, Just checking if below answer helps. If yes, please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries. Thank you.

Accepted answer

ShaikMaheer-MSFT 38,456 Reputation points Microsoft Employee

2023-08-28T15:39:30.9566667+00:00

Hi Vik,

Thank you for posting query in Microsoft Q&A Platform.

Great analysis. Appreciating your efforts. Please note, Dedicated SQL Pools are actual Data warehouse systems. Azure SQL DB's are not Data warehouse Systems. By design there are differences between Databases and Data warehouses. Hence you see different logs and they mapped to different resource types.

Thank you for sharing your findings. This helps community as well.

Hope this helps. Please let me know if any further queries.

Please consider hitting Accept Answer button. Accepted answers help community as well.
Please sign in to rate this answer.
Vikas Vaidya 0 Reputation points

2023-08-28T23:22:06.97+00:00

Hi @ShaikMaheer-MSFT

Thanks for your comment.

As both of these different resources are now sharing the common resource type "Microsoft.Sql/servers/databases" while still having different log categories it is resulting in Policy Compliance issue.

I am seeking if any resolution can be provided by the relevant team(s).

ShaikMaheer-MSFT 38,456 Reputation points Microsoft Employee

2023-08-30T10:43:21.5166667+00:00

Hi Vik,

I would suggest for making policy Complient, while assigning policy leverage Exclusions field to exclude dedicated SQL resources. That way, your policy will get complaint.

Please note, between I would like you to submit feedback item for this using below link.

https://feedback.azure.com/d365community/forum/79b1327d-d925-ec11-b6e6-000d3a4f06a4

Hope this helps. Please let me know if any further queries.

Please consider hitting Accept Answer button. Accepted answers help community as well.

ShaikMaheer-MSFT 38,456 Reputation points Microsoft Employee

2023-09-01T10:20:43.25+00:00

Hi Vik, Just checking if above answer helps. If yes, please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries. Thank you.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Built-in Policy "Deploy - Configure diagnostic settings for SQL Databases to Log Analytics workspace" is not working for "Dedicated SQL pool" resources

0 additional answers

Your answer