DEV Community

Cover image for Configure secret-less connection from App Services to Azure Sql via terraform
Massimiliano Donini
Massimiliano Donini

Posted on

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
  }

  ...
}
Enter fullscreen mode Exit fullscreen mode

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"
  }

  ...
}
Enter fullscreen mode Exit fullscreen mode

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];
Enter fullscreen mode Exit fullscreen mode

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

  1. Get the current Azure tenant id
  2. Read the App Service service principal from Azure AD
  3. 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" {}
Enter fullscreen mode Exit fullscreen mode

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
}
Enter fullscreen mode Exit fullscreen mode

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"]
}
Enter fullscreen mode Exit fullscreen mode

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;"
}
Enter fullscreen mode Exit fullscreen mode

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
    ...
  }

  ...
}

Enter fullscreen mode Exit fullscreen mode

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"
    }
  }
}

Enter fullscreen mode Exit fullscreen mode

Nothing else needs to change in your code, given you were reading the connection string from the configuration.

I hope you find this useful!

Top comments (5)

Collapse
 
masahigo profile image
Masi • Edited

Thanks for sharing! I can confirm that what you describe in the article still works.

A couple of details would have been nice to mention though, as the MS documentation is still quite confusing as a whole:

1) It is not needed to check the Allow Azure services and resources to access this server checkbox when creating the Azure SQL Server resource. At least I managed to get this working with having both App Service and Azure SQL behind private endpoints. Some of the MS documentation on this topic instructs to enable it.

2) The object_id in mssql_user Terraform resource is mandatory in this use case.

3) The Service Principal executing this IaC needs to have some permissions to MS Graph API, for instance Application.Read.All so that it can read the App Service's Managed Identity details from Azure AD to get it's application id which is then set as value to the beforementioned object_id.

Collapse
 
claraitit profile image
Clara

Hey Masi! Could you share your repository if you have one? Thank you!

Collapse
 
masahigo profile image
Masi • Edited

Hi Clara,

I put together a small gist which builds upon the example from this blog post: gist.github.com/Masahigo/0263ffdda...

It is not 100% complete but shows the basic idea. Identity (Azure AD user principal or service principal) executing that IaC needs to have that above mentioned permission on AAD side (or well, nowadays Microsoft Entra ID). Azure AD group is expected to be created beforehand and another service principal (only used in db automation) added to that group as member.

There's one more gotcha in regards to this approach. The Terraform provider (betr-io/mssql) writes the mssql_user resource to Terraform state and leads to issues if/when the client secret is changed/updated. This is a bug in the provider itself. Therefore it would be better to use a managed identity as it would be a more production-ready solution.

Collapse
 
krupakar1329 profile image
krupakar1329

good post

Collapse
 
nagoh profile image
Sam McGoldrick

Nice post, thanks