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.

Auditing and Diagnostics

Two complementary mechanisms route SQL telemetry to a Log Analytics workspace, set at different scopes:

Server-level extended_auditing_policy (on the mssql_server entry) — captures security audit events for the whole server, including current and future databases. It provisions an azurerm_mssql_server_extended_auditing_policy with log_monitoring_enabled, and (when workspace is set) an azurerm_monitor_diagnostic_setting on the server's master database (<server_id>/databases/master) that forwards the audit log categories SQLSecurityAuditEvents and DevOpsOperationsAudit to the named workspace. Because auditing is configured at the server, you do not need to repeat it per database.
Per-database diagnostic_settings (on each databases entry) — routes operational logs for a single database (for example Errors, Deadlocks, Blocks, Timeouts) to a workspace, storage account, or event hub. These go through the shared monitor_diagnostic_setting module — see Monitor Diagnostic Setting for the full field reference.

A typical setup pairs server-wide auditing with per-database operational diagnostics, both pointed at the same shared workspace:

mssql_server = {
    epic_sql = {
        resource_group = "internal"

        administrator_login                 = "sqladmin"
        administrator_login_password_secret = "default_password"

        # Server-level audit -> shared Log Analytics workspace (covers current + future databases).
        extended_auditing_policy = {
            workspace = "shared"
        }

        databases = {
            epicsupport = {
                name        = "EpicSupport"
                sku_name    = "GP_S_Gen5_2"
                max_size_gb = 32

                # Operational diagnostics -> shared LAW. Security audit events are captured
                # server-wide via extended_auditing_policy above, not here.
                diagnostic_settings = {
                    law = {
                        workspace = "shared"
                        enabled_logs = {
                            categories = ["Errors", "Deadlocks", "Blocks", "Timeouts"]
                        }
                    }
                }
            }
        }
    }
}

workspace (in both blocks) is a key from the log_analytics_workspace variable — the module resolves the actual workspace ID internally.

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)
`extended_auditing_policy`	object	No	omitted	Server-level security auditing to Azure Monitor (see extended_auditing_policy)
`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.

extended_auditing_policy

Enables server-level security auditing and forwards the audit stream to a Log Analytics workspace. Setting this block provisions azurerm_mssql_server_extended_auditing_policy, enabled when log_monitoring_enabled is true (the default); when workspace is set it additionally provisions an azurerm_monitor_diagnostic_setting on the server's master database that routes the audit categories to that workspace. The policy applies to the whole server, so it covers current and future databases without per-database configuration. It applies only to servers this module manages; on an existing = true server the block is ignored, so the real owner's audit configuration is left untouched.

extended_auditing_policy = {
    workspace = "shared"
}

Field	Type	Required	Default	Description
`log_monitoring_enabled`	bool	No	`true`	Enables the policy and routes audit events to Azure Monitor. When `false`, the policy is created but disabled and no diagnostic setting is made
`workspace`	string	No	`null`	`log_analytics_workspace` key for the master-database audit routing. Required when `log_monitoring_enabled` is `true` (the default); may be `null` only when `log_monitoring_enabled = false`
`categories`	list(string)	No	`["SQLSecurityAuditEvents", "DevOpsOperationsAudit"]`	Audit log categories on the master-database diagnostic setting. Must list at least one when `workspace` is set

Note: The master-database diagnostic setting is named "<server_key>-server-audit". It is created only when workspace is set and log_monitoring_enabled is true. The audit policy itself is created for every managed server that sets the block (disabled when log_monitoring_enabled = false).

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)
`diagnostic_settings`	map(object)	No	`{}`	Operational log routing to Azure Monitor (see diagnostic_settings)
`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

diagnostic_settings

Routes the database's operational logs and metrics to one or more destinations. Each entry produces a separate azurerm_monitor_diagnostic_setting via the shared monitor_diagnostic_setting module, keyed mssql_database.<server>.<database>.<setting> internally. The block shape is identical across all resource types that support diagnostics — see Monitor Diagnostic Setting for the complete field reference (name, workspace, log_analytics_destination_type, storage_account, eventhub_*, partner_solution_id, enabled_logs, enabled_metrics).

diagnostic_settings = {
    law = {
        workspace = "shared"
        enabled_logs = {
            categories = ["Errors", "Deadlocks", "Blocks", "Timeouts"]
        }
    }
}

Note: Use this for operational telemetry. Security audit events (SQLSecurityAuditEvents, DevOpsOperationsAudit) are captured server-wide through the server's extended_auditing_policy, not per database — do not duplicate them here.

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.