search

hashicorp / terraform-provider-azurerm

Terraform provider for Azure Resource Manager
https://registry.terraform.io/providers/hashicorp/azurerm/latest/docs
Mozilla Public License 2.0
4.6k stars 4.65k forks source link

azurerm_mssql_database does not correctly set long_term_retention_policy and threat_detection_policy settings #9067

Closed devblackops closed 3 years ago

devblackops commented 4 years ago

Community Note

Terraform (and AzureRM Provider) Version

Affected Resource(s)

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

scott-doyland-burrows commented 3 years ago

Please also see:

https://github.com/terraform-providers/terraform-provider-azurerm/issues/9318#issuecomment-740751924

The link above also has an issue re being unable to set the weekly backups back to zero when desired.

Looks like there are two issues that are closely related.

nerddtvg commented 3 years ago

In a similar vein to this, I am unable to specify PT0S as a value due to the regex check:

    yearly_retention = "PT0S"
Error: "long_term_retention_policy.0.yearly_retention" did not match regex "^P[0-9]*[YMWD]"

  on main.tf line 528, in resource "azurerm_mssql_database" "app_db":
 528: resource "azurerm_mssql_database" "app_db" {

Error: "long_term_retention_policy.0.yearly_retention" has to be a valid Duration format, starting with "P" and ending with either of the letters "YMWD"

  on main.tf line 528, in resource "azurerm_mssql_database" "app_db":
 528: resource "azurerm_mssql_database" "app_db" {
catt231 commented 3 years ago

plans wanting to reapply existing settings each run is really infuriating, this has been open quite a while now - any idea when it will be looked at?

 ~ long_term_retention_policy {
            monthly_retention = "P1Y"
          ~ week_of_year      = 0 -> 1
            weekly_retention  = "P8W"
          ~ yearly_retention  = "PT0S" -> "P0Y"
        }
g-psantos commented 3 years ago

Not sure if this'll help anyone, but one thing that's worked well for us with the backup retention block is to set yearly_retention and week_of_year to null.

sMuro commented 3 years ago

Not sure if this'll help anyone, but one thing that's worked well for us with the backup retention block is to set yearly_retention and week_of_year to null.

Setting week_of_year to 'null' in updates works, but if you create a new database with week_of_year as null then it throws an error

Error: Error issuing create/update request for Sql Server "<sql-server-name>" (Database "<database-name>") Long Term Retention Policies (Resource Group "<resource-group-name>"): sql.BackupLongTermRetentionPoliciesClient#CreateOrUpdate: Failure sending request: StatusCode=0 -- Original Error: Code="LongTermRetentionMissingWeekOfYear" Message="WeekOfYear is required to be set between 1 and 52 in order to set yearly retention."

smitp11 commented 3 years ago

terraform 0.14.9 azurerm 2.53.0

attempted to set this today:

short_term_retention_policy {
    retention_days = 1
    }

received this error: Error: expected short_term_retention_policy.0.retention_days to be in the range (7 - 35), got 1

microsoft documentation (and portal) suggest valid values for PITR are 1-35.

smitp11 commented 3 years ago

plans wanting to reapply existing settings each run is really infuriating, this has been open quite a while now - any idea when it will be looked at?

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

terraform 0.14.9 azurerm 2.53.0

seeing the same issue as @catt231 where by the long_term_retention is not being stored in the state file and thus every terraform plan shows that the configuration needs to be applied, here's the output:

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

and the terraform state file shows these settings:

"long_term_retention_policy": [
              {
                "monthly_retention": "PT0S",
                "week_of_year": 0,
                "weekly_retention": "PT0S",
                "yearly_retention": "PT0S"
              }
]
drdamour commented 3 years ago

is there an ENVAR to skip the regex or something?

drdamour commented 3 years ago

another similar issue, i had this

long_term_retention_policy {
  weekly_retention = "P45D"
}

and during apply got the error 

Error: Error issuing create/update request for Sql Server "example" (Database "example") Long Term Retention Policies (Resource Group "example"): sql.BackupLongTermRetentionPoliciesClient#CreateOrUpdate: Failure sending request: StatusCode=0 -- Original Error: Code="LongTermRetentionMissingWeekOfYear" Message="WeekOfYear is required to be set between 1 and 52 in order to set yearly retention."

but this setting is supposed to be optional...?

catt231 commented 3 years ago

I can't believe this isn't more of an issue, it's been open for a good while now

tkryl commented 3 years ago

There is no way to set a default value week_of_year = 0 or week_of_year = null it is possible to define by default in parent module long_term_retention_policy { weekly_retention = null monthly_retention = null yearly_retention = null week_of_year = 1 } Unfortunately, week_of_year = null raises an error, but this expression week_of_year = 1 always tries to update 0 to 1

tombuildsstuff commented 3 years ago

Taking a look through here the original issue appears to have been fixed by #12198 - closing this. For any new issues please open a new issue to track those specifically.

github-actions[bot] commented 3 years ago

This functionality has been released in v2.72.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!

drdamour commented 3 years ago

anyone who thumbs upped this or subscribed, i reopened as #13035 since it doesnt' seem initial issue was resolved in that we require week_in_year still and it always comes back as a change in plan

github-actions[bot] commented 3 years ago

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.