Error: retrieving Long Term Retention Policy for Managed Database when Instance is stopped

[PAKalucki]

Is there an existing issue for this?

• I have searched the existing issues

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 and review the contribution guide to help.

Terraform Version

1.4.6

AzureRM Provider Version

3.83.0

Affected Resource(s)/Data Source(s)

azurerm_mssql_managed_database

Terraform Configuration Files

resource "azurerm_mssql_managed_database" "this" {
  name                = var.db_name != "" ? var.db_name : "${var.name_prefix}-sqldb"
  managed_instance_id = azurerm_mssql_managed_instance.this.id

  timeouts {
    create = "4h"
  }
}

Debug Output/Panic Output

Error: retrieving Long Term Retention Policy for  Managed Database: (Database Name "sand-xyz-sqldb" / Managed Instance Name "sand-xyz-instance" / Resource Group "dev-xyz-eastus-rg"): sql.ManagedInstanceLongTermRetentionPoliciesClient#Get: Failure responding to request: StatusCode=400 -- Original Error: autorest/azure: Service returned an error. Status=400 Code="LongTermRetentionPolicyNotSupported" Message="Long Term Retention is not supported : Database 'sand-xyz-sqldb' does not exist on server 'sand-xyz-instance'."
│
│   with module.mssql_sandbox[0].azurerm_mssql_managed_database.this,
│   on modules/mssql_managed_instance/main.tf line 1, in resource "azurerm_mssql_managed_database" "this":
│    1: resource "azurerm_mssql_managed_database" "this" {
│
│ retrieving Long Term Retention Policy for  Managed Database: (Database Name "sand-xyz-sqldb" / Managed Instance Name "sand-xyz-instance" / Resource Group "dev-xyz-eastus-rg"):
│ sql.ManagedInstanceLongTermRetentionPoliciesClient#Get: Failure responding to request: StatusCode=400 -- Original Error: autorest/azure: Service returned an error. Status=400 Code="LongTermRetentionPolicyNotSupported" Message="Long Term
│ Retention is not supported : Database 'sand-xyz-sqldb' does not exist on server 'sand-xyz-instance'."

Expected Behaviour

Apply should work

Actual Behaviour

Apply fails despite not using long_term_retention_policy block.

Steps to Reproduce

• create azurerm_mssql_managed_database and instance in it
• stop the managed instance
• run terraform apply again

Important Factoids

No response

References

No response

[brnwn4]

any solution?

[enorlando]

any updates on this?

[andersthorbeck]

We have a MSSQL managed database which has a long term retention policy, but which is frequently stopped for long periods of time for cost-saving reasons, because the database (and its corresponding application) is only infrequently required.

This bug is a pain for us, because it means that we need to remove the entire azurerm_mssql_managed_database resource from the Terraform state for the terraform plan step to successfully complete, in order to allow the terraform apply step to actually carry out other changes to unrelated resources made in the same Terraform state.

A solution here which would avoid breaking the whole Terraform state when the database is in stopped state would be great!

[StefHarmens]

For anyone encountering the same issue: in my case the problem was caused by an expired CMK (Customer Managed Key). After renewing the key, the error was resolved.

[enorlando]

any updates on this?

[NicOps95]

I recently encountered the same issue (~> 4.23). It involves a SQL Managed Instance configured with CMK. During the execution of terraform apply, if the database inside it is turned off, it returns the message: “Error: retrieving Long Term Retention Policy...”.

To resolve the issue, I turned the database on before running terraform apply. If the database is running during the apply, it does not return any error.

[enorlando]

any solution?

[enorlando]

any updates on this?