Secure access to an Azure SQL database with Managed Identity.

Azure offers Managed Identities, an integrated system for secure access with and to Azure resources.
This integration is deeply implemented into Azure, provides the highest level of security that is technically possible, and is therefore the recommended way to go.

Managed Identity

Managed Identity is a mechanism that is built into almost every Azure resource and is globally unique.
Each resource has a "technically unique user" that is known and can only be used within an Azure Active Directory domain.

Furthermore, these identities are only known within Azure, so cannot be used locally (even during development) or on-prem. This limits their use somewhat, but is precisely what makes Managed Identity so enormously secure.

Security Benefit of Managed Identity

Most data leaks happen because outside access to the database becomes possible. And access becomes possible because credentials are lost.
Managed Identity is a mechanism that has no credentials - and if you don't have them, you can't lose anything.

For access to be possible, the attacker would have to be part of the Azure Active Directory domain or have access to it.\ This increases the security level enormously!

Azure SQL

Azure SQL is an Azure product, but technically speaking it is "just a SQL" server.
SQL Server supports Active Directory for years, and Azure SQL has supported Azure Active Directory since the beginning - but Azure SQL does not have a really good integration with Managed Identities.

The implementation shown here is based on undocumented interfaces from the community that work but are officially "to be removed (https://github.com/MicrosoftDocs/sql-docs/issues/2323#issuecomment-791718677)".

Whether these will ever be removed, or whether they will be removed before something official comes along: all uncertain.

The documentation, for being an enormously important security component, is subterraneously bad.
Actually an absolute unworthy.

Goal of this Article

The goal of this article is now that we create a web application using Azure DevOps based on Bicep, which gets access to an Azure SQL database via Managed Identities - fully automatically.

This is an everyday scenario that is needed or desired in thousands of cases, and does not appear once in any official documentation as an end to end example.

I implemented a first attempt this year in April together with my colleague Philipp Bauknecht (E2E DevOps for Azure SQL with Managed Identities) and have now improved it further.

The time it took for this implementation was about four weeks - mainly because official documentation is missing.

The Implementation

These steps are usually done one time only for each environment. They are therefore not part of the actual automated infrastructure deployment with Bicep. Some steps require certain permissions in Azure Active Directory.

I use powershell for all my script executions, so I can reuse vars and values.

Prerequisites

• Azure Account
• Administrative Azure privileges
• Azure DevOps with connection to your Azure Tenant
• Azure DevOps Repository
• .NET 6

Azure Resource Providers

Please ensure you have folling Azure Resource Providers enabled in your subscription:

• Azure App Service Plan
• Azure Web App
• Azure KeyVault
• Azure App Config
• Azure SQL
• Azure Container Instance

If not, activate all of them. Otherwise scripts will crash during deployment.

Setup PowerShell Vars

$location = 'GermanyWestCentral'
$subscriptionId = '12345-12345-12345-12345' # insert the subscription guid here

$projectName = 'benabt-platform' # edit this (max 17 chars!) <<<<<<
$env = 'dev' # edit this for every environment!

# these names respect the Azure Naming Guidelines and also all length limits
$serviceAzureRGName = "rg-$($projectName)-$($env)"
$deploymentIdentityName = "id-$($projectName)-sql-deployer-$($env)"
$deploymentIdentityAzureRGName = "rg-$($projectName)-devops-$($env)"

$azureSqlAdminGroupName = "sg-$($projectName)-sqladmins-$($env)" # sg = security group
$azureSqlAdminGroupContactMailNickname = $azureSqlAdminGroupName

$azureGroupDirectoryReaders = '88d8e3e3-8f55-4a1e-953a-9b9898b8876b' # this is fixed by Azure

First Step: Login to Azure

The first thing we need to do is log into our PowerShell instance. I always recommend to use the device code variant, because this allows to use the browser of your choice.
Unfortunately, some companies prevent this by policy, which is totally nonsensical. I hope, if this is the case for you, the correct Browser instance is called to log in to Azure.

az login --use-device-code
az account set --subscription $subscriptionId

Second Step: Create two Resource Groups

This setup requires two resource groups:

The first resource group contains all the platform resources, i.e. the web app, the database, the KeyVault etc etc. This is the usual setup.

In addition, we need a second resource group where a special identity is created that will later be used by Azure DevOps for deployment.

# Create the first Azure Resource Group for our services
az group create --location $location --name $serviceAzureRGName
# Create the second Azure Resource Group for our deployment identity
az group create --location $location --name $deploymentIdentityAzureRGName

Third Step: create a Azure DevOps Service Principal

For security reasons, it is recommended that Azure DevOps is not given any administrative rights, which is why we also created the resource groups manually.
Now we need to create a service principal in Azure DevOps. This is a manual, administrative step.

Azure DevOps - Manage service connections

Please make sure you use the "Resource Group" variant and select $serviceAzureRGName which is rg-benabt-platform-dev in this sample!

The Azure DevOps Wizard creates a Service Principal in Azure, which is listed in the Azure Active Directory view as Enterprise Application.
Now please find the Azure Service Principal (you have set a name) in the Enterprise Registrations and set the Object Id of the registration as a new PowerShell variable.

$azureDevOpsSPObjectId = "" # edit this from Enterprise Registration! <<<<<< ##