9 August, 2021

Azure SQL with Service Prinicpals and Terraform

I wanted to automate the creation of Azure SQL databases with Azure AD Service Principal access, using Terraform.

As of September 2021, there is currently 1 manual step required, as this cannot be automated:

  1. ~~The SQL Server requires a Managed Identity, with the Directory Reader role on Azure AD. The azuread provider doesn't currently have a role_member resource (issue #50). A manual workaround is to add the managed identity to a group which has this role.~~ Fixed in v2.3.0!
  2. Once the database is created, you need to run a query to CREATE USER [${app.display_name}] FROM EXTERNAL PROVIDER; EXEC sp_addrolemember 'db_owner', [${app.display_name}]; (or some other DB role/permission). You might want to do this via sqlcmd.

Here's a fully worked example, which also outputs a database URL and Django MSSQL config.

data "azurerm_client_config" "current" {}

variable "db_name" {
  type    = string
  default = "testdb"
}

variable "env" {
  type    = string
  default = "test"
}

variable "location" {
  type    = string
  default = "Australia East"
}


resource "azurerm_resource_group" "rg" {
  name                 = "rg-sql-${var.env}-${lower(replace(var.location, " ", ""))}"
  location             = var.location
}

resource "random_id" "instance_id" {
  byte_length          = 4
}

resource "random_password" "admin" {
  length               = 24
  special              = false
}

resource "azurerm_mssql_server" "sql" {
  name                 = "sql-${var.env}-${lower(replace(var.location, " ", ""))}-${random_id.instance_id.hex}"
  location             = var.location
  resource_group_name  = azurerm_resource_group.rg.name

  version                      = "12.0"
  administrator_login          = "sa${random_id.instance_id.hex}"
  administrator_login_password = random_password.admin.result

  azuread_administrator {
    login_username     = "aadadmin"
    tenant_id          = data.azurerm_client_config.current.tenant_id
    object_id          = data.azurerm_client_config.current.object_id
  }

  identity {
    type               = "SystemAssigned"
  }
}

data "azuread_service_principal" "sql" {
  display_name         = azurerm_mssql_server.sql.name
}

resource "azuread_directory_role" "directory_reader" {
  display_name = "Directory Readers"
}

resource "azuread_directory_role_member" "db_directory_reader" {
  role_object_id   = azuread_directory_role.directory_reader.object_id
  member_object_id = data.azuread_service_principal.sql.object_id
}

resource "azurerm_mssql_database" "db" {
  name                 = var.db_name
  server_id            = azurerm_mssql_server.sql.id
  sku_name             = "Basic"
}

resource "azuread_application" "app" {
  display_name         = "${azurerm_mssql_server.sql.name}_${var.db_name}"
}

resource "azuread_service_principal" "app" {
  application_id       = azuread_application.app.application_id
}

resource "azuread_service_principal_password" "db_password" {
  service_principal_id = azuread_service_principal.app.object_id
}



output "sql_create_user" {
  value = "Run on ${azurerm_mssql_server.sql.name}/${azurerm_mssql_database.db.name}: CREATE USER [${azuread_application.app.display_name}] FROM EXTERNAL PROVIDER; EXEC sp_addrolemember 'db_owner', [${azuread_application.app.display_name}];"
}

output "database_url" {
  value = "mssql://${azuread_application.app.application_id}:${azuread_service_principal_password.db_password.value}@${azurerm_mssql_server.sql.fully_qualified_domain_name}:1433/${azurerm_mssql_database.db.name}"
  sensitive = true
}

output "django_mssql_config" {
  value = <<-EOT
  {'default':
      {
          'ATOMIC_REQUESTS': False,
          'AUTOCOMMIT': True,
          'CONN_MAX_AGE': 600,
          'ENGINE': 'mssql',
          'HOST': '${azurerm_mssql_server.sql.fully_qualified_domain_name}',
          'NAME': '${azurerm_mssql_database.db.name}',
          'OPTIONS': {
              'driver': 'ODBC Driver 17 for SQL Server',
              'extra_params': 'Authentication=ActiveDirectoryServicePrincipal',
              'sslmode': 'require'
          },
          'PASSWORD': '${azuread_service_principal_password.db_password.value}',
          'PORT': 1433,
          'USER': '${azuread_application.app.application_id}',
      }
  }
  EOT
  sensitive = true
}
Tagged: