Terraform Module: Azure SQL Server & Database — production-safe logical server with private access and Entra-only auth

Quick take — A reusable hashicorp/azurerm ~> 4.0 Terraform module for azurerm_mssql_server and azurerm_mssql_database: Entra-only auth, TLS 1.2+, auditing, short-term backup retention, and zone-redundant databases. New here? Jump to the Quickstart below to deploy it in minutes; read on for how it works and when to reach for it.

Quickstart (copy-paste)

Minimal, runnable configuration — drop this in a .tf file and fill in the "..." placeholders (each required input is commented):

provider "azurerm" {
  features {}
}

module "sql_database" {
  source = "git::https://dev.azure.com/teknohut/kloudvin/_git/terraform-modules//terraform-module-azure-sql-database?ref=v1.0.0"

  server_name         = "..."  # Globally unique logical server name (3-63 chars, lowerc…
  database_name       = "..."  # Database name (1-128 chars).
  resource_group_name = "..."  # Resource group for the server and database.
  location            = "..."  # Azure region.
  azuread_admin       = {}     # Entra administrator (`login_username`, `object_id`, opt…
}

Then terraform init && terraform apply. Every other input has a sensible default — see Inputs below to override behaviour.

What this module is

Azure SQL Database is a fully managed PaaS relational engine. In Terraform terms it is split into two resources that almost always travel together: azurerm_mssql_server — the logical server that owns authentication, the public network toggle, TLS floor, and the Entra (Azure AD) administrator — and azurerm_mssql_database, which carries the SKU, storage, collation, and backup behaviour. A logical server is not a VM; it is a connectivity and identity boundary, and one server can host many databases that share its firewall and auth posture.

Wrapping the pair in a module matters because the defaults are dangerous. A bare azurerm_mssql_server accepts SQL logins, allows public network access, and defaults minimum_tls_version to 1.2 only if you remember to set it. This module flips every one of those to the safe value, makes Entra-only authentication a first-class toggle, and bolts on the production sub-resources you would otherwise forget: a server-level auditing policy, short-term (PITR) backup retention, and an optional firewall rule. You get a single, reviewable input surface instead of five resources whose interdependencies (the server identity must exist before the audit policy references storage; Entra-only auth must be off-then-on around the admin block) are easy to wire incorrectly.

When to use it

• You are standing up an OLTP database for an app and want it private, Entra-authenticated, and audited from day one rather than retrofitting compliance later.
• You run many databases across environments and need every logical server to share an identical security baseline (TLS 1.2, public access off, no SQL admin password lying around).
• You want PITR retention and (optionally) long-term retention encoded as code so backup posture survives drift and audits.
• You are not a fit if you need a Managed Instance (use azurerm_mssql_managed_instance), an elastic pool as the primary unit (add it alongside), or a serverless free-tier experiment where the safety rails just slow you down.

Module structure

terraform-module-azure-sql-database/
├── versions.tf
├── main.tf
├── variables.tf
└── outputs.tf

versions.tf

