Problems with Azure SQL Auditing using Log Analytics on a server level

[hmbrennecke]

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 comments along the lines of "+1", "me too" or "any updates", 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 and review the contribution guide to help.

Terraform Version

1.5.2

AzureRM Provider Version

3.92.0

Affected Resource(s)/Data Source(s)

azurerm_mssql_server_extended_auditing_policy, azurerm_mssql_database_extended_auditing_policy, azurerm_monitor_diagnostic_setting

Terraform Configuration Files

terraform {
  required_providers {
    azurerm = {
      source  = "hashicorp/azurerm"
      version = ">=3.41.0"
    }
  }
}

resource "azurerm_mssql_server_extended_auditing_policy" "main" {
  server_id              = var.server_id
  retention_in_days      = 30
  log_monitoring_enabled = true
}


resource "azurerm_monitor_diagnostic_setting" "mssql_audit" {
  name                       = "mssql-audit-to-log-analytics"
  target_resource_id         = var.server_id
  log_analytics_workspace_id = var.log_analytics_workspace_id

  metric {
    category = "AllMetrics"
  }

  depends_on = [
    azurerm_mssql_server_extended_auditing_policy.main
  ]
}

Debug Output/Panic Output

-

Expected Behaviour

The azurerm resource "azurerm_mssql_server_extended_auditing_policy" should enable the Azure SQL Auditing on Azure Portal and the resource "azurerm_monitor_diagnostic_setting" should set Log Analytics as the audit log destination.

I found an issue related to this, but it was at the database level, which in this case using the resources:

resource "azurerm_mssql_database_extended_auditing_policy" "main" {
  database_id            = var.database_id
  retention_in_days      = 30
  log_monitoring_enabled = true
}

resource "azurerm_monitor_diagnostic_setting" "mssql_audit" {
  name                       = "mssql-audit-to-log-analytics"
  target_resource_id         = var.database_id
  log_analytics_workspace_id = var.log_analytics_workspace_id

  enabled_log {
    category = "SQLSecurityAuditEvents"
  }

  metric {
    category = "AllMetrics"
  }

  depends_on = [
    azurerm_mssql_database_extended_auditing_policy.main
  ]
}

It was possible to enable and set the analytic log destination to Log Analytics in the database:

But if I set this to the server level:

resource "azurerm_mssql_server_extended_auditing_policy" "main" {
  server_id              = var.server_id
  retention_in_days      = 30
  log_monitoring_enabled = true
}

resource "azurerm_monitor_diagnostic_setting" "mssql_audit" {
  name                       = "mssql-audit-to-log-analytics"
  target_resource_id         = var.server_id
  log_analytics_workspace_id = var.log_analytics_workspace_id

  metric {
    category = "AllMetrics"
  }

  depends_on = [
    azurerm_mssql_server_extended_auditing_policy.main
  ]
}

It only enables Azure SQL Audit and does not set the Log Analystics as the destination:

Actual Behaviour

Azure SQL Audit was indeed enabled, but without an audit log destination.

Steps to Reproduce

No response

Important Factoids

No response

References

No response

[magodo]

By looking at the Portal, the request for the sql server level is targeting to: /subscriptions/xxxx/resourceGroups/test-rg/providers/Microsoft.Sql/servers/acctest-sqlserver-344/auditingSettings/default?api-version=2021-11-01-preview, which is defined in https://github.com/Azure/azure-rest-api-specs/blob/c1d839d48ee936c9338431c38f2cbbfbc9879ea2/specification/sql/resource-manager/Microsoft.Sql/preview/2022-11-01-preview/BlobAuditing.json#L61. (Note that this endpoint only has GET and PUT).

This isn't currently supported by the provider yet.

On the other hand, those database level audit settings are implemented by the extension resource of insight RP.

[hmbrennecke]

@magodo thank you very much for your reply. So for now I'm going to work at the database level audit, but is there a change that we can do this at the server level in the future?

[babuga365]

It would be good to have options to enable log analytics option on resource: azurerm_mssql_server_extended_auditing_policy, similar like storage account and no need to create diagnostics settings seperately.

Reason: As per this document: https://learn.microsoft.com/en-us/azure/azure-sql/database/auditing-server-level-database-level?view=azuresql, enabling auditing on SQL server level is enough and it will automatically monitor all the available SQL Databases on that server.

