Terraform-provider-azurerm: Problem with threat_detection_policy in azurerm_sql_database module

[ravhik]

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 v0.12.8
provider.azurerm v1.35.0

Affected Resource(s)

• azurerm_sql_database

Terraform Configuration Files

resource "azurerm_sql_database" "sql_database" {
  name                = var.sql_database_name
  resource_group_name = var.sql_database_resource_group_name
  location            = var.sql_database_location
  server_name         = var.sql_server_name
  create_mode         = "OnlineSecondary"
  source_database_id  = var.sql_source_database_id
  edition             = var.sql_database_edition

  requested_service_objective_name = var.sql_database_service_objective

  threat_detection_policy {
    state = "Disabled"
    use_server_default = "Disabled"
  }
}

Description

Hello,
I have two databases: primary and secondary, on two different servers in two locations and established a failover group between them. The secondary database is created with the create_mode property set to "OnlineSecondary". For both databases I configured the threat_detection_policy with the state property set to "Disabled".
After deployment, when I checked the terraform state with terraform plan I got info that there will be one change, namely for my secondary database the property use_default_server will be add with the default value "Disabled".
I tried to add this property to my configuration to avoid this behavior , but unfortunately terraform doesn't see it and I got still same outcome.

   ~ resource "azurerm_sql_database" "sql_database" {
         collation                        = "SQL_Latin1_General_CP1_CI_AS"
         create_mode                      = "OnlineSecondary"
         creation_date                    = "2019-10-01T13:35:50.103Z"
         default_secondary_location       = "West Europe"
         edition                          = "Standard"
         id                               = "/subscriptions/sub_id/resourceGroups/some_rescource_group_name_ne/providers/Microsoft.Sql/servers/server_ne/databases/some_db_name"
         location                         = "northeurope"
         max_size_bytes                   = "268435456000"
         name                             = "some_db_name"
         read_scale                       = false
         requested_service_objective_id   = "some_id"
         requested_service_objective_name = "S0"
         resource_group_name              = "some_resouce_group_name"
         server_name                      = "some_server_name"
         source_database_id               = "/subscriptions/sub_id/resourceGroups/some_rescource_group_name_we/providers/Microsoft.Sql/servers/server_we/databases/some_db_name"
       ~ threat_detection_policy {
             disabled_alerts      = []
             email_account_admins = "Disabled"
             email_addresses      = []
             retention_days       = 0
             state                = "Disabled"
           + use_server_default   = "Disabled"
         }
     }

Debug Output

Panic Output

Expected Behavior

Actual Behavior

Steps to Reproduce

1. terraform apply

Important Factoids

References

• #0000

[danielweberoffice]

I want to enhance the problem a bit. In case we create the 2 databases on the primary and secondary server we are not able to change something on the secondary.

If we want to increase the performance level (DTUs) on the databases it is only possible to increase on the primary. The secondary will stick to the old configured values.

Any subsequent execution of terraform plan shows that performance level on the secondary will be updated, at least it is stated there. After an successful run the performance level is still not modified on the secondary server and the plan will show the same changes again.

If you do a failover to switch primary and secondary and try to apply the changes again a hard error is thrown:

Error: Error issuing create/update request for SQL Database "XXX" (Resource Group "XXX", Server "XXX"): sql.DatabasesClient#CreateOrUpdate: Failure sending request: StatusCode=400 -- Original Error: Code="Failed" Message="The async operation failed." InnerError={"unmarshalError":"json: cannot unmarshal array into Go struct field serviceError2.details of type map[string]interface {}"} AdditionalInfo=[{"code":"BadRequest","details":[],"innererror":[],"message":"The destination database name 'XXX' already exists on the server 'XXX'.","target":null}]