terraform {
  required_version = ">= 1.6.0"

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

main.tf

# A SQL-auth admin password is only needed when Entra-only auth is disabled.
# Generate one so we never require the caller to pass a plaintext secret.
resource "random_password" "sql_admin" {
  count            = var.azuread_authentication_only ? 0 : 1
  length           = 24
  special          = true
  override_special = "!#$%&*()-_=+[]{}<>:?"
}

resource "azurerm_mssql_server" "this" {
  name                = var.server_name
  resource_group_name = var.resource_group_name
  location            = var.location
  version             = "12.0" # the only valid value for Azure SQL Database logical servers

  # SQL-auth admin is omitted entirely when Entra-only auth is on.
  administrator_login          = var.azuread_authentication_only ? null : var.administrator_login
  administrator_login_password = var.azuread_authentication_only ? null : random_password.sql_admin[0].result

  minimum_tls_version           = var.minimum_tls_version
  public_network_access_enabled = var.public_network_access_enabled

  azuread_administrator {
    login_username              = var.azuread_admin.login_username
    object_id                   = var.azuread_admin.object_id
    tenant_id                   = var.azuread_admin.tenant_id
    azuread_authentication_only = var.azuread_authentication_only
  }

  identity {
    type = "SystemAssigned"
  }

  tags = var.tags
}

resource "azurerm_mssql_database" "this" {
  name      = var.database_name
  server_id = azurerm_mssql_server.this.id

  sku_name     = var.sku_name
  max_size_gb  = var.max_size_gb
  collation    = var.collation
  license_type = var.license_type

  # vCore-tier reliability: zone redundancy is only valid on supported SKUs.
  zone_redundant = var.zone_redundant

  storage_account_type = var.backup_storage_redundancy

  short_term_retention_policy {
    retention_days = var.short_term_retention_days
  }

  dynamic "long_term_retention_policy" {
    for_each = var.long_term_retention == null ? [] : [var.long_term_retention]
    content {
      weekly_retention  = long_term_retention_policy.value.weekly_retention
      monthly_retention = long_term_retention_policy.value.monthly_retention
      yearly_retention  = long_term_retention_policy.value.yearly_retention
      week_of_year      = long_term_retention_policy.value.week_of_year
    }
  }

  tags = var.tags
}

# Optional single firewall rule (e.g. an egress NAT range). Prefer Private
# Endpoints in production; this exists for break-glass / CI ranges.
resource "azurerm_mssql_firewall_rule" "this" {
  for_each         = var.firewall_rules
  name             = each.key
  server_id        = azurerm_mssql_server.this.id
  start_ip_address = each.value.start_ip_address
  end_ip_address   = each.value.end_ip_address
}

# Server-level auditing to a storage account. Database-level auditing
# inherits from this unless explicitly overridden.
resource "azurerm_mssql_server_extended_auditing_policy" "this" {
  count                                   = var.audit_storage_endpoint == null ? 0 : 1
  server_id                               = azurerm_mssql_server.this.id
  storage_endpoint                        = var.audit_storage_endpoint
  storage_account_access_key              = var.audit_storage_account_access_key
  storage_account_access_key_is_secondary = false
  retention_in_days                       = var.audit_retention_days
}

variables.tf

variable "server_name" {
  type        = string
  description = "Globally unique name of the logical SQL server (becomes <name>.database.windows.net)."

  validation {
    condition     = can(regex("^[a-z0-9][a-z0-9-]{1,61}[a-z0-9]$", var.server_name))
    error_message = "server_name must be 3-63 chars, lowercase letters, numbers and hyphens, and may not start or end with a hyphen."
  }
}

variable "database_name" {
  type        = string
  description = "Name of the database created on the logical server."

  validation {
    condition     = length(var.database_name) >= 1 && length(var.database_name) <= 128
    error_message = "database_name must be between 1 and 128 characters."
  }
}

variable "resource_group_name" {
  type        = string
  description = "Resource group that will contain the server and database."
}

variable "location" {
  type        = string
  description = "Azure region (e.g. centralindia, eastus)."
}

variable "administrator_login" {
  type        = string
  default     = "sqladmin"
  description = "SQL-auth admin username. Ignored when azuread_authentication_only is true."
}

variable "azuread_admin" {
  type = object({
    login_username = string
    object_id      = string
    tenant_id      = optional(string)
  })
  description = "Entra ID administrator for the server (user or group object_id is recommended over an individual)."
}

variable "azuread_authentication_only" {
  type        = bool
  default     = true
  description = "When true, disables SQL authentication entirely and no admin password is generated."
}

variable "minimum_tls_version" {
  type        = string
  default     = "1.2"
  description = "Minimum TLS version accepted by the server."

  validation {
    condition     = contains(["1.2", "1.3"], var.minimum_tls_version)
    error_message = "minimum_tls_version must be 1.2 or 1.3 for production SQL servers."
  }
}

variable "public_network_access_enabled" {
  type        = bool
  default     = false
  description = "Allow access over the public endpoint. Keep false and use Private Endpoints in production."
}

variable "sku_name" {
  type        = string
  default     = "GP_S_Gen5_2"
  description = "Database SKU (e.g. GP_S_Gen5_2 serverless, GP_Gen5_4 provisioned, S0 DTU)."
}

variable "max_size_gb" {
  type        = number
  default     = 32
  description = "Maximum database size in GB."

  validation {
    condition     = var.max_size_gb >= 1 && var.max_size_gb <= 4096
    error_message = "max_size_gb must be between 1 and 4096."
  }
}

variable "collation" {
  type        = string
  default     = "SQL_Latin1_General_CP1_CI_AS"
  description = "Database collation. Cannot be changed after creation."
}

variable "license_type" {
  type        = string
  default     = null
  description = "BasePrice (Azure Hybrid Benefit) or LicenseIncluded. Null lets Azure pick the default for the SKU."

  validation {
    condition     = var.license_type == null || contains(["BasePrice", "LicenseIncluded"], var.license_type)
    error_message = "license_type must be BasePrice, LicenseIncluded, or null."
  }
}

variable "zone_redundant" {
  type        = bool
  default     = false
  description = "Enable zone redundancy (only valid on supported vCore/Premium SKUs)."
}

variable "backup_storage_redundancy" {
  type        = string
  default     = "Zone"
  description = "Backup storage redundancy: Local, Zone, or Geo."

  validation {
    condition     = contains(["Local", "Zone", "Geo"], var.backup_storage_redundancy)
    error_message = "backup_storage_redundancy must be Local, Zone, or Geo."
  }
}

variable "short_term_retention_days" {
  type        = number
  default     = 7
  description = "Point-in-time restore (PITR) retention window in days (1-35)."

  validation {
    condition     = var.short_term_retention_days >= 1 && var.short_term_retention_days <= 35
    error_message = "short_term_retention_days must be between 1 and 35."
  }
}

variable "long_term_retention" {
  type = object({
    weekly_retention  = optional(string, "PT0S")
    monthly_retention = optional(string, "PT0S")
    yearly_retention  = optional(string, "PT0S")
    week_of_year      = optional(number, 1)
  })
  default     = null
  description = "Optional LTR policy using ISO-8601 durations (e.g. weekly_retention = P4W). Null disables LTR."
}

variable "firewall_rules" {
  type = map(object({
    start_ip_address = string
    end_ip_address   = string
  }))
  default     = {}
  description = "Map of firewall rule name => IP range. Use 0.0.0.0/0.0.0.0 to allow Azure services. Prefer Private Endpoints."
}

variable "audit_storage_endpoint" {
  type        = string
  default     = null
  description = "Blob endpoint of the storage account for auditing (e.g. https://acct.blob.core.windows.net/). Null disables auditing."
}

variable "audit_storage_account_access_key" {
  type        = string
  default     = null
  sensitive   = true
  description = "Access key for the audit storage account. Required when audit_storage_endpoint is set."
}

variable "audit_retention_days" {
  type        = number
  default     = 90
  description = "How long audit logs are retained in storage (0 = unlimited)."
}

variable "tags" {
  type        = map(string)
  default     = {}
  description = "Tags applied to the server and database."
}

outputs.tf

output "server_id" {
  description = "Resource ID of the logical SQL server."
  value       = azurerm_mssql_server.this.id
}

output "server_name" {
  description = "Name of the logical SQL server."
  value       = azurerm_mssql_server.this.name
}

output "server_fqdn" {
  description = "Fully qualified domain name of the server (<name>.database.windows.net)."
  value       = azurerm_mssql_server.this.fully_qualified_domain_name
}

output "server_identity_principal_id" {
  description = "Principal ID of the server's system-assigned managed identity (for granting access to Key Vault / storage)."
  value       = azurerm_mssql_server.this.identity[0].principal_id
}

output "database_id" {
  description = "Resource ID of the database."
  value       = azurerm_mssql_database.this.id
}

output "database_name" {
  description = "Name of the database."
  value       = azurerm_mssql_database.this.name
}

output "ado_net_connection_string" {
  description = "ADO.NET connection string using Entra access token auth (no password embedded)."
  value       = "Server=tcp:${azurerm_mssql_server.this.fully_qualified_domain_name},1433;Database=${azurerm_mssql_database.this.name};Authentication=Active Directory Default;Encrypt=True;"
}

output "administrator_login_password" {
  description = "Generated SQL-auth admin password (empty when Entra-only auth is enabled)."
  value       = var.azuread_authentication_only ? "" : random_password.sql_admin[0].result
  sensitive   = true
}

How to use it

module "sql_server_database" {
  source = "git::https://dev.azure.com/teknohut/kloudvin/_git/terraform-modules//terraform-module-azure-sql-database?ref=v1.0.0"

  server_name         = "kv-orders-prod-cin"
  database_name       = "orders"
  resource_group_name = azurerm_resource_group.data.name
  location            = azurerm_resource_group.data.location

  # Entra-only: no SQL password anywhere in state or pipeline.
  azuread_authentication_only = true
  azuread_admin = {
    login_username = "sg-sql-admins-prod"
    object_id      = data.azuread_group.sql_admins.object_id
    tenant_id      = data.azurerm_client_config.current.tenant_id
  }

  # General Purpose provisioned, zone-redundant, geo backups.
  sku_name                  = "GP_Gen5_4"
  max_size_gb               = 128
  zone_redundant            = true
  backup_storage_redundancy = "Geo"
  short_term_retention_days = 14

  long_term_retention = {
    weekly_retention  = "P4W"
    monthly_retention = "P12M"
    yearly_retention  = "P7Y"
    week_of_year      = 1
  }

  audit_storage_endpoint           = azurerm_storage_account.audit.primary_blob_endpoint
  audit_storage_account_access_key = azurerm_storage_account.audit.primary_access_key
  audit_retention_days             = 365

  tags = {
    env   = "prod"
    app   = "orders"
    owner = "data-platform"
  }
}

# Downstream: wire a Private Endpoint to the logical server using its ID,
# so the app reaches the database without any public exposure.
resource "azurerm_private_endpoint" "sql" {
  name                = "pe-kv-orders-prod"
  resource_group_name = azurerm_resource_group.data.name
  location            = azurerm_resource_group.data.location
  subnet_id           = azurerm_subnet.data.id

  private_service_connection {
    name                           = "psc-sql"
    private_connection_resource_id = module.sql_server_database.server_id
    subresource_names              = ["sqlServer"]
    is_manual_connection           = false
  }
}

# Downstream: grant the app's identity rights by handing the FQDN to config.
resource "azurerm_app_configuration_key" "sql_fqdn" {
  configuration_store_id = azurerm_app_configuration.shared.id
  key                    = "Orders:SqlServerFqdn"
  value                  = module.sql_server_database.server_fqdn
}

With Terragrunt

Terragrunt keeps this module DRY across environments — define the backend and provider once in a root config, then a thin terragrunt.hcl per environment supplies only the inputs that differ.

1. Root config — live/terragrunt.hcl (inherited by every module):

remote_state {
  backend = "azurerm"
  generate = { path = "backend.tf", if_exists = "overwrite" }
  config = {
    # ...azurerm state bucket/container + key per path...
  }
}

2. Module config — live/prod/sql_database/terragrunt.hcl:

include "root" {
  path = find_in_parent_folders()
}

terraform {
  source = "git::https://dev.azure.com/teknohut/kloudvin/_git/terraform-modules//terraform-module-azure-sql-database?ref=v1.0.0"
}

inputs = {
  server_name = "..."
  database_name = "..."
  resource_group_name = "..."
  location = "..."
  azuread_admin = {}
}

3. Deploy one environment, or roll out all modules together:

cd live/prod/sql_database && terragrunt apply        # this module
terragrunt run-all apply                      # every module under live/prod

Why Terragrunt here: the backend and provider live in one place instead of being copy-pasted into every module; inputs is overridden per environment (dev / stage / prod) without forking the module; and run-all orchestrates dependencies across modules. Reach for it once you have more than one environment or more than a handful of modules — for a single stack, the plain Quickstart above is enough.

Inputs

Outputs

Enterprise scenario

A retail platform team runs the orders OLTP database for their checkout service in centralindia. They consume this module from their landing-zone Terraform with azuread_authentication_only = true, a GP_Gen5_4 zone-redundant database, Geo backup redundancy, and a 7-year long_term_retention policy to satisfy financial-records audit requirements. Because public_network_access_enabled defaults to false, the only path to the server is the Private Endpoint they wire from server_id, and the application pods authenticate with their workload identity using the ado_net_connection_string output — so no SQL password ever exists in Key Vault, pipeline variables, or Terraform state.

Best practices

• Prefer Entra-only auth and managed identities. Keep azuread_authentication_only = true so no SQL password is generated, set the azuread_admin to a group (not a person), and let apps connect via Active Directory Default tokens. This removes the single most-leaked secret in the SQL world.
• Stay private by default. Leave public_network_access_enabled = false and reach the server through a Private Endpoint on the sqlServer subresource; only add firewall_rules for genuine break-glass or CI egress ranges, never 0.0.0.0.
• Right-size the SKU and use serverless for spiky workloads. Serverless (GP_S_*) auto-pauses idle databases to cut cost in dev/test, while provisioned vCore gives predictable latency for prod; apply Azure Hybrid Benefit via license_type = "BasePrice" if you own SQL Server core licenses.
• Match backup posture to RPO and compliance. Set backup_storage_redundancy = "Geo" and a longer short_term_retention_days for production, and encode long_term_retention in ISO-8601 so weekly/monthly/yearly backups are reproducible rather than a click in the portal.
• Turn on zone redundancy for prod reliability. zone_redundant = true on a supported SKU survives a single-AZ failure with no data loss; it is one of the cheapest reliability wins on Azure SQL.
• Name and tag deterministically. Use a <org>-<workload>-<env>-<region> server name (lowercase, hyphenated) so the global database.windows.net namespace stays collision-free, and tag every resource with env/app/owner for cost allocation and audit traceability.