azurerm_mssql_database_extended_auditing_policy and azurerm_monitor_diagnostic_setting cannot find master database at first run

[ncseffai]

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

Terraform Version

1.5.0

AzureRM Provider Version

3.61.0

Affected Resource(s)/Data Source(s)

azurerm_monitor_diagnostic_setting / "azurerm_mssql_database"

Terraform Configuration Files

resource "azurerm_resource_group" "example" {
  name     = "rg01"
  location = "West Europe"
}

resource "azurerm_log_analytics_workspace" "log_analytics_workspace_mgmt" {
  name                = "example-law"
  resource_group_name = azurerm_resource_group.example.name
  location                     = azurerm_resource_group.example.location
} 

resource "azurerm_mssql_server" "example" {
  name                         = "sql123abc"
  resource_group_name          = azurerm_resource_group.example.name
  location                     = azurerm_resource_group.example.location
  version                      = "12.0"
  administrator_login          = "4dm1n157r470r"
  administrator_login_password = "4-v3ry-53cr37-p455w0rd"
  minimum_tls_version          = "1.2"
}

resource "azurerm_mssql_database" "db01" {
  name           = "db01"
  server_id      = azurerm_mssql_server.example.id
  collation      = "SQL_Latin1_General_CP1_CI_AS"
  license_type   = "LicenseIncluded"
  max_size_gb    = 1
  sku_name       = "Basic"

  lifecycle {
    ignore_changes = [
      license_type
    ]  
  }
}

resource "azurerm_monitor_diagnostic_setting" "azure_mssql_auditing_master" {
  name                       = "azure-mssql-auditing_master"
  log_analytics_workspace_id = azurerm_log_analytics_workspace.log_analytics_workspace_mgmt.id
  target_resource_id         = "${azurerm_mssql_server.example.id}/databases/master"

  enabled_log {
    category = "SQLSecurityAuditEvents"

    retention_policy {
      enabled = false
    }
  }

  metric {
    category = "AllMetrics"

    retention_policy {
      enabled = false
    }
  }

  lifecycle {
    ignore_changes = [log, metric]
  }
  
  depends_on = [
    azurerm_mssql_server.example
  ]
}

resource "azurerm_monitor_diagnostic_setting" "azure_mssql_auditing_db01" {
  name                       = "azure-mssql-auditing_db01"
  log_analytics_workspace_id = azurerm_log_analytics_workspace.log_analytics_workspace_mgmt.id
  target_resource_id         = azurerm_mssql_database.db01.id

  enabled_log {
    category = "SQLSecurityAuditEvents"

    retention_policy {
      enabled = false
    }
  }

  metric {
    category = "AllMetrics"

    retention_policy {
      enabled = false
    }
  }

  lifecycle {
    ignore_changes = [log, metric]
  }
}

resource "azurerm_mssql_database_extended_auditing_policy" "audit_db01" {
  database_id            = azurerm_mssql_database.db01.id
  log_monitoring_enabled = true
}

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

resource "azurerm_mssql_database_extended_auditing_policy" "audit_master" {
  database_id            = "${azurerm_mssql_server.example.id}/databases/master"
  log_monitoring_enabled = true
}

Debug Output/Panic Output

