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

Question

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,556 Reputation points Microsoft Employee Moderator

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.

Answer accepted by question author

0 additional answers

Your answer

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,556 Reputation points Microsoft Employee Moderator

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.

Answer 1

ShaikMaheer-MSFT 38,556 Microsoft Employee Moderator

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.

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,556 Reputation points Microsoft Employee Moderator

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,556 Reputation points Microsoft Employee Moderator

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.

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