Enable both Azure SQL Auditing and All Logs in Azure SQL database

[fjalcarazp]

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.

I need to enable Azure SQL Auditing on both the Azure SQL Server and the Azure SQL Database, and send the logs to an Event Hub. Additionally, I want to configure the diagnostic settings to send all logs and metrics to the Event Hub.

When I apply this Terraform code, the SQL audit logs are enabled, but the Event Hub is not configured in the Azure portal:

resource "azurerm_mssql_database_extended_auditing_policy" "contactshubdb_audit_policy" {
  database_id            = azurerm_mssql_database.contactshubdb.id
  log_monitoring_enabled = true
}

resource "azurerm_monitor_diagnostic_setting" "contactshubdb_sql_auditing" {
  name                           = "datadog"
  target_resource_id             = azurerm_mssql_database.contactshubdb.id
  eventhub_name                  = data.azurerm_eventhub.evh_datadog.name
  eventhub_authorization_rule_id = data.azurerm_eventhub_namespace_authorization_rule.evhns_datadog_authorization_rule.id

  enabled_log {
    # category = "SQLSecurityAuditEvents"
    category_group = "AllLogs"
  }

  metric {
    category = "AllMetrics"
  }

  # lifecycle {
  #   ignore_changes = [enabled_log, metric]
  # }
}

If I apply this Terraform code, the SQL audit logs are enabled properly, but the diagnostic settings do not have all logs enabled:

resource "azurerm_mssql_database_extended_auditing_policy" "contactshubdb_audit_policy" {
  database_id            = azurerm_mssql_database.contactshubdb.id
  log_monitoring_enabled = true
}

resource "azurerm_monitor_diagnostic_setting" "contactshubdb_sql_auditing" {
  name                           = "datadog"
  target_resource_id             = azurerm_mssql_database.contactshubdb.id
  eventhub_name                  = data.azurerm_eventhub.evh_datadog.name
  eventhub_authorization_rule_id = data.azurerm_eventhub_namespace_authorization_rule.evhns_datadog_authorization_rule.id

  enabled_log {
    category = "SQLSecurityAuditEvents"
    # category_group = "AllLogs"
  }

  metric {
    category = "AllMetrics"
  }

  # lifecycle {
  #   ignore_changes = [enabled_log, metric]
  # }
}

Terraform Version

1.9.0

AzureRM Provider Version

3.107.0

Affected Resource(s)/Data Source(s)

azurerm_monitor_diagnostic_setting; azurerm_mssql_database;

Terraform Configuration Files

resource "azurerm_mssql_server" "mssqlserver" {
  name                          = lower("${local.resource_prefix}-SQL01")
  location                      = local.env_config[var.env_name].region
  resource_group_name           = module.rg.rg_name
  version                       = "12.0"
  administrator_login           = "sqladmin"
  administrator_login_password  = random_password.mssqlserver_admin_password.result
  public_network_access_enabled = false
  minimum_tls_version           = "1.2"
  tags                          = local.tags

  azuread_administrator {
    azuread_authentication_only = false
    login_username              = local.env_config[var.env_name].dbadmin_config.login_username
    object_id                   = local.env_config[var.env_name].dbadmin_config.object_id
    tenant_id                   = local.env_config[var.env_name].dbadmin_config.tenant_id
  }
}

// 3. Create private endpoint for MSSQL Server
resource "azurerm_private_endpoint" "sql01_privateendpoint" {
  name                = "${local.resource_prefix}-PLINK01-DB"
  location            = local.env_config[var.env_name].region
  resource_group_name = module.rg.rg_name
  subnet_id           = module.spoke-web.subnet_spoke_private_id
  tags                = local.tags

  private_service_connection {
    name                           = "${local.resource_prefix}-PLINKCONNECTION01"
    private_connection_resource_id = azurerm_mssql_server.mssqlserver.id
    subresource_names              = ["sqlServer"]
    is_manual_connection           = false
  }

  # This should be enabled if your service connection has permissions to do that, otherwise manual action is required
  private_dns_zone_group {
    name                 = "default"
    private_dns_zone_ids = [data.azurerm_private_dns_zone.dnsrsv_pdz_database.id]
  }

  lifecycle {
    ignore_changes = [
      private_dns_zone_group
    ]
  }
}

// 2. Create Database in MSSQL Server
resource "azurerm_mssql_database" "contactshubdb" {
  name         = lower("${local.resource_prefix}-DB01")
  server_id    = azurerm_mssql_server.mssqlserver.id
  collation    = "SQL_Latin1_General_CP1_CI_AS"
  license_type = "LicenseIncluded"
  max_size_gb  = 2
  sku_name     = "S0"
  tags         = local.tags

  depends_on = [
    azurerm_mssql_server.mssqlserver,
  ]
}

