Automate managed identity authentication between Azure App Service and Azure SQL Database - Part 2
Published: 2021-10-31 11:11:41

# Summary
In this post, I intend to share how to proceed the deployment pipeline flow of [the previous post](https://dev.to/koheikawata/automate-managed-identity-authentication-between-azure-app-service-and-azure-sql-database-3e8l). 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](https://github.com/koheikawata/weather-app/blob/main/pipelines/azure-devops/iac/iac-pipeline-template.yml)

# TOC
- [Deployment flow](#deployment-flow)
  - [1. Service connection](#1)
  - [2. Security Group](#2)
  - [3. Service Principal](#3)
  - [4. Pipeline Library variable group](#4)
  - [5. Pipeline secret variable](#5)
  - [6. Variable template Yaml file](#6)
  - [7. Judge if Pipeline secret variable exists](#7)
  - [8. Azure resource deployment](#8)
  - [9. Add SQL Server identity to security group](#9)
  - [10. Create database user with SQL query](#10)
  - [11. Upload secret to Key Vault](#11)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ix5b63rk91oko80luxw1.png)

<br> 

## 1. Service connection <a name="1"></a>
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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/da8z05bz6ffbvtqbr3kx.png)

<br>

## 2. Security Group <a name="2"></a>
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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/td185izk5ed6iwb3iiao.png)

<br>
 
## 3. Service Principal <a name="3"></a>
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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fem71p4nw8uvcacesn1w.png)

<br> 

## 4. Pipeline Library variable group <a name="4"></a>
In this [code sample](https://github.com/koheikawata/weather-app/blob/main/pipelines/azure-devops/iac/iac-pipeline-template.yml), 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  |

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/93ez1nqatzzelsnip6zt.png)

<br> 

## 5. Pipeline secret variable <a name="5"></a>
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](https://docs.microsoft.com/en-us/azure/devops/pipelines/process/variables?view=azure-devops&tabs=yaml%2Cbatch#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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7khfnimvivzkq9ontvrq.png) 

<br>

## 6. Variable template Yaml file <a name="6"></a>
Some of variables related to Infrastructure as Code is in [iac-variables.yml](https://github.com/koheikawata/weather-app/blob/main/pipelines/azure-devops/iac/iac-variables.yml)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pv526kmrt7yklzbqzy6n.png)

<br> 

## 7. Judge if Pipeline secret variable exists <a name="7"></a>
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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2v7k96vrfv31puuvvet2.png)
 
<br>

## 8. Azure resource deployment <a name="8"></a>
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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zjvnr22fjuiqzc52x8vi.png)
 
<br>

## 9. Add SQL Server identity to security group <a name="9"></a>
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. 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n1ohfy3kqvj587dllvyx.png)
 
<br>

## 10. Create database user with SQL query <a name="10"></a>
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](https://dev.to/koheikawata/automate-managed-identity-authentication-between-azure-app-service-and-azure-sql-database-3e8l#problem-2).

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z1ef64rj0invbrxk0i4u.png)
 

<br> 

## 11. Upload secret to Key Vault <a name="11"></a>
After deployment is done, the Pipeline secret variables are uploaded to Key Vault for the future pipeline run.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3g14uv5pqizywhhi3dj1.png)