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

Summary

Setting up an automated Infrastructure as Code pipeline for Managed Identity authentication between Azure App Service and Azure SQL Database is not sraightforward. I faced some problems to build it, and would like to share key points here.

Sample code: weather-app/pipelines/azure-devops/iac/iac-pipeline-template.yml

TOC

• Problems
  • 1. Create SQL Database User by Service Principal
  • 2. Connect-AzureAD in Azure DevOps pipeline
    • Additional problem 1: Need an additional service principal that can access Azure AD
    • Additional problem 2: Connect to Azure AD with service principal
  • 3. Automation of password management

Problems

1. Create SQL Database User by Service Principal

We can choose one from some options of authentication method when Azure App Service or Azure Funcions access Azure SQL Database. When you take Managed Identity authentication, you have to create a SQL Database user by running a query on it. This user should be a managed identity object ID of App Service or Functions. You have to do three actions below when you deploy relevant Azure resources manually.

Manual deployment

Azure service	Action	Tool	Permission
App Service	Add system assigned identity	Azure Portal	AAD User
SQL Server	Set up firewall rule	Azure Portal	AAD User
SQL Database	Create user	SQL query	AAD User (SQL AAD Admin)

On the other hand, when you automate deployment of those Azure resources through Azure DevOps pipeline, you need additional actions because in this case Azure AD Admin is taken by a service principal, which led Azure SQL Server to need Azure AD Directory Readers permission to access Microsoft Graph and get some information about Azure AD object IDs.

We can find some discussions about the issues of when you run a SQL Database query with a service principal, instead of Azure AD User, in order to create App Sevice's managed identity.

• CREATE USER FROM EXTERNAL PROVIDER by Service Principal #2323
• SqlException (0x80131904): Principal 'xyz' could not be resolved: How can I add a Managed Identity to Azure Sql Server when running under a Service Principal? #4959

After those discussions, Microsoft Product Group added a workaround in the document. This workaround adds two steps of 1) Assign a system assigned identity to SQL Server, 2) Grant Directory Readers permission to SQL Server's identity.

• Azure Active Directory service principal with Azure SQL
• Directory Readers role in Azure Active Directory for Azure SQL

Additional issues about this workaround is it requires Azure AD Global Administrator or Privileged Role Administrator to grant Azure AD Directory Readers role to Azue SQL Server. These roles have pretty high privileges and a service principal usually does not have such a high privileged permission. Then there are additional documentations of a workaround of the workaround that a service principal adds the SQL Server identity to a Azure AD Security Group that has a role of Directory Readers.

• Tutorial: Assign Directory Readers role to an Azure AD group and manage role assignments
• Use Azure AD groups to manage role assignments

By createing a security group, we have an automated deployment pipeline in Azure DevOps, although you have to create a security group and grant Directory Readers permission to it only once by Global Administrator or Privileged Role Administrator when you create a new Azure tenant.

Automated deployment through Azure DevOps

Azure service	Actions	Tool	Permission
App Service	Add system assigned identity	Azure DevOps pipeline	AAD Service Principal(Azure DevOps Sevice Connection)
SQL Server	Set up firewall rule	Azure DevOps pipeline	AAD Service Principal(Azure DevOps Sevice Connection)
SQL Server	Add system assigned identity	Azure DevOps pipeline	AAD Service Principal(Azure DevOps Sevice Connection)
SQL Server	Grant AAD Directory Readers permission	Azure Portal	AAD Global Administrator or Privileged Role Administrator
SQL Database	Create user	Azure DevOps pipeline	AAD Service Principal(Azure DevOps Sevice Connection)

2. Connect-AzureAD in Azure DevOps pipeline

Here are additional two problems.

Additional problem 1: Need an additional service principal that can access Azure AD

First, Azure DevOps pipeline uses a service principal to access and manage Azure resources, which is automatically generated when you create a Azure DevOps Service Connection. Then you need to create an additional service principal through Azure AD app registration and 1) grant a permission to access Azure AD to retrieve SQL Server's object ID and 2) grant an owner role to add the SQL server identity to the security group you created before. Additional service principal requires you to manage its secret and additional manual process of app registration when you create a new Azure tenant.

Additional problem 2: Connect to Azure AD with service principal

Second, when I tried to login with a service principal through Powershell on Azure DevOps pipeline, the code below does not work.

This won't work

