Azure SQL Database (Logical Server)

Azure SQL Database is the fully-managed PaaS database engine. It is deployed as two resources: a logical server (azurerm_mssql_server) that acts as the administrative and connection endpoint (<name>.database.windows.net), and one or more databases (azurerm_mssql_database) hosted on it. This repo splits them into two modules — mssql_server and mssql_database — that mirror that relationship: databases are nested under each server in the mssql_server variable (the same pattern as subnets nested in networks), and the database module flattens them into composite <server>.<database> keys.

Note: The logical server has no compute cost — you are billed per database (or per elastic pool).

Logical server vs. Managed Instance: A logical server hosts isolated, database-scoped PaaS databases (no SQL Agent, cross-database queries, or instance-level features) and is not injected into a VNet. If you need near-100% SQL Server instance compatibility inside your VNet, use the mssql_managed_instance module instead.

Modules

Module	Resource	Purpose
`mssql_server`	`azurerm_mssql_server`	The logical server — endpoint, authentication, identity
`mssql_database`	`azurerm_mssql_database`	Databases nested under each server (the billable unit); fed the `mssql_server` variable and flattened to `<server>.<database>` keys

Basic Configuration

A server with its databases nested underneath:

mssql_server = {
    app = {
        resource_group = "hsw"

        administrator_login                 = "sqladmin"
        administrator_login_password_secret = "default_password"

        databases = {
            appdb = {
                sku_name     = "S0"
                collation    = "SQL_Latin1_General_CP1_CI_AS"
                license_type = "LicenseIncluded"
                max_size_gb  = 2
            }
        }
    }
}

resource_group is a key from the resource-group variable (not an ID/name); the module resolves the actual ID internally. Each database is keyed within its server's databases map and flattened to a composite <server>.<database> key (e.g. app.appdb).

Authentication

The SQL administrator password is never stored in tfvars or in Terraform state. administrator_login_password_secret names a key in the repo's local.secrets map (aggregated in src/locals.tf from TF_VAR_* environment variables); the resolved value feeds the provider's write-only administrator_login_password_wo argument, which Terraform does not persist to state:

$env:TF_VAR_default_password = "<strong-password>"

administrator_login                 = "sqladmin"
administrator_login_password_secret = "default_password"

Because a write-only value isn't tracked in state, rotate the password by changing the secret and bumping administrator_login_password_wo_version (default 1) — Terraform uses that version number to detect when to push an update. This path requires Terraform ≥ 1.11 (enforced by required_version in providers.tf).

Plan-time guard: if SQL auth is in use but the password resolves to null — the administrator_login_password_secret key is unset, or its TF_VAR_* env var isn't exported — the module fails during plan with a clear message rather than waiting for an apply-time API error. Set the env var, or enable Entra-only auth.

For Entra-only (Azure AD-only) authentication, omit the SQL administrator fields and set azuread_administrator with azuread_authentication_only = true:

azuread_administrator = {
    login_username              = "SQL Admins"
    object_id                   = "00000000-0000-0000-0000-000000000000"
    azuread_authentication_only = true
}

The azuread_administrator can be a user, group, or service principal: set object_id to that principal's object ID and login_username to a display label (for a group, its name). object_id is the immutable directory binding (ARM sid); login_username is the admin's display/login name (ARM login). A group is the recommended choice so admin membership is managed in Entra rather than in tfvars.

Directory Readers (Entra-side, outside this repo). Assigning the Entra admin on a logical server does not itself require Directory Readers. However, the server's identity needs the Directory Readers role — or the Microsoft Graph permissions User.Read.All, GroupMember.Read.All, Application.Read.All — to expand group membership (so members of a group admin actually get permissions) and to create Entra users/logins in the database. Without it, those operations fail. This is an Entra RBAC grant managed outside Terraform (per issue #111). See Directory Readers role in Microsoft Entra ID for Azure SQL.

Networking

A logical server is not injected into a subnet. This module defaults public_network_access_enabled to false (private), so reach it via a private endpoint — the private_endpoint module supports mssql_server as a private_connection_resource_type with the sqlServer sub-resource:

