Terraform on Azure: Key Vault (Secrets), Azure SQL Database & Azure DNS as Code

Three Azure services show up in almost every real workload, and they are exactly the three where a wrong click in the portal quietly becomes a security incident: Key Vault (where secrets live), Azure SQL Database (the data that secret protects), and Azure DNS (the name the world uses to reach it). This lesson wires all three together with Terraform in one working, copy-pasteable demo — a Key Vault that uses Azure RBAC and holds a generated SQL admin password, an Azure SQL server + database that consume that password behind a firewall rule, and a public DNS A record that points a hostname at the app. You will run it end to end: terraform init → plan → apply → verify → destroy.

The reason to teach these three as a unit is that their seams are where engineers get hurt. The Key Vault is easy; granting Terraform permission to write the first secret is where the modern RBAC vs access-policy fork lives, and the wrong choice gives you a 403 Forbidden on your own apply. The SQL server is easy; the fact that its admin password — and any secret you read back with a data source — lands in the state file in cleartext is the thing that turns “I used Key Vault” into “I leaked a password.” The firewall rule is one resource; its most-used form (0.0.0.0–0.0.0.0) does not mean what its numbers suggest. And the DNS record is trivial; the delegation that makes it resolve is not in Terraform at all. We cover each seam explicitly, with the exact HCL and the exact failure.

This is a Senior-tier, hands-on lesson. It assumes you already know core Terraform — HCL, providers, variables, state, modules, for_each — and that you can authenticate the azurerm provider to a subscription (via Azure CLI, a service principal, or OIDC). If the provider/auth/remote-backend setup is new to you, the companion lesson Terraform on Azure: getting started, provider authentication & remote backend covers it in full; this lesson takes that as read and pins hashicorp/azurerm ~> 4.0 throughout.

What you’ll build

The scenario is the data tier for a small line-of-business app. The app needs a SQL database; the database needs an admin credential; that credential must never be typed by a human or committed to Git; and the app must be reachable at app.kv-demo.example. In portal terms that is four blades and a dozen fields, several of which default to insecure. In Terraform it is one directory you can read, review, and destroy in a single command — and, crucially, one you can diff: the day someone loosens the SQL firewall by hand, the next plan shows it as drift.

Concretely, terraform apply will stand up: a resource group; a random_password that mints a 24-character admin secret at apply time; a Key Vault with rbac_authorization_enabled = true, soft-delete and purge protection, plus an azurerm_role_assignment granting the caller Key Vault Secrets Officer so Terraform can write; an azurerm_key_vault_secret holding that password; an azurerm_mssql_server (with an Entra ID admin) and an azurerm_mssql_database that use the password; two firewall rules (allow-Azure-services and your client IP); a public IP; a public azurerm_dns_zone; and an azurerm_dns_a_record that points app at the IP. The whole thing costs a few rupees a day if you leave it running and is fully removed by terraform destroy (with one purge-protection caveat we will hit deliberately).

The three services map to Terraform resources like this — keep this table open, it is the spine of the whole lesson:

Why Terraform for this at all, rather than the portal, az CLI, or ARM/Bicep? Because these resources are stateful, security-sensitive, and long-lived, which is exactly the profile Terraform is built for:

The one honest caveat in that table — secrets can land in Terraform state — is not a reason to avoid Terraform; it is a thing you manage, and half of this lesson is how. Let’s build the pieces.

Azure Key Vault as code

A Key Vault is a hardened, access-controlled store for three kinds of material: secrets (arbitrary strings — passwords, connection strings, API keys), keys (asymmetric/symmetric crypto keys you use without exporting), and certificates (X.509 certs with lifecycle). The azurerm_key_vault resource creates the vault itself; separate resources create the material inside it.

The vault has two SKUs. The difference is where keys live, not how many secrets you can store:

Here is the vault. Note rbac_authorization_enabled (the modern auth mode — more on the fork below), the soft-delete/purge settings, and the network_acls block:

data "azurerm_client_config" "current" {}

resource "azurerm_key_vault" "kv" {
  name                = "kv-demo-prod-001"          # 3-24 chars, globally unique
  location            = azurerm_resource_group.rg.location
  resource_group_name = azurerm_resource_group.rg.name
  tenant_id           = data.azurerm_client_config.current.tenant_id
  sku_name            = "standard"

  # --- modern auth: Azure RBAC on the data plane ---
  rbac_authorization_enabled = true                 # azurerm v4 name (was enable_rbac_authorization)

  # --- data protection (⚠️ purge protection is irreversible) ---
  soft_delete_retention_days = 90
  purge_protection_enabled   = true

  # --- network ---
  public_network_access_enabled = true
  network_acls {
    default_action             = "Deny"
    bypass                     = "AzureServices"
    ip_rules                   = [var.my_ip_cidr]    # e.g. "203.0.113.10/32"
    virtual_network_subnet_ids = []
  }

  tags = local.tags
}