• A server policy applies to all existing and newly created databases on the server.
• If server auditing is enabled, it always applies to the database. The database is audited regardless of the database auditing settings.
• Enabling auditing on the database in addition to enabling auditing on the server doesn't override or change any of the settings of the server auditing. Both audits exist side by side. In other words, the database is audited twice in parallel; once by the server policy and once by the database policy.

It would be good to have log analytics option on this resource as well; azurerm_mssql_database_extended_auditing_policy

Both the resources has only Storage Account integration options and not having Log Analytics and EventHub options through terraform. If we have these options on the resource: azurerm_mssql_server_extended_auditing_policy, azurerm_mssql_database_extended_auditing_policy, it will simplify the terraform setup and easily understandable.

[frostsxx]

@magodo thank you very much for your reply. So for now I'm going to work at the database level audit, but is there a change that we can do this at the server level in the future?

Hi @hmbrennecke! Have you found any solution to this problem? I'm currently trying the same thing.

[EduardGurman]

Hey, Same problem here, the Log analytics for the server is not configured although the database is.

[adrianstrat]

Looks like you also need to create an associated azurerm_mssql_database_extended_auditing_policy resource for the master database. I had a working solution following this example: https://github.com/hashicorp/terraform-provider-azurerm/blob/main/examples/sql-azure/sql_server_auditing_log_analytics/main.tf

[frostsxx]

This is my current code and it got my issue solved.

SQL code:

resource "azurerm_mssql_server" "createSQLServer" {
  name                         = lower("${module.createRG-SQL.prefixRG}-${module.createRG-SQL.acronymResourceLocation}-${module.createRG-SQL.tagEnvironment}-${module.createRG-SQL.tagProject}-sql")
  resource_group_name          = module.createRG-SQL.nameRG
  location                     = module.createRG-SQL.locationRG
  version                      = "12.0"
  administrator_login          = var.adminLogin
  administrator_login_password = var.adminLoginPassword

  depends_on = [module.createRG-SQL]
}

resource "azurerm_mssql_database" "createSQLDatabase" {
  name                 = lower("${module.createRG-SQL.prefixRG}-${module.createRG-SQL.acronymResourceLocation}-${module.createRG-SQL.tagEnvironment}-${module.createRG-SQL.tagProject}-db")
  server_id            = azurerm_mssql_server.createSQLServer.id
  collation            = "SQL_Latin1_General_CP1_CI_AS"
  license_type         = "LicenseIncluded"
  max_size_gb          = 2
  sku_name             = "Basic"
  enclave_type         = "VBS"
  storage_account_type = "Zone" # Backups Storage redundancy

  depends_on = [azurerm_mssql_server.createSQLServer]

  # prevent the possibility of accidental data loss
  /*
  lifecycle {
    prevent_destroy = true
  }
  */
}

resource "azurerm_mssql_database_extended_auditing_policy" "auditingPolicyDatabase" {
  database_id            = azurerm_mssql_database.createSQLDatabase.id
  retention_in_days      = 30
  log_monitoring_enabled = true

  depends_on = [azurerm_mssql_database.createSQLDatabase]
}

resource "azurerm_monitor_diagnostic_setting" "createSQLAudit" {
  name                       = "mssql-audit-to-log-analytics"
  target_resource_id         = azurerm_mssql_database.createSQLDatabase.id
  log_analytics_workspace_id = azurerm_log_analytics_workspace.createLogAnalytics.id

  enabled_log {
    category = "SQLSecurityAuditEvents"
  }

  metric {
    category = "AllMetrics"
  }

  depends_on = [azurerm_mssql_database_extended_auditing_policy.auditingPolicyDatabase]
}

Auditing / Log analytics code:

# Create a Log Analytics Workspace
resource "azurerm_log_analytics_workspace" "createLogAnalytics" {
  name                = "${var.prefixRG}-${var.resourceLocation}-${var.tagEnvironment}-${var.nameLogAnalytics}-log"
  resource_group_name = azurerm_resource_group.createRG.name
  location            = azurerm_resource_group.createRG.location
  sku                 = "PerGB2018" # Pricing tier, you can adjust as needed
  retention_in_days   = 30          # Retention period for logs

  depends_on = [azurerm_resource_group.createRG]
}