Support for enable ms-support auditing

[MichalSino]

Is there an existing issue for this?

• I have searched the existing issues

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

Description

There is not possibility to set "Enable Auditing of Microsoft support operations" and "Use different audit log destinations" through azurerm provider. Is it possible to add this feature to azurerm_mssql_server_extended_auditing_policy or create new resource like above? After creating resource we could set destination in diagnostic_settings (like with extended auditing policy).

New or Affected Resource(s)/Data Source(s)

azurerm_mssql_server_mssupport_auditing_policy

Potential Terraform Configuration

resource "azurerm_mssql_server_mssupport_auditing_policy" "example" {
  server_id                       = azurerm_mssql_server.example.id
  enabled                         = true
  log_monitoring_enabled          = true
  storage_endpoint                = azurerm_storage_account.example.primary_blob_endpoint
  storage_account_subscription_id = azurerm_subscription.primary.subscription_id
  retention_in_days               = 6
}

resource "azurerm_monitor_diagnostic_setting" "example" {
  name                       = "example"
  target_resource_id         = "${azurerm_mssql_server.example.id}/databases/master"
  log_analytics_workspace_id = data.azurerm_log_analytics_workspace.example.id

  log {
    category = "DevOpsAuditing"
    enabled  = true

    retention_policy {
      enabled = false
    }
  }
}

References

No response

[aristosvo]

I'll take a quick look!

[MichalSino]

Thanks. I forgot to write that it is relates to Azure mssql server :)

[aristosvo]

@MichalSino If you know how to build the provider yourselves locally, feel free to check out the PR and get a feel if this is working for you!

The reason I'm proposing this is because I've the experience that the configuration of auditing on SQL might be a bit more complicated than it seems at first sight.

[MichalSino]

@aristosvo Thanks for quick reply. Unfortunately I've never written provider, so I don't know how to do it.

I've read this pull request and I think it is not full solution, because in cli we can use those 2 commands (and they do 2 different things, but similar):

This is what you mentioned:
az sql server audit-policy update -g ${data.azurerm_resource_group.rg.name} -n ${azurerm_mssql_server.server.name} --set isDevopsAuditEnabled=true --lats Enabled --lawr ${azurerm_log_analytics_workspace.law.id}

And this is what I found in docs:
az sql server ms-support audit-policy update --ids ${azurerm_mssql_server.server..id} --lats Enabled --lawri ${azurerm_log_analytics_workspace.law.id} --state Enabled

Someone from MS should write which one is correct. ;)

I can't find the difference in API, but second one works better in my case.

But thanks for reply. I will wait for this. :)

[aristosvo]

@MichalSino Thanks for your pointers! Enhanced the resource to a separate one, which indeed uses different APIs and works better.

I'll run some tests and add some docs, let's get it going!

[MichalSino]

@aristosvo great. I will wait for good news. :)

Thanks a lot.

[fcatacut]

And this is what I found in docs: az sql server ms-support audit-policy update --ids azurermmssqlserver.server..id−−latsEnabled−−lawri{azurerm_log_analytics_workspace.law.id} --state Enabled

Regarding az sql server ms-support audit-policy update,

Auditing of Microsoft Support operations for your logical server allows you to audit Microsoft support engineers' operations when they need to access your server during a support request. The use of this capability, along with your auditing, enables more transparency into your workforce and allows for anomaly detection, trend visualization, and data loss prevention.

Source: Auditing of Microsoft Support operations (September 2022)

[MichalSino]

I know what does it mean. :)

The question is which method is better to set up it and why. ;)

[fcatacut]

In the image below, az sql server audit-policy update configures the "Azure SQL Auditing" section while az sql server ms-support audit-policy update configures the "Auditing of Microsoft support operations" section, which is the one that you want based on your original description.

[MichalSino]

As I said earlier I know it. :)

But we can also set it up using

az sql server audit-policy update -g ${data.azurerm_resource_group.rg.name} -n ${azurerm_mssql_server.server.name} --set isDevopsAuditEnabled=true --lats Enabled --lawr ${azurerm_log_analytics_workspace.law.id}

[fcatacut]

Looking at the az sql server audit-policy update documentation and unless I'm missing something, none of its parameters will configure the destination for MS support operation when "using different audit log destinations" is checked. This is what you're asking for, right? If so, the only way to configure these would be with az sql server ms-support audit-policy update.

[MichalSino]

So, you suggest to use first one if we want to enable Ms support auditing and second one if we want to change the destination of those logs, right?

[fcatacut]

To enable SQL auditing, you have to run az sql server audit-policy update --state Enabled specifying the audit logs destination.

If you want to enable the auditing of Microsoft support operations, I think that you have to run the following: az sql server ms-support audit-policy update -g mygroup -n myserver --state Enabled. If you want to send the audit logs to a different destination, you'd have to specify additional parameters.

[MichalSino]

But what is isDevOpsauditEnabled=true for in
az sql server audit-policy update -g ${data.azurerm_resource_group.rg.name} -n ${azurerm_mssql_server.server.name} --set isDevopsAuditEnabled=true --lats Enabled --lawr

[fcatacut]

Azure CLI parameters are formatted in lower case characters and words are delimited by dashes whereas the code snippet that you provided is in camel case. What is the source of your snippet?

Note: There is a "Devops operations Audit Logs" log category for Azure SQL Databases.

[fcatacut]

There is a IsDevopsAuditEnabled property, but it's for the Azure SDK for .NET: https://learn.microsoft.com/en-us/dotnet/api/microsoft.azure.management.sql.models.serverblobauditingpolicy.isdevopsauditenabled.

[MichalSino]

Azure CLI parameters are formatted in lower case characters and words are delimited by dashes whereas the code snippet that you provided is in camel case. What is the source of your snippet?

Note: There is a "Devops operations Audit Logs" log category for Azure SQL Databases.

When you do "az sql server audit-policy update --ids sql-server-resource-id" there is property "isDevopsAuditEnabled": false and I changed it to true to set this

But I understand that this should not be used to set ms-support auditing.

[github-actions]

This functionality has been released in v3.30.0 of the Terraform Provider. Please see the Terraform documentation on provider versioning or reach out if you need any assistance upgrading.

For further feature requests or bug reports with this functionality, please create a new GitHub issue following the template. Thank you!

[github-actions]

I'm going to lock this issue because it has been closed for 30 days ⏳. This helps our maintainers find and focus on the active issues.
If you have found a problem that seems similar to this, please open a new issue and complete the issue template so we can capture all the details necessary to investigate further.