RBAC authorization vs access policies — the fork

This is the single most important decision in the file, and the one that most often produces a 403 on your first apply. A vault authorizes data-plane operations (read/write a secret, use a key) one of two ways, set by rbac_authorization_enabled:

With RBAC, the vault’s ARM (management-plane) creation does not grant you rights to write secrets. You must add a role assignment, and — because the secret write depends on that grant — you must order it with depends_on:

resource "azurerm_role_assignment" "kv_secrets_officer" {
  scope                = azurerm_key_vault.kv.id
  role_definition_name = "Key Vault Secrets Officer"          # write/read/delete secrets
  principal_id         = data.azurerm_client_config.current.object_id
}

The built-in Key Vault roles you will actually use:

The legacy alternative, for a vault with rbac_authorization_enabled = false, is an access policy — note it lives as its own resource and uses verb lists:

# Only valid when rbac_authorization_enabled = false
resource "azurerm_key_vault_access_policy" "legacy" {
  key_vault_id = azurerm_key_vault.kv.id
  tenant_id    = data.azurerm_client_config.current.tenant_id
  object_id    = data.azurerm_client_config.current.object_id

  secret_permissions = ["Get", "List", "Set", "Delete", "Recover", "Purge"]
  key_permissions    = ["Get", "List", "Create", "Delete"]
}

⚠️ Do not mix modes. If rbac_authorization_enabled = true, an azurerm_key_vault_access_policy is ignored (and will error). If it’s false, azurerm_role_assignment at vault scope has no data-plane effect. Pick one; for anything new, pick RBAC.

Secrets, keys and certificates

The three material types are three resources. They differ in what they hold and what “the value” even is:

The secret — this is the one our SQL password uses. Its value comes from random_password, and it depends_on the role assignment so Terraform actually has permission when it writes:

resource "random_password" "sql" {
  length           = 24
  special          = true
  override_special = "!#$%*-_=+"
  min_upper        = 2
  min_lower        = 2
  min_numeric      = 2
}

resource "azurerm_key_vault_secret" "sql_admin_password" {
  name         = "sql-admin-password"
  value        = random_password.sql.result
  key_vault_id = azurerm_key_vault.kv.id
  content_type = "password"

  depends_on = [azurerm_role_assignment.kv_secrets_officer]  # RBAC must land first
}

A key (for example a customer-managed key you’d point storage or SQL TDE at) and a self-generated certificate look like this — you won’t need them for the SQL demo, but they round out the trio:

resource "azurerm_key_vault_key" "cmk" {
  name         = "cmk-tde"
  key_vault_id = azurerm_key_vault.kv.id
  key_type     = "RSA"
  key_size     = 2048
  key_opts     = ["decrypt", "encrypt", "sign", "unwrapKey", "verify", "wrapKey"]
  depends_on   = [azurerm_role_assignment.kv_crypto_officer]
}

resource "azurerm_key_vault_certificate" "self_signed" {
  name         = "app-tls"
  key_vault_id = azurerm_key_vault.kv.id

  certificate_policy {
    issuer_parameters { name = "Self" }
    key_properties {
      exportable = true
      key_type   = "RSA"
      key_size   = 2048
      reuse_key  = true
    }
    secret_properties { content_type = "application/x-pkcs12" }
    x509_certificate_properties {
      subject            = "CN=app.kv-demo.example"
      validity_in_months = 12
      key_usage          = ["digitalSignature", "keyEncipherment"]
    }
  }
  depends_on = [azurerm_role_assignment.kv_certs_officer]
}

Soft-delete, purge protection and the destroy trap

Key Vault has two layers of deletion safety, and the second one will bite your terraform destroy on purpose the first time you meet it:

Soft-delete is always on for vaults now (you only tune the retention days). Purge protection is opt-in and irreversible: once true, you cannot set it back to false, and a destroyed vault’s name is locked until soft-delete retention expires. The provider’s features block controls whether destroy even attempts a purge:

provider "azurerm" {
  subscription_id = var.subscription_id     # required in azurerm v4
  features {
    key_vault {
      purge_soft_delete_on_destroy    = true   # try to purge on destroy...
      recover_soft_deleted_key_vaults = true   # ...and recover a soft-deleted one on create
    }
  }
}

⚠️ Even with purge_soft_delete_on_destroy = true, a vault with purge_protection_enabled = true will not purge until its soft-delete window passes. For a throwaway demo, either leave purge protection off, or accept that the name is parked for the retention period. In production you want purge protection on — it is a compliance control — and you simply never destroy the vault casually.

Consuming a secret (and why the value lands in state)

Two other pieces of Terraform can read a secret back: the azurerm_key_vault_secret data source and any resource attribute that references random_password.sql.result. Both write the plaintext into the state file. This is the security seam of the entire lesson:

The rule that follows: prefer letting the application read Key Vault at runtime (via a Key Vault reference or its managed identity granted Key Vault Secrets User) so the secret never enters Terraform state at all. Use Terraform to provision the vault, generate the secret, and grant the identity — not to shuttle the plaintext into the next resource where you can avoid it. When Terraform genuinely must know a value (like setting a SQL admin password once), keep the blast radius small: an encrypted, RBAC-locked remote backend (Azure Storage blob) so the state itself is protected. The companion remote-backend lesson shows that backend; treat it as mandatory whenever secrets touch state.

Azure SQL Database as code

Azure SQL Database is a PaaS relational database. Its topology in Terraform is two resources: an azurerm_mssql_server (a logical server — an endpoint and an administration boundary, not a VM you can log into) and one or more azurerm_mssql_database on it. Access is governed separately by firewall rules and/or private endpoints.

The server carries the admin identity and the security posture:

resource "azurerm_mssql_server" "sql" {
  name                         = "sql-demo-prod-001"
  resource_group_name          = azurerm_resource_group.rg.name
  location                     = azurerm_resource_group.rg.location
  version                      = "12.0"
  administrator_login          = var.sql_admin_login
  administrator_login_password = random_password.sql.result   # ⚠️ in state
  minimum_tls_version          = "1.2"
  public_network_access_enabled = true

  azuread_administrator {
    login_username              = var.entra_admin_upn
    object_id                   = var.entra_admin_object_id
    tenant_id                   = data.azurerm_client_config.current.tenant_id
    azuread_authentication_only = false     # true = disable SQL-auth entirely
  }

  tags = local.tags
}

Entra ID admin and passwordless auth

The azuread_administrator block designates an Entra ID user or group as a database administrator. Its real payoff is azuread_authentication_only = true, which disables SQL authentication altogether — then there is no admin password to generate, store, or leak, and you drop the administrator_login* arguments entirely:

We use SQL auth in the demo precisely so the Key Vault + random_password flow is visible end to end. In production, Entra-only is the target: the app authenticates with its managed identity, mapped to a database user via CREATE USER [<identity>] FROM EXTERNAL PROVIDER, and no password exists anywhere.

Purchasing models: DTU vs vCore

The database SKU (sku_name) is the money and performance decision, and it comes in two purchasing models. Getting this table right saves the most rupees:

DTU bundles compute+IO+storage into one number (simple, less tunable); vCore separates compute and storage (transparent, tunable, supports serverless auto-pause and reserved-capacity discounts). For a first production DB, General Purpose vCore is the safe default; serverless GP is the cheapest thing that is still “real” for dev because it pauses when idle.

The database resource carries size, HA and retention:

resource "azurerm_mssql_database" "db" {
  name         = "appdb"
  server_id    = azurerm_mssql_server.sql.id      # v4 uses server_id (not server_name/rg)
  sku_name     = "S0"                             # DTU Standard S0 for the demo
  max_size_gb  = 250
  collation    = "SQL_Latin1_General_CP1_CI_AS"
  zone_redundant       = false                    # Premium/BC (and newer GP) only
  storage_account_type = "Geo"                    # Geo | Zone | Local | GeoZone backups

  short_term_retention_policy {
    retention_days = 7                            # 1-35 days of PITR
  }
  long_term_retention_policy {
    weekly_retention  = "P4W"                     # ISO-8601 durations
    monthly_retention = "P12M"
    yearly_retention  = "P5Y"
    week_of_year      = 1
  }

  tags = local.tags
}

Key database arguments and their traps:

Firewall rules — the resource that lies about its name

By default a SQL server accepts no connections. You open access with azurerm_mssql_firewall_rule — and its most common form does not mean what its IP range suggests:

