terraform-provider-azurerm: Error: cannot configure `extended_auditing_policy` in secondary create mode for Database: (Name "database" / Server Name "sql_server" / Resource Group "resource_group")

Error output: Error: cannot configure "extended_auditing_policy" in secondary create mode for Database: (Name "database" / Server Name "sql_server" / Resource Group "resource_group") │ │ with module.sql-server.azurerm_mssql_database.main["database"], │ on ../../modules/sql-server/main.tf line 30, in resource "azurerm_mssql_database" "main": │ 30: resource "azurerm_mssql_database" "main" {

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 (and AzureRM Provider) Version

Terraform v1.0.9
on linux_amd64
+ provider registry.terraform.io/hashicorp/azurerm v2.81.0

Affected Resource(s)

• azurerm_mssql_database

Terraform Configuration Files

Server-1 Root

module "main-resource-group" {
  source              = "../../modules/resource-group"
  location            = "southcentralus"
  resource_group_name = var.resource_group_name
  tags = merge(var.tags, {
    
  })
}

module "sql-server" {
  source                       = "../../modules/sql-server"
  name                         = "sql-server"
  location                     = var.location
  resource_group_name          = var.resource_group_name
  minimum_tls_version          = null
  version_number               = "12.0"
  administrator_login          = "admin1"
  administrator_login_password = "password1"


  login_username = "admin1"
  object_id      = "<object-id>"
  tenant_id      = "<tenant-id>"

  tier    = "1"

  databases = {
    database_1 = {
      name                        = "database_1"
      auto_pause_delay_in_minutes = 0      
      create_mode                 = null
      server_id                   = var.server_id
      elastic_pool_id             = var.elastic_pool_id
      collation                   = "SQL_Latin1_General_CP1_CI_AS"
      read_scale                  = false
      sku_name                    = "ElasticPool"
      zone_redundant              = false
      tags = {
        app     = "app1"
      }
    }       
  }

extendedauditpolicy = [
    {
      name = "database_1"
      database_id = "${var.database_id}database_1"
      log_monitoring_enabled = true
      storage_endpoint = "${var.storage_endpoint}"
      retention_in_days = 7
    }
  ]

  tags = merge(var.tags, {
    type = "sql"
  })

}

Server-2 Root

module "main-resource-group" {
  source              = "../../modules/resource-group"
  location            = "southcentralus"
  resource_group_name = var.resource_group_name
  tags = merge(var.tags, {
    
  })
}

module "sql-server" {
  source                       = "../../modules/sql-server"
  name                         = "sql-server-secondary"
  location                     = var.location
  resource_group_name          = var.resource_group_name
  minimum_tls_version          = null
  version_number               = "12.0"
  administrator_login          = "admin1"
  administrator_login_password = "password1"


  login_username = "admin1"
  object_id      = "<object-id"
  tenant_id      = "<tenant-id>"

  tier    = "1"

  databases = {
    database_1 = {
      name                        = "database_1"
      auto_pause_delay_in_minutes = 0      
      create_mode                 = "Secondary"
      server_id                   = var.server_id
      elastic_pool_id             = var.elastic_pool_id
      collation                   = "SQL_Latin1_General_CP1_CI_AS"
      read_scale                  = false
      sku_name                    = "ElasticPool"
      zone_redundant              = false
      tags = {
        app     = "app1"
      }
    }       
  }

  tags = merge(var.tags, {
    type = "sql"
  })

}

SQL-Server Module

resource "azurerm_mssql_server" "main" {
  name                         = var.name
  location                     = var.location
  resource_group_name          = var.resource_group_name
  minimum_tls_version          = var.minimum_tls_version
  version                      = var.version_number
  administrator_login          = var.administrator_login
  administrator_login_password = "4-v3ry-53cr37-p455w0rd"

  azuread_administrator {
    login_username = var.login_username
    object_id      = var.object_id
    tenant_id      = var.tenant_id
  }

  tags = merge(var.tags, {
    type = "sql"
    tier = var.tier
  })

  lifecycle {
    ignore_changes = [
      administrator_login,
      administrator_login_password
    ]
  }
}

resource "azurerm_mssql_database" "main" {
  for_each = var.databases

  name            = each.value.name
  server_id       = each.value.server_id
  elastic_pool_id = each.value.elastic_pool_id
  create_mode =  each.value.create_mode
  collation       = each.value.collation
  read_scale      = each.value.read_scale
  sku_name        = each.value.sku_name
  zone_redundant  = each.value.zone_redundant
  
  
  tags = merge(var.tags, {
    app = each.value.tags.app
    failover = each.value.tags.failover          
  })

} 
       
resource "azurerm_mssql_database_extended_auditing_policy" "main" {           
  count = length(var.extendedauditpolicy) > 0 ? length(var.extendedauditpolicy) : 0 

  database_id                               = var.extendedauditpolicy[count.index].database_id
  log_monitoring_enabled                    = var.extendedauditpolicy[count.index].log_monitoring_enabled
  storage_endpoint                          = var.extendedauditpolicy[count.index].storage_endpoint
  # storage_account_access_key              = azurerm_mssql_database.main[each.key]
  # storage_account_access_key_is_secondary = false          
  retention_in_days                       = var.extendedauditpolicy[count.index].retention_in_days      
}

Debug Output

Panic Output

Expected Behaviour

Per the Terraform Plan, the only thing that should change are some tags on apply. (Plan looks clean).

Actual Behaviour

When I run an apply, I get the above error.

Steps to Reproduce

1. Configure Terraform for Azure SQL Databases
2. Extended Audit policy is configured outside of Terraform
3. Import extended_audit_policy for said database
4. terraform apply
5. error above

Important Factoids

References

• #0000