azurerm_mssql_database_extended_auditing_policy and azurerm_monitor_diagnostic_setting cannot find master database at first run

ncseffai commented 1 year ago

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

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 commented 1 year ago

@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 commented 1 year ago

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 commented 1 year ago

@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 commented 1 year ago

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_diagnosticcategories 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 commented 1 year ago

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 commented 1 year ago

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 commented 11 months ago

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 commented 5 days ago

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?

hashicorp / terraform-provider-azurerm