# "Allow Azure services" — the special 0.0.0.0 / 0.0.0.0 rule
resource "azurerm_mssql_firewall_rule" "allow_azure" {
  name             = "AllowAzureServices"
  server_id        = azurerm_mssql_server.sql.id
  start_ip_address = "0.0.0.0"
  end_ip_address   = "0.0.0.0"
}

# Your workstation, so you can connect and verify
resource "azurerm_mssql_firewall_rule" "my_ip" {
  name             = "AdminWorkstation"
  server_id        = azurerm_mssql_server.sql.id
  start_ip_address = var.my_ip
  end_ip_address   = var.my_ip
}

⚠️ The 0.0.0.0–255.255.255.255 “open to the world” rule is the classic breach vector — someone adds it “just to test” and forgets. Because it is now in Terraform, a reviewer sees it in the PR diff and blocks it, and a plan flags it if it appears out of band. That reviewability is the whole point.

For production, prefer a private endpoint over any public firewall rule. The three connectivity postures:

The private-endpoint path (which ties into the private DNS section next):

resource "azurerm_private_endpoint" "sql" {
  name                = "pe-sql"
  location            = azurerm_resource_group.rg.location
  resource_group_name = azurerm_resource_group.rg.name
  subnet_id           = azurerm_subnet.data.id

  private_service_connection {
    name                           = "psc-sql"
    private_connection_resource_id = azurerm_mssql_server.sql.id
    subresource_names              = ["sqlServer"]
    is_manual_connection           = false
  }

  private_dns_zone_group {
    name                 = "sql"
    private_dns_zone_ids = [azurerm_private_dns_zone.sql.id]
  }
}

Connection strings

You rarely hardcode a connection string, but you often output one (marked sensitive) or feed one to an app setting. The forms differ by auth:

The recommended output uses Entra (no secret in the string), so it need not be sensitive — but if you ever emit the SQL-auth form, mark the output sensitive = true.

Azure DNS as code

Azure DNS hosts DNS zones on Azure’s global name-server fleet. Two flavors: public zones (internet-resolvable) and private zones (resolvable only inside linked VNets — the backbone of private-endpoint name resolution). Our demo publishes the app on a public zone.

The zone and its record resources:

resource "azurerm_dns_zone" "public" {
  name                = var.dns_zone_name        # e.g. "kv-demo.example"
  resource_group_name = azurerm_resource_group.rg.name
}

resource "azurerm_public_ip" "app" {
  name                = "pip-app"
  location            = azurerm_resource_group.rg.location
  resource_group_name = azurerm_resource_group.rg.name
  allocation_method   = "Static"
  sku                 = "Standard"
}

# Plain A record: static IP you manage
resource "azurerm_dns_a_record" "app" {
  name                = "app"                     # → app.kv-demo.example
  zone_name           = azurerm_dns_zone.public.name
  resource_group_name = azurerm_resource_group.rg.name
  ttl                 = 300
  records             = [azurerm_public_ip.app.ip_address]
}

Alias records vs plain records

A plain A record stores a literal IP. An alias record stores a target_resource_id and tracks the Azure resource — if the public IP changes, the record follows automatically. You set one or the other, never both:

# Alias A record: follows the Public IP automatically (omit `records`)
resource "azurerm_dns_a_record" "app_alias" {
  name                = "app"
  zone_name           = azurerm_dns_zone.public.name
  resource_group_name = azurerm_resource_group.rg.name
  ttl                 = 300
  target_resource_id  = azurerm_public_ip.app.id
}

The catch that trips everyone: a public Azure DNS zone only resolves once you delegate it — copy the four name servers Azure assigned (azurerm_dns_zone.public.name_servers) into your domain registrar’s NS records for that name. Terraform creates the zone; it cannot delegate a domain it doesn’t manage. Until you delegate, nslookup app.kv-demo.example returns nothing.

Public vs private DNS zones

For private endpoints, name resolution must return the private IP inside the VNet. That’s what a private DNS zone does — it overrides the public *.database.windows.net name with the endpoint’s private address:

resource "azurerm_private_dns_zone" "sql" {
  name                = "privatelink.database.windows.net"   # fixed name for SQL
  resource_group_name = azurerm_resource_group.rg.name
}

resource "azurerm_private_dns_zone_virtual_network_link" "sql" {
  name                  = "sql-link"
  resource_group_name   = azurerm_resource_group.rg.name
  private_dns_zone_name  = azurerm_private_dns_zone.sql.name
  virtual_network_id     = azurerm_virtual_network.vnet.id
  registration_enabled   = false
}

