Understanding Azure SQL Long-Term Retention Immutability Configuration with Terraform and AzAPI
Executive Summary Organizations using Azure SQL Database Long-Term Retention (LTR) backup policies may encounter failures when attempting to disable backup immutability while also specifying an immutability mode in the same request. In the investigated scenario, the customer observed that the operation succeeded when performed through ARM templates but failed when executed through Terraform using AzAPI-based resources. The investigation determined that the Azure SQL resource provider enforces validation rules that prevent TimeBasedImmutabilityMode from being specified when TimeBasedImmutability is set to Disabled. The issue was not caused by the Azure SQL service itself, but rather by how configuration values were being submitted through Terraform and AzAPI resource updates. The recommended mitigation is to ensure that immutability mode is omitted or explicitly set to null when disabling time-based immutability. This allows the request to comply with the resource provider's validation requirements. Introduction Azure SQL Database supports immutable Long-Term Retention (LTR) backups to help organizations meet compliance, governance, and data protection requirements. These policies allow administrators to control whether retained backups can be modified or deleted. During an investigation involving Terraform and AzAPI deployments, a customer reported inconsistent behavior when attempting to disable backup immutability. While equivalent ARM template operations completed successfully, Terraform-based deployments generated validation errors. This article explains the observed behavior, the investigation findings, the confirmed root cause, and the recommended mitigation. Issue Description Reported Symptoms The customer reported the following behavior: Enabling and managing Long-Term Retention backup immutability worked successfully. Configurations involving immutability mode settings could be applied successfully under certain conditions. Attempts to disable immutability through Terraform resulted in failures. Similar operations appeared to succeed when performed using ARM templates. Technical Environment The discussion confirmed the following components: Azure SQL Database Long-Term Retention (LTR) backup policies Backup immutability configuration Terraform deployments AzAPI resources and AzAPI resource updates ARM template deployments Azure SQL Resource Provider validation logic Expected Behavior When administrators disable backup immutability, the configuration update should be accepted and the policy should transition to a disabled state. Actual Behavior Requests submitted through Terraform/AzAPI included both: TimeBasedImmutability = Disabled TimeBasedImmutabilityMode = Unlocked The Azure SQL resource provider rejected this configuration, returning an error indicating that an immutability policy mode cannot be specified when backup immutability is not enabled. Investigation and Troubleshooting 1. Initial Customer Question The customer sought clarification on whether enabling, disabling, locking, and unlocking backup immutability should all be possible through AzAPI resources and whether a product issue existed. 2. Review of Azure SQL Resource Provider Behavior The support team reviewed requests submitted to the Azure SQL resource provider and compared successful and unsuccessful operations. The investigation focused on configuration differences between ARM template deployments and Terraform-driven updates. Confirmed Finding When ARM templates disabled immutability, the request contained: TimeBasedImmutability = Disabled and did not include an immutability mode parameter. Confirmed Finding When Terraform attempted to disable immutability, the request included both: TimeBasedImmutability = Disabled TimeBasedImmutabilityMode = Unlocked This resulted in a validation failure from the Azure SQL resource provider. 3. Validation of Error Behavior The team verified that the error was generated by the Azure SQL resource provider and was reproducible outside Terraform, including equivalent testing through ARM deployments when the conflicting parameter combination was supplied. Confirmed Error The resource provider returned an error equivalent to: Cannot set immutability policy mode when backup immutability is not enabled. 4. Assessment of Terraform and AzAPI Behavior The investigation identified an important behavioral difference. Terraform itself did not yet expose dedicated Time-Based Immutability parameters in its SQL modules. As a result, the customer was using AzAPI resources to perform direct REST-based operations. The team discovered that: azapi_resource behaved as expected. azapi_resource_update could retrieve and reuse an existing property value when no value was explicitly provided. This behavior caused the immutability mode value to persist unexpectedly during updates. 5. Reproduction and Verification The engineering discussion included review and validation of the reported behavior. Testing confirmed that requests containing immutability mode while immutability was disabled were expected to fail due to platform validation. Root Cause Confirmed Root Cause The failure occurred because the update request attempted to disable backup immutability while simultaneously providing a value for TimeBasedImmutabilityMode. Azure SQL validation rules require immutability mode to be associated only with an enabled immutability configuration. When immutability is disabled, an immutability mode must not be supplied. An additional contributing factor was the behavior of azapi_resource_update, which could retain a previously configured immutability mode value when no new value was explicitly provided. Consequently, requests unintentionally included an immutability mode even though the intent was to disable immutability entirely. The available evidence supports this conclusion through: Comparison of successful ARM template requests and failing Terraform requests. Reproduction of the same validation behavior by the Azure SQL resource provider. Validation of the AzAPI update behavior involving retained values. Mitigation and Resolution Recommended Mitigation When disabling backup immutability: Set TimeBasedImmutability to Disabled. Do not provide TimeBasedImmutabilityMode. Terraform/AzAPI Workaround The investigation determined that explicitly setting: TimeBasedImmutabilityMode = null prevents the previous value from being reused and allows the request to be processed correctly. Configuration Matrix Discussed The support team identified the following expected behavior: Operation Immutability Mode Requirement Locking backups Mode should be set to Locked Unlocking while remaining enabled Mode may be supplied and is recommended for clarity Disabling immutability Mode should not be supplied This guidance was explicitly discussed during the investigation. Validation After applying the mitigation: The disable operation should complete without the immutability mode conflict. Requests should no longer trigger the Azure SQL validation error related to immutability mode usage. Recommendations and Best Practices Recommendations Supported by the Investigation Ensure that immutability mode is not included when disabling backup immutability. Review Terraform templates for dynamically generated properties that may continue to emit previously populated values. When using AzAPI update resources, explicitly manage nullable properties where supported to avoid unintended value persistence. Important Considerations Behavior may vary depending on: Azure SQL API version Terraform provider version AzAPI provider implementation details Existing Long-Term Retention backup state Whether previously locked backups exist Always validate deployment behavior in a non-production environment before applying configuration changes broadly. Conclusion This investigation demonstrated that the inability to disable Azure SQL Long-Term Retention backup immutability was not caused by a platform defect in Azure SQL. Instead, the failure occurred because requests attempted to specify an immutability mode while immutability itself was being disabled. The issue was further influenced by AzAPI update behavior that could preserve previously configured values unless explicitly cleared. Setting the immutability mode to null, or removing it entirely when disabling immutability, resolved the problem. The key technical takeaway is that TimeBasedImmutabilityMode and TimeBasedImmutability must be configured consistently with Azure SQL resource provider validation rules, particularly during infrastructure-as-code deployments. Public Documentation Azure SQL Database Long-Term Retention documentation Azure SQL Backup Immutability documentation ARM/Bicep resource documentation for backup Long-Term Retention policies Terraform provider documentation for Azure SQL DatabaseTDE High availability with customer-managed key for Azure SQL Databases
If the server loses access to the stored Database Encryption Key (DEK) in AKV, in up to 10 minutes a database will start denying all connections with the corresponding error message and change its state to Inaccessible. The only action allowed on a database in the Inaccessible state is deleting it. Thus, it's highly recommended to configure the server to use two different key vaults in two different regions with the same key material.
