Understanding Azure SQL Long-Term Retention Immutability Configuration with Terraform and AzAPI

Organisations utilising Azure SQL Database Long-Term Retention (LTR) backup policies might face challenges when trying to disable backup immutability while also defining an immutability mode in the same request. In the case we explored, the customer noticed that the operation was successful via ARM templates, but it failed when executed using Terraform with AzAPI-based resources.

Our investigation revealed that the Azure SQL resource provider enforces certain validation rules which disallow specifying TimeBasedImmutabilityMode when TimeBasedImmutability is set to Disabled. The issue stemmed not from the Azure SQL service itself, but rather from the way configuration values were submitted through Terraform and AzAPI updates.

The suggested workaround is to either leave out the immutability mode or set it to null when turning off time-based immutability. This adjustment will make the request compliant with the resource provider’s validation expectations.

Azure SQL Database facilitates immutable Long-Term Retention (LTR) backups to assist organisations in adhering to compliance, governance, and data protection standards. These policies grant administrators control over whether retained backups can be altered or deleted.

During an investigation concerning Terraform and AzAPI deployments, a customer reported erratic behaviour while trying to disable backup immutability. While equivalent operations through ARM templates were successful, attempts via Terraform led to validation errors.

This article outlines the behaviour observed, our findings, the confirmed root cause, and the recommended fixes.

The customer observed the following:

• Successfully enabling and managing Long-Term Retention backup immutability.
• Configurations related to immutability mode settings worked under specific conditions.
• Efforts to disable immutability via Terraform failed.
• Operations appeared to succeed when performed with ARM templates.

Our discussions confirmed the following key components:

• Azure SQL Database Long-Term Retention (LTR) backup policies
• Backup immutability configurations
• Terraform deployments
• AzAPI resources and updates
• ARM template deployments
• Validation logic of the Azure SQL Resource Provider

When administrators disable backup immutability, they expect the configuration update to be accepted and the policy to shift to a disabled state.

Requests sent through Terraform/AzAPI included both:

• TimeBasedImmutability = Disabled
• TimeBasedImmutabilityMode = Unlocked

This configuration was rejected by the Azure SQL resource provider, returning an error indicating that an immutability policy mode cannot be present if backup immutability is not enabled.

The customer wanted clarity on whether enabling, disabling, locking, and unlocking backup immutability should be possible through AzAPI resources and whether this indicated a product issue.

The support team analyzed requests sent to the Azure SQL resource provider and compared successful and unsuccessful operations, focusing on the differences between ARM template deployments and Terraform-driven updates.

For ARM templates disabling immutability, the request included:

• TimeBasedImmutability = Disabled

and notably did not have an immutability mode parameter.

Conversely, when Terraform tried to disable immutability, the request included:

• TimeBasedImmutability = Disabled
• TimeBasedImmutabilityMode = Unlocked

This led to a validation failure from the Azure SQL resource provider.

Our team confirmed that the error originated from the Azure SQL resource provider and could be reproduced outside Terraform, including similar testing via ARM deployments with the conflicting parameter combination.

The resource provider provided an error message like:

Cannot set immutability policy mode when backup immutability is not enabled.

This investigation uncovered a critical behavioural difference. Terraform itself did not yet include dedicated Time-Based Immutability parameters in its SQL modules. Therefore, the customer used AzAPI resources for direct REST-based operations.

The team found that:

• azapi_resource behaved as expected.
• azapi_resource_update could retrieve and reuse an existing property value if no new value was provided explicitly.

This resulted in the immutability mode value persisting unexpectedly during updates.

The engineering team discussed and validated the reported behaviour. Testing confirmed that requests with immutability mode while immutability was disabled are expected to fail due to platform validation.

The failure was due to the update request trying to disable backup immutability while simultaneously providing a value for TimeBasedImmutabilityMode. Azure SQL validation rules dictate that an immutability mode must only be associated with an enabled immutability configuration. When immutability is disabled, no immutability mode should be supplied.

Moreover, the azapi_resource_update behaviour could retain a previously configured immutability mode value unless explicitly cleared, causing requests to unintentionally keep an immutability mode when the aim was full immutability deactivation.

The available evidence supports this conclusion through:

1. Comparison of successful ARM template requests and failing Terraform requests.
2. Reproduction of the same validation outcomes by the Azure SQL resource provider.
3. Validation of AzAPI update behaviour involving retained values.

When you are disabling backup immutability:

• Set TimeBasedImmutability to Disabled.
• Avoid providing TimeBasedImmutabilityMode.

Our investigation found that explicitly setting:

TimeBasedImmutabilityMode = null

will prevent the previous value from being reused and allow the request to be processed correctly.

The support team provided the following expected behaviour:

These guidelines were discussed during the investigation.

After following the mitigation steps:

• The disable operation should complete without any immutability mode conflicts.
• Requests should cease to trigger the Azure SQL validation error relating to immutability mode usage.

• Always ensure that immutability mode isn’t included when disabling backup immutability.
• Check Terraform templates for dynamically generated properties that may still carry forward previously populated values.
• When utilising AzAPI update resources, manage nullable properties explicitly where applicable to prevent unintended value persistence.

Behaviour may differ based on:

• Azure SQL API version
• Terraform provider version
• Details surrounding AzAPI provider implementation
• Current Long-Term Retention backup state
• Presence of previously locked backups

Always test deployment behaviour in a non-production setting before rolling out configuration changes widely.

This investigation confirmed that the inability to disable Azure SQL Long-Term Retention backup immutability was not a platform defect within Azure SQL. Instead, the failure arose from attempts to specify an immutability mode while disabling immutability itself. Additionally, AzAPI update behaviour that retained previously configured values unless cleared contributed to the issue. Setting the immutability mode to null, or completely omitting it when disabling immutability, resolved the conflict.

The key takeaway here is that TimeBasedImmutabilityMode and TimeBasedImmutability must align with Azure SQL resource provider validation rules, especially during infrastructure-as-code deployments.