Creating SQL databases - Terraform

To create SQL databases the following resources are required:

  • An Entra ID security group for the System Admins.
  • The SQL server
  • One or more firewall rules
  • One or more databases

Security group

The security group can either be created manually or with Terraform. To create with Terraform the service principal needs to have API access to User.ReadWriteAll on the Microsoft Graph.

data "azuread_client_config" "current" {}

resource "azuread_group" "sg_db_admin" {
  display_name     = "SQL Server Administrators - ${var.environment_name}"
  owners           = [data.azuread_client_config.current.object_id]
  security_enabled = true
  description      = "Add users to this group to administer SQL server"
}

SQL server

Unless there is a legacy app which requires access to the SQL server via username/password, the default standard should be to create the database server to only support Entra ID users. This no longer requires an admin password to be provided, however, if the admin password is changed the SQL server has to be deleted and recreated.

The azuread_authentication_only parameter prevents the use of SQL accounts.

# Create SQL server and database (with AD group for Admin access)
resource "azurerm_mssql_server" "sql_server" {
  name                         = var.sql_server_name
  resource_group_name          = azurerm_resource_group.rg.name
  location                     = azurerm_resource_group.rg.location
  version                      = "12.0"
  administrator_login          = "sqladmin"
  administrator_login_password = var.sql_admin_password

  azuread_administrator {
    login_username = var.sql_entraid_admin_username
    object_id      = var.sql_entraid_admin_object_id
    azuread_authentication_only = true
  }
}

Firewall rules

resource "azurerm_mssql_firewall_rule" "fw_rule1" {
  name             = "Name of user"
  server_id        = azurerm_mssql_server.sql_server.id
  start_ip_address = "IP address"
  end_ip_address   = "IP address"
}

Databases

The final stage is to create the database. The most critical parameter on a database is lifecyle -> prevent_destroy - this helps to prevent accidental deletion of the database in the event of the resource being removed from the terraform file (or an error - like the admin password not being provided causing an attempt to delete and recreate the SQL server).

resource "azurerm_mssql_database" "sqldb_main" {
  name                        = "sqldb-syn-um-main"
  server_id                   = azurerm_mssql_server.sql_server.id
  collation                   = "SQL_Latin1_General_CP1_CI_AS"
  auto_pause_delay_in_minutes = 0
  sku_name                    = "S0"

  lifecycle {
    prevent_destroy = true
  }
}