Each Azure service has a specific privatelink zone name (SQL is privatelink.database.windows.net, blob is privatelink.blob.core.windows.net, Key Vault is privatelink.vaultcore.azure.net). Use the exact string, or resolution silently returns the public IP.

Hands-on: build it with Terraform

Now the centerpiece — a complete directory you can copy, apply, verify, and destroy. It builds the full stack from the diagram: random_password → Key Vault (RBAC secret) → SQL server + database (firewalled, using the secret) → DNS A record.

Read it left→right: Terraform mints the password and (unavoidably) records it in state; Key Vault stores it under RBAC with purge protection; the SQL server and database consume it behind a firewall rule; and Azure DNS publishes the app’s hostname. The six badges are the six things that go wrong in production — we hit each one below.

The directory has five files:

versions.tf — providers pinned, remote state, and the KV feature that governs destroy:

terraform {
  required_version = ">= 1.6"

  required_providers {
    azurerm = { source = "hashicorp/azurerm", version = "~> 4.0" }
    azuread = { source = "hashicorp/azuread", version = "~> 3.0" }
    random  = { source = "hashicorp/random",  version = "~> 3.6" }
  }

  # Remote state — keep the (secret-bearing) state encrypted & RBAC-locked
  backend "azurerm" {
    resource_group_name  = "rg-tfstate"
    storage_account_name = "sttfstateprod001"
    container_name       = "tfstate"
    key                  = "keyvault-sql-dns.tfstate"
  }
}

provider "azurerm" {
  subscription_id = var.subscription_id     # required by azurerm v4
  features {
    key_vault {
      purge_soft_delete_on_destroy    = true
      recover_soft_deleted_key_vaults = true
    }
  }
}

provider "azuread" {}
provider "random" {}

variables.tf:

variable "subscription_id" { type = string }

variable "location" {
  type    = string
  default = "centralindia"
}

variable "prefix" {
  type    = string
  default = "kvdemo"
}

variable "sql_admin_login" {
  type    = string
  default = "sqladminuser"
}

variable "entra_admin_upn"       { type = string }   # you@tenant.onmicrosoft.com
variable "entra_admin_object_id" { type = string }   # objectId of that user/group

variable "my_ip"      { type = string }               # "203.0.113.10"
variable "my_ip_cidr" { type = string }               # "203.0.113.10/32"

variable "dns_zone_name" {
  type    = string
  default = "kv-demo.example"
}

main.tf — the whole stack:

data "azurerm_client_config" "current" {}

locals {
  tags = {
    project   = "kv-sql-dns-demo"
    managedBy = "terraform"
    env       = "demo"
  }
}

resource "azurerm_resource_group" "rg" {
  name     = "rg-${var.prefix}-demo"
  location = var.location
  tags     = local.tags
}

# ---------- Terraform generates the credential ----------
resource "random_password" "sql" {
  length           = 24
  special          = true
  override_special = "!#$%*-_=+"
  min_upper        = 2
  min_lower        = 2
  min_numeric      = 2
}

# ---------- Key Vault (RBAC) ----------
resource "azurerm_key_vault" "kv" {
  name                       = "kv-${var.prefix}-001"
  location                   = azurerm_resource_group.rg.location
  resource_group_name        = azurerm_resource_group.rg.name
  tenant_id                  = data.azurerm_client_config.current.tenant_id
  sku_name                   = "standard"
  rbac_authorization_enabled = true
  soft_delete_retention_days = 7        # min 7; keep short for a demo
  purge_protection_enabled   = false    # ⚠️ demo only — set true in prod

  network_acls {
    default_action = "Allow"            # demo; use "Deny" + ip_rules in prod
    bypass         = "AzureServices"
  }
  tags = local.tags
}

resource "azurerm_role_assignment" "kv_secrets_officer" {
  scope                = azurerm_key_vault.kv.id
  role_definition_name = "Key Vault Secrets Officer"
  principal_id         = data.azurerm_client_config.current.object_id
}

resource "azurerm_key_vault_secret" "sql_admin_password" {
  name         = "sql-admin-password"
  value        = random_password.sql.result
  key_vault_id = azurerm_key_vault.kv.id
  content_type = "password"
  depends_on   = [azurerm_role_assignment.kv_secrets_officer]  # RBAC lands first
}