private_endpoints = {
    epic_sql = {
        resource_group = "hsw"
        subnet         = "hsw.hsw"
        private_service_connection = {
            private_connection_resource_type = "mssql_server"
            private_connection_resource      = "epic_sql" # mssql_server key
            subresource_names                = ["sqlServer"]
        }
        private_dns_zone_group = {
            private_dns_zones = ["privatelink.database.windows.net"]
        }
    }
}

Set public_network_access_enabled = true to expose the public endpoint instead.

mssql_server Parameters

Parameter	Type	Required	Default	Description
`resource_group`	string	Yes	-	Key of the resource group
`name`	string	No	Auto-generated	Server name (globally unique, lowercase). If omitted, uses the naming convention
`location`	string	No	Global location	Azure region
`existing`	bool	No	`false`	Reference an existing server via a data source instead of creating one
`version`	string	No	`12.0`	`12.0` (v12) or `2.0` (legacy v11)
`administrator_login`	string	No*	-	SQL administrator login. *Required unless Entra-only auth is enabled
`administrator_login_password_secret`	string	No*	-	Key into the `secrets` map for the admin password. *Required unless Entra-only auth is enabled
`connection_policy`	string	No	`Default`	`Default`, `Proxy`, or `Redirect`
`minimum_tls_version`	string	No	`1.2`	`1.0`, `1.1`, `1.2`, or `Disabled`
`public_network_access_enabled`	bool	No	`false`	Whether the public endpoint is enabled. Defaults to private — opt in to public explicitly
`outbound_network_restriction_enabled`	bool	No	`false`	Restrict outbound traffic from the server
`express_vulnerability_assessment_enabled`	bool	No	`false`	Enable Express Vulnerability Assessment
`primary_user_assigned_identity`	string	No	first `user_assigned_identities` entry	`user_assigned_identity` key for the server's primary identity. When `identity.type` includes `UserAssigned`, this is auto-derived from the first `user_assigned_identities` entry if omitted (the provider requires a primary then); set it explicitly to choose a different one
`transparent_data_encryption_key_vault_key_id`	string	No	-	Versioned Key Vault key URL for CMK TDE
`identity`	object	No	omitted	Managed identity (see Identity)
`azuread_administrator`	object	No	omitted	Entra administrator (see Authentication)
`tags`	map(string)	No	`{}`	Tags merged with default tags
`timeouts`	object	No	omitted	Custom operation timeouts

Identity

identity = {
    type                     = ["SystemAssigned", "UserAssigned"]
    user_assigned_identities = ["sql"]
}

Field	Type	Required	Default	Description
`type`	list(string)	Yes	-	Any combination of `SystemAssigned` and `UserAssigned`
`user_assigned_identities`	list(string)	No	`[]`	Keys from the `user_assigned_identity` variable

Note: When type includes UserAssigned, the server's primary user-assigned identity auto-derives from the first user_assigned_identities entry (the provider requires a primary then). Set primary_user_assigned_identity to choose a different one.

Database Parameters

Each entry in a server's databases map supports the following. The database key is combined with its server key into a composite <server>.<database> key internally (so a database is identified by its server, not a server field).