resource "azurerm_mssql_server_extended_auditing_policy" "mssqlserver_audit_policy" {
  server_id              = azurerm_mssql_server.mssqlserver.id
  log_monitoring_enabled = true
}

resource "azurerm_monitor_diagnostic_setting" "mssql_diagnotic_setting" {
  # If the first deployment fails, run it again.
  name                           = "datadog"
  target_resource_id             = "${azurerm_mssql_server.mssqlserver.id}/databases/master"
  eventhub_authorization_rule_id = data.azurerm_eventhub_namespace_authorization_rule.evhns_datadog_authorization_rule.id
  eventhub_name                  = data.azurerm_eventhub.evh_datadog.name

  enabled_log {
    category = "SQLSecurityAuditEvents"
  }

  metric {
    category = "AllMetrics"
  }

  lifecycle {
    ignore_changes = [enabled_log, metric]
  }
}

resource "azurerm_mssql_database_extended_auditing_policy" "contactshubdb_audit_policy" {
  database_id            = azurerm_mssql_database.contactshubdb.id
  log_monitoring_enabled = true
}

resource "azurerm_monitor_diagnostic_setting" "contactshubdb_sql_auditing" {
  name                           = "datadog-sql-auditing"
  target_resource_id             = azurerm_mssql_database.contactshubdb.id
  eventhub_name                  = data.azurerm_eventhub.evh_datadog.name
  eventhub_authorization_rule_id = data.azurerm_eventhub_namespace_authorization_rule.evhns_datadog_authorization_rule.id

  enabled_log {
    # category = "SQLSecurityAuditEvents"
    category_group = "AllLogs"
  }

  metric {
    category = "AllMetrics"
  }

  lifecycle {
    ignore_changes = [enabled_log, metric]
  }
}

Debug Output/Panic Output

-

Expected Behaviour

1. A Diagnostic Setting named “Datadog” with all logs enabled should be created to send the logs to Azure Event Hub.
2. The Azure SQL Database should have Azure SQL Auditing enabled and configured to send the logs to Azure Event Hub.

Actual Behaviour

1. A Diagnostic Setting named “Datadog” with all logs enabled has been created and is configured to send the logs to Azure Event Hub.
2. The Azure SQL Database has Azure SQL Auditing enabled, but there is no Azure Event Hub configured in the Azure Portal.

It is not possible to enable all logs and Azure SQL Auditing logs simultaneously and send them to an Event Hub.

Steps to Reproduce

terraform apply

Important Factoids

No response

References

No response

[fjalcarazp]

By the way, If I enable all logs in the Azure SQL Server the Azure SQL Auditing is also enabled but the event hub is not configured properly as well.

resource "azurerm_mssql_server_extended_auditing_policy" "mssqlserver_audit_policy" {
  server_id              = azurerm_mssql_server.mssqlserver.id
  log_monitoring_enabled = true
}

resource "azurerm_monitor_diagnostic_setting" "mssql_diagnotic_setting" {
  # If the first deployment fails, run it again.
  name                           = "datadog"
  target_resource_id             = "${azurerm_mssql_server.mssqlserver.id}/databases/master"
  eventhub_authorization_rule_id = data.azurerm_eventhub_namespace_authorization_rule.evhns_datadog_authorization_rule.id
  eventhub_name                  = data.azurerm_eventhub.evh_datadog.name

  enabled_log {
    # category = "SQLSecurityAuditEvents"
    category_group = "AllLogs"
  }

  metric {
    category = "AllMetrics"
  }

  # lifecycle {
  #   ignore_changes = [enabled_log, metric]
  # }
}

[peanutsguy]

I've got the same issue when pointing to a Log Analytics Workspace

resource "azurerm_mssql_server_extended_auditing_policy" "this" {
  server_id              = azurerm_mssql_server.this.id
  log_monitoring_enabled = true
}

resource "azurerm_mssql_server_microsoft_support_auditing_policy" "this" {
  server_id              = azurerm_mssql_server.this.id
  log_monitoring_enabled = true
}

resource "azurerm_monitor_diagnostic_setting" "this_mssql" {
  lifecycle {
    ignore_changes = [
      log_analytics_workspace_id,
      log,
      metric
    ]
  }
  name                       = join("", ["sqldiag", var.sqlep_azurerm_app_name, var.enviroment, var.abrloc])
  target_resource_id         = "${azurerm_mssql_server.this.id}/databases/master"
  log_analytics_workspace_id = var.workspace_id

  enabled_log {
    category_group = "AllLogs"
  }

  metric {
    category = "AllMetrics"
  }
}

[fmelendezn]

In my company currently Cybersecurity department are requesting for this on any SQLdatabase creation that will having sensitive data.