# ---------- Azure SQL ----------
resource "azurerm_mssql_server" "sql" {
  name                          = "sql-${var.prefix}-001"
  resource_group_name           = azurerm_resource_group.rg.name
  location                      = azurerm_resource_group.rg.location
  version                       = "12.0"
  administrator_login           = var.sql_admin_login
  administrator_login_password  = random_password.sql.result   # ⚠️ in state
  minimum_tls_version           = "1.2"
  public_network_access_enabled = true

  azuread_administrator {
    login_username              = var.entra_admin_upn
    object_id                   = var.entra_admin_object_id
    tenant_id                   = data.azurerm_client_config.current.tenant_id
    azuread_authentication_only = false
  }
  tags = local.tags
}

resource "azurerm_mssql_database" "db" {
  name        = "appdb"
  server_id   = azurerm_mssql_server.sql.id
  sku_name    = "S0"
  max_size_gb = 250
  collation   = "SQL_Latin1_General_CP1_CI_AS"
  tags        = local.tags
}

resource "azurerm_mssql_firewall_rule" "allow_azure" {
  name             = "AllowAzureServices"
  server_id        = azurerm_mssql_server.sql.id
  start_ip_address = "0.0.0.0"
  end_ip_address   = "0.0.0.0"
}

resource "azurerm_mssql_firewall_rule" "my_ip" {
  name             = "AdminWorkstation"
  server_id        = azurerm_mssql_server.sql.id
  start_ip_address = var.my_ip
  end_ip_address   = var.my_ip
}

# ---------- Azure DNS ----------
resource "azurerm_public_ip" "app" {
  name                = "pip-${var.prefix}-app"
  location            = azurerm_resource_group.rg.location
  resource_group_name = azurerm_resource_group.rg.name
  allocation_method   = "Static"
  sku                 = "Standard"
}

resource "azurerm_dns_zone" "public" {
  name                = var.dns_zone_name
  resource_group_name = azurerm_resource_group.rg.name
}

resource "azurerm_dns_a_record" "app" {
  name                = "app"
  zone_name           = azurerm_dns_zone.public.name
  resource_group_name = azurerm_resource_group.rg.name
  ttl                 = 300
  target_resource_id  = azurerm_public_ip.app.id   # alias → tracks the IP
}

outputs.tf:

output "key_vault_uri" {
  value = azurerm_key_vault.kv.vault_uri
}

output "sql_server_fqdn" {
  value = azurerm_mssql_server.sql.fully_qualified_domain_name
}

output "app_hostname" {
  value = "app.${azurerm_dns_zone.public.name}"
}

output "zone_name_servers" {
  description = "Delegate these NS records at your registrar."
  value       = azurerm_dns_zone.public.name_servers
}

output "sql_connection_string" {
  description = "Entra (passwordless) connection string."
  value       = "Server=tcp:${azurerm_mssql_server.sql.fully_qualified_domain_name},1433;Database=${azurerm_mssql_database.db.name};Authentication=Active Directory Default;Encrypt=True;"
}

Run it, step by step

Step 1 — terraform init. Downloads the three providers and wires the Azure backend. Expect:

Initializing the backend...
Initializing provider plugins...
- Installing hashicorp/azurerm v4.x.x...
- Installing hashicorp/azuread v3.x.x...
- Installing hashicorp/random v3.x.x...
Terraform has been successfully initialized!

Step 2 — terraform plan -out tfplan. Read the summary line — it should propose creating everything and nothing destructive:

Plan: 11 to add, 0 to change, 0 to destroy.

Notice the random_password and the two administrator_login_password occurrences are shown as (sensitive value) — Terraform redacts them in plan output, but they still land in state. That redaction is cosmetic; the state protection (encrypted backend) is what matters.

Step 3 — terraform apply tfplan. Watch the ordering: random_password is instant; the role assignment applies and Terraform waits on it (depends_on) before writing the secret; the SQL server (a minute or two) precedes the database and firewall rules. On success:

Apply complete! Resources: 11 added, 0 changed, 0 destroyed.

Outputs:
app_hostname      = "app.kv-demo.example"
key_vault_uri     = "https://kv-kvdemo-001.vault.azure.net/"
sql_server_fqdn   = "sql-kvdemo-001.database.windows.net"
zone_name_servers = tolist(["ns1-01.azure-dns.com.", ...])

Step 4 — verify. Confirm each service independently, not just “apply succeeded”:

The -G flag on sqlcmd uses Entra auth (your logged-in identity, which is the azuread_administrator). If you’d rather test SQL auth, pull the password from Key Vault and pass -U sqladminuser -P "<pw>".

