Configure secret-less connection from App Services to Azure Sql via terraform

It's been a while since we can connect App services to Azure Sql in a secret-less fashion, using managed service identity (MSI for brevity from now onwards).

The configuration is a bit more complicated than connecting to other Azure services e.g. Azure Storage Account because it involves running some queries on the Azure Sql database in order to create the user and grant them the required privileges, for more info see the tutorial here.

In order to be able to connect to Azure Sql with MSI we need to configure few things:

Grant database access to Azure AD users
Turn on MSI on the App Service
Create a user for the service principal and grant the required privileges in the database(s)
Change the connection string to use the new authentication mode

This is quite easy to do manually, but if you are using IaC, then manual changes are a no go.

Configure all of this in terraform was a non trivial task and took me quite a bit to understand the ins and outs and since I wasn't able to find much documentation online, I decided to put together this blog post.

Step 1: Grant database access to Azure AD users

In order to be able to connect to Azure Sql with a managed identity, we need to configure the Azure Sql Server to allow Azure AD authentication, you can read more on the subject here.

Via terraform we can configure it adding the azuread_administrator block on the Azure Sql Server resource as shown below:

resource "azurerm_mssql_server" "sql" {
  ...

  azuread_administrator {
    login_username = var.sql_server_ad_admin_username
    object_id      = var.sql_server_ad_admin_object_id
  }

  ...
}

Here we're passing in the user name and the object id of the Azure AD User or Azure AD Group that we want to configure as the server admin.

Step 2: Turn on MSI on the App Service

In order to create a MSI for our App Service, we need to configure the identity block to SytemAssigned as shown below.

Please note that there's a small catch in terraform about turning on managed identity for an existing App Service, essentially you can't use it until it's there, so you may need to run terraform apply twice, one to turn on MSI, and then the second time to grant some privileges to it.

You can find more details on an issue I opened in the azurerm terraform provider here.

resource "azurerm_app_service" "web" {
  name                = "${var.prefix}-web-backend-${var.env}"
  location            = azurerm_resource_group.backend.location
  resource_group_name = azurerm_resource_group.backend.name
  ...

  identity {
    type = "SystemAssigned"
  }

  ...
}

Step 3: Create a user for the service principal and grant the required privileges in the database(s)

This is the tricky part, that I struggled to automate because it requires running a couple of sql commands in the Sql Server database, as suggested in this article here.

The sql you need to run creates a user and grants it the required privileges as shown below.

CREATE USER [ServicePrincipalName] FROM EXTERNAL PROVIDER;
GO
ALTER ROLE db_datareader ADD MEMBER [ServicePrincipalName];
ALTER ROLE db_datawriter ADD MEMBER [ServicePrincipalName];

The point of this article though is to take care of this via terraform, in order to do so we need to:

Get the current Azure tenant id
Read the App Service service principal from Azure AD
Create the user and grant it required privileges in the database

Let's see how we can achieve this with terraform:

Get current tenant id

This is easy, we can use a built-in terraform data source to access it:

data "azurerm_client_config" "current" {}

Read the App Service service principal from Azure AD

Here we can once again use a terraform data source to get access to the application_id property of the generated MSI as follows:

data "azuread_service_principal" "web_managed_identity" {
  object_id = azurerm_app_service.web.identity.0.principal_id
}

Create the user and grant it required privileges

In order to achieve this step, we need to use a 3rd party provider called mssql_user, you can find it on the terraform registry here

The only catch here is that you need to specify an Azure AD credential to connect to the Azure Sql database, so you can use the user we configured in the step 1 above.
If you used an Azure AD group instead you may create a service principal, add it to the group in Azure AD and use it's client_id/client_secret to connect to the database.

resource "mssql_user" "web" {
  server {
    host = azurerm_mssql_server.sql.fully_qualified_domain_name
    azure_login {
      tenant_id     = data.azurerm_client_config.current.tenant_id
      client_id     = var.sql_sp_client_id
      client_secret = var.sql_sp_client_secret
    }
  }
  object_id = data.azuread_service_principal.web_managed_identity.application_id
  database  = var.database_name
  username  = azurerm_app_service.web.name
  roles     = ["db_datareader", "db_datawriter"]
}

Here we need to specify few things:

The FQDN name of the Azure Sql Server
How to login to the database (I'm using a service principal that's been added to the Azure AD group that's set as the Azure Sql Admin)
What's the object id of the service principal we are granting access to
What's the name of the service principal
What roles we want to assign to it

Step 4: Change the connection string to use the new authentication mode

Note that you need to reference System.Data.SqlClient version 3 or greater for dotnet core, older versions doesn't support Authentication=Active Directory Default

locals {
  connection_string = "Server=${var.prefix}-sql-${var.env}.database.windows.net; Authentication=Active Directory Default; Database=${var.database_name};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Persist Security Info=False;"
}

and then we just need to set this new connection string on the App Service as follows:

resource "azurerm_app_service" "web" {
  ...

  app_settings = {
    "ConnectionStrings__Database" = local.connection_string
    ...
  }

  ...
}

As a last step, I'm showing the terraform configuration to include all the required providers used to achieve this:

terraform {
  required_providers {
    azurerm = {
      source  = "hashicorp/azurerm"
      version = "~> 2.84"
    }
    azuread = {
      source  = "hashicorp/azuread"
      version = "~> 2.14.0"
    }
    mssql = {
      source  = "betr-io/mssql"
      version = "0.2.4"
    }
  }
}