│ Error: creating Monitor Diagnostics Setting "azure-mssql-auditing_master" for Resource "/subscriptions/XXXX/resourceGroups/rg01/providers/Microsoft.Sql/servers/sql123abc/databases/master": diagnosticsettings.DiagnosticSettingsClient#CreateOrUpdate: Failure responding to request: StatusCode=404 -- Original Error: autorest/azure: Service returned an error. Status=404 Code="ResourceNotFound" Message="The Resource 'Microsoft.Sql/servers/sql123abc/databases/master' under resource group 'rg01' was not found. For more details please go to https://aka.ms/ARMResourceNotFoundFix"
│
│   with azurerm_monitor_diagnostic_setting.azure_mssql_auditing_master,
│   on main.tf line 37, in resource "azurerm_monitor_diagnostic_setting" "azure_mssql_auditing_master":
│   37: resource "azurerm_monitor_diagnostic_setting" "azure_mssql_auditing_master" {
│
╵
╷
│ Error: creating MsSql Database "master" Extended Auditing Policy (Sql Server "sql123abc" / Resource Group "rg01"): sql.ExtendedDatabaseBlobAuditingPoliciesClient#CreateOrUpdate: Failure responding to request: StatusCode=404 -- Original Error: autorest/azure: Service returned an error. Status=404 Code="ResourceNotFound" Message="The Resource 'Microsoft.Sql/servers/sql123abc/databases/master' under resource group 'rg01' was not found. For more details please go to https://aka.ms/ARMResourceNotFoundFix"
│
│   with azurerm_mssql_database_extended_auditing_policy.audit_master,
│   on main.tf line 103, in resource "azurerm_mssql_database_extended_auditing_policy" "audit_master":
│  103: resource "azurerm_mssql_database_extended_auditing_policy" "audit_master" {

Expected Behaviour

After running the terraform apply, the SQL Audit should be enabled and configured to send the logs to the Log Analytics workspace, both on server level and on the DB01 database level.

SERVER LEVEL AUDIT SETTING

DB01 DATABASE LEVEL AUDIT SETTING

Actual Behaviour

On server level the audit is turned on, but the log analyitcs workspace is not configured as a destination. The reason for that is because at the first run there is an error stating the master database cannot be found. If the terraform plan / apply runs again, it will find the master database and the log analyitcs workspace will be configured correctly.

SERVER LEVEL AUDIT CONFIGURATION AFTER FIRST RUN

I tried to put timer to wait 5 minutes after the SQL server is created, but it does not solves the issue. However if I just rerun it again (plan/apply) it works immediately.

The terraform file based on this example (note this is for the older Terraform version, because the "log" is deprecated and "enabled_log" should be used.
https://github.com/hashicorp/terraform-provider-azurerm/blob/main/examples/sql-azure/sql_auditing_log_analytics/main.tf

For the DB01 database it configures it correctly after first run

Steps to Reproduce

terraform.exe plan -out=main.tfplan

terraform.exe apply main.tfplan

Important Factoids

No response

References

https://registry.terraform.io/providers/hashicorp/azurerm/latest/docs/resources/mssql_database_extended_auditing_policy

https://github.com/hashicorp/terraform-provider-azurerm/blob/main/examples/sql-azure/sql_auditing_log_analytics/main.tf

[sinbai]

@ncseffai thanks for opening this issue. Could you double check the target_resource_id in the following tf config (master database) exists?

resource "azurerm_monitor_diagnostic_setting" "azure_mssql_auditing_master" {
  name                       = "azure-mssql-auditing_master"
  log_analytics_workspace_id = azurerm_log_analytics_workspace.log_analytics_workspace_mgmt.id
  target_resource_id         = "${azurerm_mssql_server.example.id}/databases/master"
 ...
 ...
 ...

[vasti]

The issue is still present with Terraform v1.5.3 & AzureRM provider v3.66.0.

My resources are defined like so:

resource "azurerm_mssql_server" "mssql_server" {
  name                         = "mock-sql-name"
  resource_group_name          = "mock-rg-name"
  location                     = "westeurope"
  <..>
}

data "azurerm_monitor_diagnostic_categories" "diagnostic_categories" {
  resource_id = "${azurerm_mssql_server.mssql_server.id}/databases/master"
}

resource "azurerm_mssql_database_extended_auditing_policy" "audit_master" {
  database_id            = "${azurerm_mssql_server.mssql_server.id}/databases/master"
  log_monitoring_enabled = true
}

I get the ResourceNotFound for both azurerm_monitor_diagnostic_categories data source & azurerm_mssql_database_extended_auditing_policy:

│ Error: retrieving Diagnostics Categories for Resource "Scope (Scope: \"/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/mock-rg-name/providers/Microsoft.Sql/servers/mock-sql-name/databases/master\")": diagnosticsettingscategories.DiagnosticSettingsCategoriesClient#DiagnosticSettingsCategoryList: Failure responding to request: StatusCode=404 -- Original Error: autorest/azure: Service returned an error. Status=404 Code="ResourceNotFound" Message="The Resource 'Microsoft.Sql/servers/mock-sql-name/databases/master' under resource group 'mock-rg-name' was not found. For more details please go to https://aka.ms/ARMResourceNotFoundFix"
│
│   with data.azurerm_monitor_diagnostic_categories.diagnostic_categories,
│   on main.tf line 44, in data "azurerm_monitor_diagnostic_categories" "diagnostic_categories":
│   44: data "azurerm_monitor_diagnostic_categories" "diagnostic_categories" {

│ Error: creating MsSql Database "master" Extended Auditing Policy (Sql Server "mock-sql-name" / Resource Group "mock-rg-name"): sql.ExtendedDatabaseBlobAuditingPoliciesClient#CreateOrUpdate: Failure responding to request: StatusCode=404 -- Original Error: autorest/azure: Service returned an error. Status=404 Code="ResourceNotFound" Message="The Resource 'Microsoft.Sql/servers/mock-sql-name/databases/master' under resource group 'mock-rg-name' was not found. For more details please go to https://aka.ms/ARMResourceNotFoundFix"
│
│   with azurerm_mssql_database_extended_auditing_policy.audit_master,
│   on main.tf line 79, in resource "azurerm_mssql_database_extended_auditing_policy" "audit_master":
│   79: resource "azurerm_mssql_database_extended_auditing_policy" "audit_master" {

It works just fine when I run it second time.

[ncseffai]

@ncseffai thanks for opening this issue. Could you double check the target_resource_id in the following tf config (master database) exists?

resource "azurerm_monitor_diagnostic_setting" "azure_mssql_auditing_master" {
  name                       = "azure-mssql-auditing_master"
  log_analytics_workspace_id = azurerm_log_analytics_workspace.log_analytics_workspace_mgmt.id
  target_resource_id         = "${azurerm_mssql_server.example.id}/databases/master"
 ...
 ...
 ...

I am not sure I understand. If it didn't exists it would not run correctly for the second time, no?

[vasti]

My guess is that it takes some time for master database to properly initialize after the SQL server is created (i.e. AzureRM API reports back to terraform that server is created before master is created).
When azurerm_monitor_diagnostic_categories tries to get diagnostic categories for master during first run - it fails.
By the time second run happens - master is properly initialized and terraform no longer fails.

[ncseffai]

Yes, I agree. I believe this is causing the issue. What is also interesting that even if I set a timer that waits for 5 or 10 minutes and configure the terraform to wait for that before it runs azurerm_monitor_diagnostic_categories code snippet, it still fails!

Should not terraform handle this internaly in its logic?

[henkjan-van-wijk]

I also tried it with a datasource to check for the existance of the master database, but that also fails the first time.

data "azurerm_mssql_database" "master" {
  name      = "master"
  server_id = azurerm_mssql_server.mssql.id
}

[jsayce]

I hit this problem, but managed to solve it with a combination of time_sleep and a datasource:

resource "azurerm_mssql_server" "sql" {
  ...
}

resource "time_sleep" "wait" {
  depends_on      = [azurerm_mssql_server.sql]
  create_duration = "5m"
}

data "azurerm_mssql_database" "master" {
  server_id  = azurerm_mssql_server.sql.id
  name       = "master"
  depends_on = [time_sleep.wait]
}

resource "azurerm_monitor_diagnostic_setting" "diagnostics" {
  name                           = "diagnostics"
  target_resource_id             = data.azurerm_mssql_database.master.id
  ...
}

[jhutchings]

Interestingly this race condition hasn't been widely hit by me or others I work with (or just under reported) but I just had someone report it to me as well.

My guess, without reading the code, is that the REST API isn't waiting for the Master Database to be created as it's a separate concern from provisioning the virtual resource and so the provider isn't waiting either. Is this something a custom poller could be created for in the create phase of the provider? Or is this against AzureRM Provider best practices?

[CSB1708]

I don't know who this might help but i've just hit this issue and rather than adding waits/data blocks I set a depends_on the azurerm_monitor_diagnostic_setting resource set to our database resources e.g. azurerm_mssql_database.this this seems to delay the creation of the azurerm_monitor_diagnostic_setting resource long enough for the master DB to init (at least it did for the runs I did post change) appreciate the dependency isn't strictly correct however it's a way to add the delay without adding time_sleep resource workarounds.