Step 5 — terraform destroy. ⚠️ This tears down real (billed) resources:

Plan: 0 to add, 0 to change, 11 to destroy.
...
Destroy complete! Resources: 11 destroyed.

Because we set purge_protection_enabled = false and soft_delete_retention_days = 7, the vault soft-deletes cleanly; its name is parked for 7 days. Had purge protection been true, destroy would remove the vault but you could not purge (or reuse the name) until the window elapsed — the deliberate trap we flagged. If you must reuse the name immediately in a demo, either change the vault name or manually recover/purge a non-protected soft-deleted vault with az keyvault purge --name <vault> (only works without purge protection).

Variables, outputs & making it reusable

The demo hardcodes structure but parameterizes the moving parts. To make it a reusable module, lift the resources into modules/data-tier/ and expose a tight input surface. A for_each over a map of databases turns “one DB” into “N DBs on the shared server”:

variable "databases" {
  type = map(object({
    sku_name    = string
    max_size_gb = number
  }))
  default = {
    appdb     = { sku_name = "S0", max_size_gb = 250 }
    reporting = { sku_name = "S1", max_size_gb = 500 }
  }
}

resource "azurerm_mssql_database" "db" {
  for_each    = var.databases
  name        = each.key
  server_id   = azurerm_mssql_server.sql.id
  sku_name    = each.value.sku_name
  max_size_gb = each.value.max_size_gb
  tags        = local.tags
}

A sensible module input surface:

You don’t have to write the vault or role-assignment plumbing yourself — Microsoft’s Azure Verified Modules (AVM) publish maintained, tested equivalents:

Use AVM for the vault (its RBAC/network handling is fiddly and worth not re-deriving); keep the SQL + DNS inline or in a thin local module where you want the retention/firewall logic visible in review. The trade-off is the usual one: registry modules buy you tested defaults and lose you some transparency. For how to author and version your own, see the module lessons in this course; the architecting-ladder lesson covers when a local module should graduate to a shared, versioned one.

Common mistakes and troubleshooting

Every row here is something that has cost a real engineer real time on exactly this stack:

Four of these deserve a sentence of context. Row 1 (the 403) is the number-one first-run failure on RBAC vaults: management-plane creation gives you nothing on the data plane, so Terraform can create the vault but not write a secret into it until the role assignment lands — and role assignments can take a beat to propagate, so the depends_on matters as much as the grant. Row 5 (secret in state) is not a bug to fix but a property to manage: any value Terraform must know is written to state, redaction in plan output notwithstanding; the mitigation is a locked-down backend plus, wherever possible, letting the app read Key Vault itself so Terraform never touches the plaintext. Row 6 (firewall) catches everyone once because the 0.0.0.0–0.0.0.0 rule reads like “all IPs” but means “allow other Azure services” — your laptop still needs its own rule. Row 9 (DNS) is the reminder that Terraform’s job ends at the zone: resolution only works after you delegate the zone’s name servers at whoever holds the parent domain.

Cost, cleanup & production notes

Left running, this demo is cheap but not free. Approximate India-region monthly costs:

⚠️ The SQL database is the meter that spins. If you’re only learning, use a serverless General Purpose SKU (GP_S_Gen5_1) with auto_pause_delay_in_minutes = 60 so it pauses when idle, or simply terraform destroy between sessions. Nothing here needs to run overnight.

To clean up: terraform destroy removes all 11 resources. The only residue is the soft-deleted vault name (parked for the retention window) and, if you enabled purge protection, the inability to purge until it expires. Everything else is gone and billing stops.

Five production hardening notes for this exact tier:

Two of those tie back to earlier lessons: the SQL server (and the app in front of it) authenticate with managed identity rather than secrets — the same pattern the App Service lesson uses to consume Key Vault via a slot-safe reference, and the Application Gateway / WAF lesson fronts with a public IP that this very DNS record would point at.

Cheat-sheet

Resources and their load-bearing arguments:

Command quick-reference:

Interview and exam questions

1. What’s the difference between RBAC authorization and access policies on a Key Vault, and which do you pick? Access policies are a vault-local permission list (azurerm_key_vault_access_policy); RBAC uses standard Azure role assignments (azurerm_role_assignment) and is Microsoft’s recommendation. Set rbac_authorization_enabled = true and grant Key Vault Secrets Officer/User. Don’t mix — with RBAC on, access policies are ignored.

