Automate managed identity authentication between Azure App Service and Azure SQL Database - Part 2

Summary

In this post, I intend to share how to proceed the deployment pipeline flow of the previous post. I describe flow charts so you can understand the complex process of managed identity authentication for Azure SQL Database.

The sample code is here : weather-app/pipelines/azure-devops/iac/iac-pipeline-template.yml

Deployment flow

The figure below is an overall picture of the deployment process.

Azure tenant and Azure DevOps tenant is connected by Service Connection
Process 1-6 is done manually, and process 7-11 is automated.
Azure AD Global Administrator or Privileged Role Administrator permission is required for some process.

1. Service connection

First, you create Azure DevOps Service Connection that connects you to Azure tenant. For this process, Azure AD User permission that is allowed to access Azure AD is required.

2. Security Group

You need to create Azure AD Security Group and grant Directory Reader role to the group. Without this security group, you need Global Administrator or Privileged Role Administrator permission every time you run the deployment pipeline. This step requires such a high privileged permission but it is only one time.

3. Service Principal

You need to create a service principal through Azure AD app registration and grant Directory Reader role. This service principal is used later in Azure DevOps pipeline for:

Getting SQL Server object ID
Adding SQL Server identity to the security group member

Creating a service principal is done by Azure AD User, but granting Directory Reader Role requires Global Administrator or Privileged Role Administrator permission.

4. Pipeline Library variable group

In this code sample, variables related to Azure AD is set up in Azure DevOps Pipeline Library variable group.

Variable name	Usage
SVC_CONNECTION_AAD_NAME	For SQL Server Azure AD Admin
SVC_CONNECTION_OBJECT_ID	For access policy to Key Vault
SP_SQL_QUERY_RUN_CLIENT_ID	For Azue AD connection
SG_SQL_QUERY_RUN_OBJECT_ID	For granting security group member

5. Pipeline secret variable

We have two variables, Service Principal password and SQL Server login password that we want to set in Key Vault as secret, but the problem is we need do so manually in the first deployment. (Because Key Vault is not deployed yet.) In this case, you put those passwords as Pipeline secret variables in the first deployment. Those passwords are uploaded to Key Vault in the first deployment, and then you do not need those Pipeline secret variables after second time.

6. Variable template Yaml file

Some of variables related to Infrastructure as Code is in iac-variables.yml

7. Judge if Pipeline secret variable exists

From this step, the process is automated with Azure DevOps pipeline. It checks if environment variables include secret variables of the service principal secret and SQL login password. If included, the Azure DevOps pipeline uses them. If not, the Azure DevOps pipeline downloads those secrets from existing Key Vault, which is assumed to be the second time pipeline run or after. If neither, the Azure DevOps pipeline fails.

8. Azure resource deployment

The Azure DevOps pipeline deploys Azure App Service, Key Vault, SQL Server, SQL Database, and identities of App Service and SQL Server. This step can be done by Azure DevOps Service Connection service principal permission.

9. Add SQL Server identity to security group

With the service principal with Azure AD Directory Role and security group owner, the Azure DevOps pipeline gets SQL Server object ID and assigns the security group member to it.

10. Create database user with SQL query

The Azure DevOps pipeline runs SQL query to create database user roles in Azure SQL Database. This can be done by Azure DevOps Service Connection service principal permission, but SQL Server identity should be already included in the security group that has Azure AD Directory Reader role. This step is related to the topic in the previous post, 2. Connect-AzureAD in Azure DevOps pipeline.