Install-Module AzureAD -Force
$tenantId = (Get-AzContext).Tenant.Id
$user = "$env:SP_CLIENTID"
$password = ConvertTo-SecureString -AsPlainText "$env:SP_PASSWORD" -Force
$pscredential = New-Object -TypeName System.Management.Automation.PSCredential($user, $password)
Connect-AzureAD -Credential $pscredential -Tenant $tenantId

It was very confusing when it works I run these Powershell script manually on Azure Portal. This is another problem that I can run with my Azure AD User permission but Azure DevOps pipeline cannot run with a service connection service principal permission. Then I looked around and found a solution that I log in to Azure with a service principal first and then get Microsoft Graph token and access to Azure AD. The code example of Azure DevOps pipeline yaml is like this.

- task: AzurePowerShell@5
  inputs:
    azureSubscription: '$(AZURE_RM_SVC_CONNECTION)'
    azurePowerShellVersion: latestVersion
    ScriptType: InlineScript
    Inline: |
      Install-Module -Name "AzureAD" -Force
      $tenantId = (Get-AzContext).Tenant.Id
      az login --service-principal --username $env:SP_CLIENTID --password $env:SP_PASSWORD --tenant $tenantId --allow-no-subscriptions
      $aadToken = az account get-access-token --resource-type aad-graph | ConvertFrom-Json
      $graphToken = az account get-access-token --resource-type ms-graph | ConvertFrom-Json
      Connect-AzureAD -TenantId $tenantId -AccountId $env:SP_CLIENTID  -MsAccessToken $graphToken.accessToken -AadAccessToken $aadToken.accessToken
      $miIdentity = Get-AzureADServicePrincipal -SearchString "$env:SQL_SERVER_NAME"
      Add-AzureADGroupMember -ObjectId $env:SP_OBJECT_ID -RefObjectId $miIdentity.ObjectId
  env:
    SP_PASSWORD: $(SP_PASSWORD)

3. Automation of password management

When you automate a deployment pipeline, you could face password management problems. Passwords should be protected in Azure Key Vault, which is a recommended way by Microsoft, but you need passwords somewhere before Key Vault is deployed through Azure DevOps pipeline. There are two cases you need passwords in the beginning of deployment.

• SQL login admin password
• Service Principal password

In this scenario, I chose Azue DevOps pipieline secret variable to set up variables in the first deployment. After second pipeline run, you do not need the pipeline secret variables because those passwords are stored in Azure Key Vault during the first deployment pipline run. I added three conditional tasks in Azure DevOps pipeline.

1. Judge which secret to use, pipeline secret variable or Key Vault
2. Get the secret from Key Vault if needed
3. Overwrite the password environment variable if needed

- task: PowerShell@2
  inputs:
    condition: eq(variables['SP_PASSWORD'], '')
    targetType: 'inline'
    script: |
      Write-Host "##vso[task.setvariable variable=spSecretKv;]true"
      Write-Host "Retrieve secret from Key Vault"
  displayName: Judge which secret to use, pipeline secret variable or Key Vault
- task: AzureKeyVault@1
  condition: eq(variables['spSecretKv'], 'true')
  inputs:
    azureSubscription: '$(AZURE_RM_SVC_CONNECTION)'
    KeyVaultName: '$(KEYVAULT_NAME)'
    SecretsFilter: 'sppassowrdsecret'
    RunAsPreJob: false
  displayName: Get the secret from Key Vault
- task: PowerShell@2
  inputs:
    condition: eq(variables['spSecretKv'], 'true')
    targetType: 'inline'
    script: |
      Write-Host "##vso[task.setvariable variable=SP_PASSWORD;]$env:sppassowrdsecret"
  env:
    sppassowrdsecret: $(sppassowrdsecret)
  displayName: Overwrite the password environment variable

After Azure resource deployment, the passwords from pipeline secret variables should be stored in Key Vault so you do not need pipline secret variables in the future run. You can set secrets Key Vault through Azure DevOps pipeline like below.

- task: AzureCLI@2
  condition: ne(variables['spSecretKv'], 'true')
  inputs:
    azureSubscription: '$(AZURE_RM_SVC_CONNECTION)'
    scriptType: ps
    scriptLocation: inlineScript
    inlineScript: |
      az keyvault secret set --vault-name $(KEYVAULT_NAME) --name sppassowrdsecret --value $(SP_PASSWORD)
  displayName: Set secret in Key Vault