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"
}
}
}
Nothing else needs to change in your code, given you were reading the connection string from the configuration.
I hope you find this useful!
Latest comments (5)
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.
Hey Masi! Could you share your repository if you have one? Thank you!
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.
good post
Nice post, thanks