Azure Azure - Terraform templates

MS SQL Server

Last updated: November 28, 2024
In Azure, to create an MS SQL Database, you need to create two components, an MS SQL Server and an MS SQL Database. The server is like a "container" for databases, then the database resource is where the data is stored. 

MS SQL Server


To create an MS SQL Server use the following snippet:

variable "admin_password" {
  type        = string
  sensitive   = true
  default     = null
}

resource "random_password" "admin_password" {
  count       = var.admin_password == null ? 1 : 0
  length      = 20
  special     = true
  min_numeric = 1
  min_upper   = 1
  min_lower   = 1
  min_special = 1
}

locals {
  admin_password = try(random_password.admin_password[0].result, var.admin_password)
}

resource "azurerm_mssql_server" "server" {
  name                          = "${lower(var.environment_prefix)}-${lower(var.solution_prefix)}${lower(var.customer_prefix)}"
  resource_group_name           = azurerm_resource_group.rg.name
  location                      = azurerm_resource_group.rg.location
  administrator_login           = "${var.environment_prefix}_${var.solution_prefix}_admin"
  administrator_login_password  = local.admin_password
  version                       = "12.0"
  tags                          = var.tags
}

output "admin_password" {
  value = nonsensitive(local.admin_password)
}


There are a couple of things happening here. First, you see that the code is generating a random password for the MS SQL Server login. The random password is totally optional part, you can just write your own password or provide it as a variable value, but unless you have a specific need, I'd keep the random generation. You can use the login and password to log into the SQL Server.

Note: the output command will write on console the admin password. Remove it if you don't need to see it every time, or remove the nonsensitive command so it is not visible

Enabling Entra authentication


In two steps, you can enable your azure resources (e.g. Functions, Web Apps, etc.) to be able to authenticate to the MS SQL Server with a system managed or user assigned managed identity.

Step 1: Set the Entra Admin of the MS SQL Server
 
Inside the azurerm_mssql_server resource, add the azuread_administrator configuration; this is setting the Entra Admin of the server (Entra - formerly known as Active Directory). 

resource "azurerm_mssql_server" "server" {
  name                          = "${lower(var.environment_prefix)}-${lower(var.solution_prefix)}${lower(var.customer_prefix)}"
  resource_group_name           = azurerm_resource_group.rg.name
  location                      = azurerm_resource_group.rg.location
  administrator_login           = "${var.environment_prefix}_${var.solution_prefix}_admin"
  administrator_login_password  = local.admin_password
  version                       = "12.0"
  tags                          = var.tags

  azuread_administrator {
    login_username = "ENTRA_USERNAME"
    object_id      = "ENTRA_USER_OBJECT_ID"
  }
}

The Entra user would need to be a user that you created in your Entra directory. You would need this user to administer the server as well as to assign permissions for your resources.

Step 2: Assign the permissions to your resources

As next step, you need to log into SQL Database with your Entra Admin account and assign necessary permissions to the specific identity. 

Example: Give access to a web app with identity WebEntraUser to your database.

1. Go to the database resource in azure portal
2. Go to Query Editor 
3. Log in with your Entra Admin account (you might need to add your IP to the firewall of MS SQL Server at Network section)
4. Run the code below

CREATE USER WebEntraUser FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER WebEntraUser;
ALTER ROLE db_datawriter ADD MEMBER WebEntraUser;
ALTER ROLE db_ddladmin ADD MEMBER WebEntraUser;
GO

Note: Replace WebEntraUser with the name of the service account or user managed identity of the resource you want to give permission to the database.
Note: Your WebApp needs to have WebEntraUser set as its identity for this to work. If your web app is using a system assigned identity, then this is the name of your web app.
Note: Check out the tutorial from Microsoft on using managed identities for your webapp - database connection, link below in the further reading part.

Further reading