terraform-provider-azurerm: azurerm_mssql_database does not correctly set long_term_retention_policy and threat_detection_policy settings

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

• azurerm: 2.33.0
• Terraform: 0.13.4

Affected Resource(s)

• azurerm_mssql_database

Terraform Configuration Files

resource "azurerm_mssql_database" "example" {
  name            = "example"
  server_id       = azurerm_mssql_server.mgmt.id
  elastic_pool_id = azurerm_mssql_elasticpool.mgmt.id
  max_size_gb     = 100

  threat_detection_policy {
    state                      = "Enabled"
    email_account_admins       = "Enabled"
    use_server_default         = "Enabled"
    storage_endpoint           = var.storage_account_blob_endpoint
    storage_account_access_key = var.storage_account_access_key
  }

  short_term_retention_policy {
    retention_days = 35
  }

  long_term_retention_policy {
    weekly_retention  = "P6W"
    monthly_retention = "P1M"
    yearly_retention  = "P0Y"
    week_of_year      = 1
  }
}

Debug Output

Panic Output

Expected Behavior

The SQL database is provisioned with the expected settings and subsequent plans show zero changes.

Actual Behavior

threat_detection_policy.use_server_default is never set to Enabled and long_term_retention_policy.week_of_year and long_term_retention_policy.yearly_retention are never set to P0Y and 1 respectively. Terraform apply will say it’s updating the resources but that is never reflected in the state.

  ~ resource "azurerm_mssql_database" "example" {
        auto_pause_delay_in_minutes = 0
        collation                   = "SQL_Latin1_General_CP1_CI_AS"
        elastic_pool_id             = "/subscriptions/xxx/resourceGroups/xxx/providers/Microsoft.Sql/servers/xxx/elasticPools/xxx"
        extended_auditing_policy    = []
        id                          = "/subscriptions/xxx/resourceGroups/xxx/providers/Microsoft.Sql/servers/xxx/databases/example"
        max_size_gb                 = 100
        min_capacity                = 0
        name                        = "example"
        read_replica_count          = 0
        read_scale                  = false
        server_id                   = "/subscriptions/xxx/resourceGroups/xxx/providers/Microsoft.Sql/servers/xxx"
        sku_name                    = "ElasticPool"
        tags                        = {}
        zone_redundant              = false

      ~ long_term_retention_policy {
            monthly_retention = "P1M"
          ~ week_of_year      = 0 -> 1
            weekly_retention  = "P6W"
          ~ yearly_retention  = "PT0S" -> "P0Y"
        }

        short_term_retention_policy {
            retention_days = 35
        }

      ~ threat_detection_policy {
            disabled_alerts            = []
            email_account_admins       = "Enabled"
            email_addresses            = []
            retention_days             = 0
            state                      = "Enabled"
            storage_account_access_key = (sensitive value)
            storage_endpoint           = "https://xxx.blob.core.windows.net/"
          ~ use_server_default         = "Disabled" -> "Enabled"
        }
    }

Steps to Reproduce

1. terraform apply

Important Factoids

References

• #0000