Parameter	Type	Required	Default	Description
`name`	string	No	Auto-generated (key)	Database name. If omitted, uses the naming convention
`existing`	bool	No	`false`	Reference an existing database via a data source
`sku_name`	string	No	Provider default	e.g. `Basic`, `S0`, `GP_S_Gen5_1`, `BC_Gen5_2`
`collation`	string	No	`SQL_Latin1_General_CP1_CI_AS`	Database collation. Forces replacement if changed
`license_type`	string	No	-	`LicenseIncluded` or `BasePrice`. *Not supported on serverless (`GP_S_`) SKUs** — omit it there
`max_size_gb`	number	No	-	Max size in GB (`0.1`, `0.5`, or integers ≥ 1)
`min_capacity`	number	No	-	Minimum vCores (serverless only)
`auto_pause_delay_in_minutes`	number	No	-	Auto-pause delay (serverless only); `-1` disables
`read_scale`	bool	No	-	Route read-intent connections to a replica (Premium/BC)
`read_replica_count`	number	No	-	Read replicas (Hyperscale only)
`zone_redundant`	bool	No	-	Spread replicas across zones (Premium/BC)
`storage_account_type`	string	No	`Zone`	Backup redundancy: `Geo`, `GeoZone`, `Local`, `Zone`
`enclave_type`	string	No	unset	`Default` or `VBS` (Always Encrypted with secure enclaves)
`geo_backup_enabled`	bool	No	`true`	Geo backup (DataWarehouse SKUs only)
`ledger_enabled`	bool	No	`false`	Ledger database. Forces replacement if changed
`maintenance_configuration_name`	string	No	`SQL_Default`	Public maintenance window
`create_mode`	string	No	`Default`	`Copy`, `PointInTimeRestore`, `Secondary`, etc.
`creation_source_database_id`	string	No	-	Source database ID for copy/restore/secondary modes
`elastic_pool_id`	string	No	-	Elastic pool ID to place the database in
`sample_name`	string	No	-	`AdventureWorksLT`
`secondary_type`	string	No	`Geo`	`Geo`, `Named`, or `Standby`
`transparent_data_encryption_enabled`	bool	No	`true`	Enable TDE
`transparent_data_encryption_key_vault_key_id`	string	No	-	CMK key URL for TDE
`transparent_data_encryption_key_automatic_rotation_enabled`	bool	No	`false`	Auto-rotate the TDE CMK
`short_term_retention_policy`	object	No	omitted	PITR retention (see below)
`long_term_retention_policy`	object	No	omitted	LTR retention (see below)
`threat_detection_policy`	object	No	omitted	Advanced Threat Protection settings
`identity`	object	No	omitted	User-assigned identity (see below)
`tags`	map(string)	No	`{}`	Tags merged with default tags
`timeouts`	object	No	omitted	Custom operation timeouts

short_term_retention_policy

short_term_retention_policy = {
    retention_days           = 7
    backup_interval_in_hours = 12
}

Field	Type	Required	Default	Description
`retention_days`	number	Yes	-	PITR retention, `1`–`35`
`backup_interval_in_hours`	number	No	`12`	`12` or `24`

long_term_retention_policy

long_term_retention_policy = {
    weekly_retention  = "P4W"
    monthly_retention = "P12M"
    yearly_retention  = "P5Y"
    week_of_year      = 1
}

Field	Type	Required	Default	Description
`weekly_retention`	string	No	`PT0S`	ISO 8601 duration, up to 520 weeks
`monthly_retention`	string	No	`PT0S`	ISO 8601 duration, up to 120 months
`yearly_retention`	string	No	`PT0S`	ISO 8601 duration, up to 10 years
`week_of_year`	number	No	-	Week (`1`–`52`) to take the yearly backup
`immutable_backups_enabled`	bool	No	`false`	Make LTR backups immutable

identity

identity = {
    type                     = "UserAssigned"
    user_assigned_identities = ["sql"]
}

Field	Type	Required	Default	Description
`type`	string	No	`UserAssigned`	Only `UserAssigned` is supported on a database
`user_assigned_identities`	list(string)	Yes	-	Keys from the `user_assigned_identity` variable

Data Loss Protection

The azurerm provider recommends a prevent_destroy lifecycle guard on databases. This module does not set it, because prevent_destroy cannot be driven from a variable and would conflict with this repo's nightly dev-destroy automation. For production databases, add the guard explicitly (or protect the resource through a state lock / policy) outside the module.

Referencing Existing Resources

Set existing = true on a server or database to reference it through a data source instead of creating it. For a server, only name and resource_group are needed; for a nested database, name plus existing = true (the server is implied by where the database is nested). The modules merge resource and data outputs, so downstream references work the same regardless of which path created the object.

Naming Convention

Names are generated as {name_prefix}<type>{key}{name_suffix}<type>:

name_prefixes = {
    mssql_server   = "prod-"
    mssql_database = ""
}

name_suffixes = {
    mssql_server   = "-eastus2-sql"
    mssql_database = ""
}

With a server keyed app and a database keyed appdb, the resulting names are prod-app-eastus2-sql and appdb.

Note: The server name must be globally unique, lowercase, 1–63 characters, alphanumerics and hyphens only, and cannot start or end with a hyphen. Database names are scoped to the server and are typically set explicitly via name.