Troubleshooting Azure SQL Time-Based Immutability with Terraform

Architecture Diagram

Terraform Apply

➡

Resource Group

➡

SQL Server

➡

SQL Databases

➡

Short-Term Retention
(7 days)

➡

Long-Term Retention
(weekly/monthly/yearly)

➡

AzAPI Update
(time-based immutability)

TL;DR

• Terraform azurerm_mssql_database.long_term_retention_policy cannot fully enable time-based immutability yet.
• immutable_backups_enabled = true alone does not enforce it.
• Use AzAPI (azapi_update_resource) to enable timeBasedImmutability and timeBasedImmutabilityMode.
• Ensure LTR policies exist before updating via AzAPI.
• Validated in portal: backups become undeletable until retention expires.

Problem Statement

Trying to enforce time-based immutability for Azure SQL database backups using Terraform:

long_term_retention_policy {
    immutable_backups_enabled = true
    weekly_retention          = "P1W"
}

✅ Terraform applies without errors
❌ Portal shows Time-Based Immutability = Disabled

Root Cause

• immutable_backups_enabled is not the same as timeBasedImmutability in Azure.
• Terraform azurerm provider lags behind the latest API.
• Advanced immutability properties only exist in preview API:

Microsoft.Sql/servers/databases/backupLongTermRetentionPolicies@2024-11-01-preview

Solution

1. Deploy SQL server + database + LTR using Terraform.
2. Update LTR policy via AzAPI provider:

resource "azapi_update_resource" "ltrp" {
  for_each = azurerm_mssql_database.sql_db

  type      = "Microsoft.Sql/servers/databases/backupLongTermRetentionPolicies@2024-11-01-preview"
  name      = "default"
  parent_id = each.value.id  

  body = {
    properties = {
      weeklyRetention           = "P1W"
      weekOfYear                = 1
      timeBasedImmutability     = "Enabled"
      timeBasedImmutabilityMode = "Unlocked"
    }
  }

  depends_on = [
    azurerm_mssql_database.sql_db
  ]
}

Key Takeaways

• Terraform azurerm provider cannot fully enable immutability yet.
• AzAPI is the reliable workaround for preview-only features.
• Always validate LTR policies in Azure Portal after deployment.
• Hybrid approach = Terraform + AzAPI → production-ready, repeatable, and compliant.

Code Ref - https://github.com/deepforu47/azure-mssql