Support for Azure SQL Extended Auditing Policy Action Groups

[iso514]

Is there an existing issue for this?

• [X] I have searched the existing issues

Community Note

• Please vote on this issue by adding a :thumbsup: 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 currently no support to configure azure sql database audit-policy action groups in Terraform. By default, only these action groups are enabled :

• BATCH_COMPLETED_GROUP,
• SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP,
• FAILED_DATABASE_AUTHENTICATION_GROUP Please can this added as it's a pretty standard requirement within enterprises to have higher auditing requirements. This can be enabled both on individual databases and at the server level.

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

azurerm_mssql_server_extended_auditing_policy

Potential Terraform Configuration

resource "azurerm_mssql_server_extended_auditing_policy" "example" {
  actions                                 = FAILED_DATABASE_AUTHENTICATION_GROUP
  server_id                               = azurerm_mssql_server.example.id
  storage_endpoint                        = azurerm_storage_account.example.primary_blob_endpoint
  storage_account_access_key              = azurerm_storage_account.example.primary_access_key
  storage_account_access_key_is_secondary = false
  retention_in_days                       = 6
}

References

It was part of this initial feature request but has not been implemented : https://github.com/hashicorp/terraform-provider-azurerm/issues/5929 Microsoft documentation reference :

• https://docs.microsoft.com/en-us/cli/azure/sql/server/audit-policy?view=azure-cli-latest#az-sql-server-audit-policy-update
• List of existing action groups : https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?msclkid=aa0429fcd12011ecb0b190ebf85f5ce2&view=azuresqldb-current#server-level-audit-action-groups

[joakimlemb]

Example workaround with azapi provider for configuring SQL auditing actions/groups and logging to log analytics:

# No native support for SQL audit actions/groups in azurerm_mssql_server_extended_auditing_policy yet: 
# https://learn.microsoft.com/en-us/azure/templates/microsoft.sql/servers/auditingsettings?pivots=deployment-language-terraform
resource "azapi_update_resource" "example_audit_actions" {
  type      = "Microsoft.Sql/servers/auditingSettings@2022-05-01-preview"
  name      = "default"
  parent_id = azurerm_mssql_server.example.id
  body = jsonencode({
    properties = {
      auditActionsAndGroups = [
        "FAILED_DATABASE_AUTHENTICATION_GROUP",
        "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP"
      ]
      isAzureMonitorTargetEnabled = true
      state                       = "Enabled"
    }
  })

  depends_on = [azurerm_mssql_server.example]
}

# Will most likely fail on first create run as the master database in azurerm_mssql_server is created async and azurerm provider is missing retry/wait logic: 
# https://github.com/hashicorp/terraform-provider-azurerm/issues/22226
resource "azurerm_monitor_diagnostic_setting" "example_audit_log" {
  name                       = "SQLSecurityAuditEvents"
  target_resource_id         = "${azurerm_mssql_server.example.id}/databases/master"
  log_analytics_workspace_id = azurerm_log_analytics_workspace.example.id

  enabled_log { category = "SQLSecurityAuditEvents" }

  lifecycle {
    ignore_changes = [metric]
  }

  depends_on = [azurerm_mssql_server.example]
}

[davidkarlsen]

This is now supported at server level:

https://registry.terraform.io/providers/hashicorp/azurerm/latest/docs/resources/mssql_server_extended_auditing_policy#audit_actions_and_groups

But not at database level:

azurerm_mssql_database_extended_auditing_policy