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

[jadamsHCBB]

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

[aristosvo]

@jadamsHCBB Thanks for raising this issue!

Is it possible to add a simpler reproduction scenario which reflect the desired configuration? Or at least a simplified configuration as used on step 4? At this moment I'm flying a bit blind on what the actual configuration is you'd like to have.

[jadamsHCBB]

Yes of course. Updated with a simple implementation. The important thing to note is that it seems the issues is stemming from this being a "Secondary" create_mode database based on our primary. Which, that seems to be where the extended_audit_policy is being inherited from. So I did an import of said policy, then ran an apply and it gave me this error.

[aristosvo]

Indeed, secondary databases inherit their extended auditing policy from their primary. If not explicitly necessary, I wouldn't change it tbh, and also not import it. See also the documentation.

Is scenario below as it is before step 3?

provider "azurerm" {
  features {}
}

variable "secondary_location" {
  type    = string
  default = "northeurope"
}

resource "azurerm_resource_group" "main" {
  name     = "jadams-example"
  location = "westeurope"
}

resource "azurerm_mssql_server" "main" {
  name                         = "sqlserver-jadams-example"
  resource_group_name          = azurerm_resource_group.main.name
  location                     = azurerm_resource_group.main.location
  version                      = "12.0"
  administrator_login          = "missadministrator"
  administrator_login_password = "AdminPassword123!"
}

resource "azurerm_mssql_database" "main" {
  name        = "db-main"
  server_id   = azurerm_mssql_server.main.id
}

resource "azurerm_storage_account" "main" {
  name                     = "jadamsexample"
  resource_group_name      = azurerm_resource_group.main.name
  location                 = azurerm_resource_group.main.location
  account_tier             = "Standard"
  account_replication_type = "LRS"
}

resource "azurerm_mssql_database_extended_auditing_policy" "main" {
  database_id                             = azurerm_mssql_database.main.id
  storage_endpoint                        = azurerm_storage_account.main.primary_blob_endpoint
  storage_account_access_key              = azurerm_storage_account.main.primary_access_key
  storage_account_access_key_is_secondary = false
  retention_in_days                       = 6
}

resource "azurerm_mssql_server" "secondary" {
  name                         = "sqlserver-jadams-example-secondary"
  resource_group_name          = azurerm_resource_group.main.name
  location                     = var.secondary_location
  version                      = "12.0"
  administrator_login          = "missadministrator"
  administrator_login_password = "AdminPassword123!"
}

resource "azurerm_mssql_database" "secondary" {
  name        = "db-secondary"
  server_id   = azurerm_mssql_server.secondary.id
  create_mode = "Secondary"      

  creation_source_database_id = azurerm_mssql_database.main.id
}

[jadamsHCBB]

Yeah that is basically how I have it all set up. Also like to note, even before importing the audit policy, I still got the same error. That is why I ended up importing it to see if it would resolve the issue.

[aristosvo]

I can't reproduce it with configuration like above, I'm not even allowed to change the auditing policy on the secondary. There must be something missing..!

---

When I add this to the configuration above, I get an error:

resource "azurerm_mssql_database_extended_auditing_policy" "secondary" {
  database_id                             = azurerm_mssql_database.secondary.id
  storage_endpoint                        = azurerm_storage_account.main.primary_blob_endpoint
  storage_account_access_key              = azurerm_storage_account.main.primary_access_key
  storage_account_access_key_is_secondary = false
  retention_in_days                       = 6
}

Error: │ Error: A resource with the ID "/subscriptions/<subscriptionId>/resourceGroups/jadams-example/providers/Microsoft.Sql/servers/sqlserver-jadams-example-secondary/databases/db-secondary/extendedAuditingSettings/Default" already exists - to be managed via Terraform this resource needs to be imported into the State. Please see the resource documentation for "azurerm_mssql_database_extended_auditing_policy" for more information.

But I cannot import it and then change this resource, it errors out on the change.

---

Final attempt is done by adding extended_auditing_policy to the secondary database, which gives me indeed that kind of error, which can be expected. But in your example configuration it is not present, so I don't expect it to be the cause of this error.

Unless you've new information, this issue is not reproducible and works as expected at my side.

[jadamsHCBB]

Oh. I see both my databases have the same exact name but just on separate servers. One being "sql-server-main" next being "gr-sql-server-main". Both databases are named "database". Would this have any effect here?

[aristosvo]

That wouldn't effect any of this.

[jadamsHCBB]

Okay one final update. I added my almost exact configuration. Its a bit complex but maybe will help determine what I did wrong. Perhaps its attempting to do something I am not aware of? Because as I said, the plan only attempts to update tags as expected here.

Edit: Added the audit policy block as well for the main server-1 database.

[jadamsHCBB]

Commit Update

So I found this commit and I see the intended behavior. If its marked as secondary create mode then it throws this error. Which I am sure is intended when creating or destroying a resource. But what if I am attempting to manage an imported resource?

I originally imported these resources using the above root/module structure. The extended audit policy using "Storage" is the thing causing the error. Also, when I do a terraform state show of said the database above, it shows the extended policy attached, is there a way you can see for me to ignore it if it is already set? I am not even trying to change anything related to audit policies. I am simply trying to update some tags during an "apply".

Also, sorry for all the confusion.

[aristosvo]

So, to summarise/rephrase your problem:

• You created the primary and secondary outside Terraform
• Both are imported into state, and the secondary has extended_auditing_policy in its state, which causes error on the apply of tags

I'm pretty sure we can fix this one way or another! I'll check your resource setup and push a possible fix, or explain how you can modify your state.

[jadamsHCBB]

Yup that is exactly it! Thank you so much for all the help!

[aristosvo]

Found it, fix is submitted!

It didn't depend on the import in the end, it depends on the behavior of the API which seems changed. It is also affecting non-imported secondary databases updates.

[jadamsHCBB]

Thanks a bunch! Really appreciate it.

[aristosvo]

@jadamsHCBB Thanks for your patience as well! I almost gave up tbh, proud we've nailed it together👍🏽

[github-actions]

This functionality has been released in v2.82.0 of the Terraform Provider. Please see the Terraform documentation on provider versioning or reach out if you need any assistance upgrading.

For further feature requests or bug reports with this functionality, please create a new GitHub issue following the template. Thank you!

[github-actions]

I'm going to lock this issue because it has been closed for 30 days ⏳. This helps our maintainers find and focus on the active issues.
If you have found a problem that seems similar to this, please open a new issue and complete the issue template so we can capture all the details necessary to investigate further.