2. Why does your first apply get a 403 writing a secret to a brand-new RBAC vault? Creating the vault (management plane) grants no data-plane rights. You must add a role assignment (Secrets Officer) for the Terraform identity and depends_on it from the secret, since role propagation isn’t instant.

3. A colleague says “we use Key Vault, so no secrets are in Terraform state.” True? Not necessarily. random_password.result and the azurerm_key_vault_secret data source both write plaintext into state. Key Vault helps only if the app reads it at runtime (KV reference / managed identity). If Terraform must know the value, protect the state backend.

4. What does the SQL firewall rule 0.0.0.0–0.0.0.0 actually allow? “Allow Azure services and resources” — not the whole internet. The internet-open rule is 0.0.0.0–255.255.255.255, which you should never use. Your own client needs a rule for its specific IP.

5. Explain purge protection and how it affects terraform destroy. With purge_protection_enabled = true, a deleted vault/secret can’t be hard-deleted before the soft-delete window elapses — even by an admin, even by the provider’s purge_soft_delete_on_destroy. Destroy removes the vault but the name is parked; it’s a one-way, compliance-grade control.

6. DTU vs vCore — how do you choose? DTU (Basic, S0–S12, P1–P15) bundles compute+IO+storage into one number: simple, less tunable. vCore (GP_, BC_, HS_, and serverless GP_S_) separates compute and storage: transparent, supports serverless auto-pause and reserved capacity. Default to GP vCore for production; serverless GP for cheap dev.

7. Your DNS A record resolves nowhere after a successful apply. Why? The public zone isn’t delegated. Azure gave you four name servers (name_servers output); you must set them as NS records at the domain’s registrar. Terraform can’t delegate a domain it doesn’t control.

8. Plain A record vs alias A record? Plain stores a literal IP in records; alias stores target_resource_id and auto-tracks the Azure resource’s current IP. Set one, never both. Use alias when pointing at an Azure Public IP, Traffic Manager, or Front Door.

9. How do you make this SQL stack passwordless? Set azuread_administrator { azuread_authentication_only = true } and drop administrator_login/administrator_login_password. The app connects with its managed identity, mapped to a DB user via CREATE USER [...] FROM EXTERNAL PROVIDER. No secret is generated, stored, or leaked.

10. (Associate-style) Why depends_on on the azurerm_key_vault_secret when it already references the vault by key_vault_id? The implicit dependency is on the vault, not on the role assignment that grants write permission. Without an explicit depends_on the role assignment, Terraform may try to write the secret before the grant lands → 403.

11. (Associate-style) You changed enable_rbac_authorization and got a deprecation warning. What now? azurerm v4 renamed it to rbac_authorization_enabled (old name removed in v5). Update the argument; the behavior is identical.

12. Where’s the right place to point this app’s public DNS record in production? Usually not a raw VM IP but a fronting layer — an Application Gateway/Load Balancer public IP or Front Door — via an alias record so it tracks the resource automatically. The DNS zone stays here; the target is whatever the ingress lesson provisions.

Key takeaways

• Wire the seams, not just the resources. The value of doing Key Vault + SQL + DNS together is learning the joints: the RBAC grant Terraform needs before it can write a secret, the password that flows from random_password into both the vault and the SQL server, and the DNS record that fronts it.
• RBAC over access policies. Set rbac_authorization_enabled = true, grant Secrets Officer to the writer and Secrets User to the app, and depends_on the grant from the secret. It’s the modern, consistent, recommended model — and enable_rbac_authorization is deprecated in v4.
• Assume the value lands in state. random_password.result and secret data sources put plaintext in state. Protect the backend (encrypted, RBAC-locked) and prefer app-side Key Vault references / managed identity so Terraform never handles the secret when it doesn’t have to.
• Purge protection is a one-way, compliance-grade trap. Great in production, annoying in demos — it blocks destroy-and-recreate until the soft-delete window passes. Know before you enable it.
• The SQL firewall rule doesn’t mean what it says. 0.0.0.0–0.0.0.0 is “Azure services,” not the internet; your client needs its own rule; and 0.0.0.0–255.255.255.255 is the breach vector a code-reviewed diff exists to catch. Prefer a private endpoint.
• DNS ends at delegation. Terraform creates the zone and records; resolution only works once you copy the zone’s name servers into the registrar. Alias records auto-track Azure resources; plain records don’t.
• Destroy to stop the meter. The S0 database is the cost; use serverless GP or terraform destroy between sessions to keep this an under-₹2,000 exercise.