Automatically provision a Azure SQL DB with a Managed Service Identity (MSI)

This may also be of interest to you

class=”wp-block-jetpack-markdown”>

My colleagues and me are building a self-service portal for one of our customers. A few days ago my colleague asked me if I could provision a Azure SQL Server and SQL Database for him. Because we do not have rights to do this manually on production (as it should be!), this all needs to happen automatically.

The consumer application is a Azure Web App. The Web App accesses the database. Normal architecture, nothing to see here. One of the great security features in Azure and Azure Active Directory is the Managed Service Identity. This makes it possible to access Azure resources with a special Service Principal that is managed by Azure. That means that you don’t need to worry about Key rotation, expiration and most importantly, keeping track of usernames and passwords in your code.

When I got to work, the provisioning of the web app and the database was fairly easy. Granting access to the MSI was documented, but already a bit shady, but worst of all, all documentation pointed to a method where you grant the MSI access with the Azure portal or SQL Management Studio (manual!) with your own user account.

What I wanted, is the following easy setup.

A script runs from the pipeline that creates the SQL Server and database
The pipeline user becomes an AAD admin on the SQL Server
A script runs to create and deploy a Azure Web App
The Azure Web App is assigned a Managed Service Identity
The MSI is granted access to the SQL DB, so that the Web App can get data. The pipeline SPN (SPN A), can grant this access (as a SQL Admin)
The MSI (MSI B) is user in the database

This seems trivial, but I can assure you, it is not. Let me describe the steps to make this happen.

Setting up Prerequisites

Setting up the database can be done with ARM, Powershell, Azure CLI etc. As long as you can run it from the pipeline it is good. In order to run things from a pipeline, or at least simulate this, we need a SPN.

# Login with you Azure account that has rights to create SPN's
$sqladminSPN = "blog-sql-admin"
az login
az ad app create --display-name $sqladminSPN

Setting up the SQL Server database

Setting up the database can be done with ARM, Powershell, Azure CLI etc. As long as you can run it from the pipeline it is good.

$resourcegroup = "rg-blog-sql"
$sqlservername = "blog-sqlsrv-rvo"
$location = "westeurope"
$databasename="tododb"

az group create --name $resourcegroup --location $location

az sql server create --resource-group $resourcegroup --name $sqlservername --admin-user sqladmin --admin-password 1234HVHV! --location $location

az sql db create --name $databasename --server $sqlservername --resource-group $resourcegroup

#Firewall ports
$clientIp = Invoke-WebRequest ' | Select-Object -ExpandProperty Content

az sql server firewall-rule create --resource-group $resourcegroup --server $sqlservername --name azure --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0

az sql server firewall-rule create --resource-group $resourcegroup --server $sqlservername --name clientip1 --start-ip-address $clientIp --end-ip-address $clientIp

Now that we have created a SQL server database can we add the MSI? No. Because the MSI is an AAD account, we cannot add it. The server was created with a SQL sevrer login. This account cannot add users from AAD.

Now we need to add a AAD user as admin of the SQL Server. This is easy from the command line and the portal. However, a user account is something you cannot and should not use from the pipeline. My user account has Multi-Factor Authentication so it is not even possible, but even if you don’t MFA, you should not use user accounts in your pipeline. We want to have a SPN as AAD admin. The pipeline user.

Unfortunately, the Azure Portal and CLI does not allow you to add a SPN as AAD Admin. You can work around this, by creating an AAD group, put the SPN in this group make the group SQL Server Admin.

$sqladmingroupname = "blog-sql-admingroup"
#Create a Group
az ad group create --display-name $sqladmingroupname --mail-nickname $sqladmingroupname

$adgroup = az ad group show --group $sqladmingroupname | ConvertFrom-Json

#Create an App Registration
$adsqlapp = az ad app list --display-name $sqladminSPN --query [] | ConvertFrom-Json

#Create SPN based on App Registration
$adsqlspn = az ad sp create --id $($adsqlapp.appId)

#it can be that it crashes here, because it needs some time to process. try again if this happens.
$adsqlspn = az ad sp list --display-name $sqladminSPN --query [] | ConvertFrom-Json

#Add SPN(!) to Group
az ad group member add --group $($adgroup.objectId) --member-id $($adsqlspn.objectId)

#Assign the group as SQL AAD Admin
az sql server ad-admin create --resource-group $resourcegroup --server-name $sqlservername --display-name "$sqladmingroupname" --object-id $($adgroup.objectId)

Creating the Web App and Managed Service Identity

Now that we have a SQL Server and also a database, we need to grant access to the Managed Service Identity. Before we do that, we need to create a Web Application and MSI.

$appplan="todowebplan"
$webapp="blogtodoweb"

#Create AppServicePlan
az appservice plan create --resource-group $resourcegroup --name $appplan --sku S1

#Create WebApp
az webapp create --resource-group $resourcegroup --name $webapp --plan $appplan

#Get Identity
az webapp identity assign --resource-group $resourcegroup --name $webapp

Now we have assigned an Identity, we need to add this to SQL Server. In many of the documentation on the internet, you will see that you can add a DB user to the SQL Server, by logging in to the SQL Server, and execute the command. This works great, but NOT if you want to do this from a command line, a powershell script or a console application that uses THE SPN as the SQL Administrator.

#THIS DOES NOT WORK WHEN YOU LOG IN WITH SPN!
`CREATE USER [username] FROM EXTERNAL PROVIDER;`

After a long search, I found 4 things

The SQL Server Admin SPN cannot access AD. When you try to use the construction `FROM EXTERNAL PROVIDER’ you get a nasty error. When you read this documentation (behind a login), it becomes shady…

>Principal ‘XXXXX-483d-436b-b037-5a3123b01a58’ could not be resolved. Error message: ‘AADSTS65002: Consent between first party applications and resources must be configured via preauthorization. Visit for details

When using the SPN to grant rights, you need to use the construction

CREATE USER [user] WITH SID = $sid, TYPE=X;

When granting access to a Database to a SPN/MSI, it does not work. You need to add these to a group as well and add the group
For some weird, frantic and absolutely strange reason, you cannot run Entity Framework Database migration when using an MSI accesstoken. When adding the MSI to the SQL Server Admin Group, it works..If anybody has an idea why this is, or how to overcome this, I’m open for it !

So back to work. We need to create an AAD Group (for DbReaders), add the MSI to the AAD Group, and then grant access to this AAD Group in the SQL DB. Below you find the script with inline comments.

$spnobjectid = $($adsqlspn.objectId)
$spnpassword = (az ad sp credential reset --name $sqladminSPN | ConvertFrom-Json).password
$tenantid = (az account show | ConvertFrom-Json).tenantId
$adReaderGroupName = "blog-db-users"

#create a db reader group
az ad group create --display-name $adReaderGroupName --mail-nickname $adReaderGroupName
$adgroupreaders = az ad group show --group $adReaderGroupName | ConvertFrom-Json

#Add MSI to this group
$msiObjectId = (az webapp identity show --name $webapp --resource-group $resourcegroup | ConvertFrom-Json).principalId
az ad group member add --group $($adgroupreaders.objectId) --member-id $msiObjectId

#For whatever reason, the MSI needs to be in the AAD group, otherwise you cannot run the Entity Framework Database Migration
az ad group member add --group $($adgroup.objectId) --member-id $msiObjectId


#---------------------------------------------------------------------------
#Execute as SPN
#---------------------------------------------------------------------------
#login with SPN with sufficient rights on the subscription. Pipeline SPN should have this
az logout
az login --service-principal -u $($adsqlspn.appId) -p $spnpassword  --tenant $tenantid --allow-no-subscriptions

# Get Access Token for the database
$token = az account get-access-token --resource  | ConvertFrom-Json
Write-Host "Retrieved JWT token for SPN [$spnobjectid]"
Write-Host "AccessToken [$($token.accessToken)]" -ForegroundColor Green

# Create a SQL connection to the User Database
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$sqlservername.database.windows.net;Initial Catalog=$databasename"
$SqlConnection.AccessToken = $token.accessToken

# Create a SQL connection to the Master Database
$SqlConnectionMaster = New-Object System.Data.SqlClient.SqlConnection
$SqlConnectionMaster.ConnectionString = "Data Source=$sqlservername.database.windows.net;Initial Catalog=master"
$SqlConnectionMaster.AccessToken = $token.accessToken

# Create a SID for the object ID of the AAD Db Reader Group
$query = ""
$query = $query + "SELECT CONVERT(VARCHAR(1000), CAST(CAST('$($adgroupreaders.objectId)' AS UNIQUEIDENTIFIER) AS VARBINARY(16)),1) SID;"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $query
$SqlCmd.Connection = $SqlConnection
$SqlConnection.Open()
$sid = $SqlCmd.ExecuteScalar()
$SqlConnection.Close()
Write-Host "SID Web App MSI [$webapp] is [$sid]"

# Set MSI in right groups
$queryMaster = ""
$queryMaster = $queryMaster + "DROP USER IF EXISTS [[$adReaderGroupName];"
$queryMaster = $queryMaster + "CREATE USER [$adReaderGroupName] WITH SID = $sid, TYPE=X;"
$queryMaster = $queryMaster + "ALTER ROLE db_owner ADD MEMBER [$adReaderGroupName];"

$query = ""
$query = $query + "DROP USER IF EXISTS [$adReaderGroupName];"
$query = $query + "CREATE USER [$adReaderGroupName] WITH SID = $sid, TYPE=X;"
$query = $query + "ALTER ROLE db_owner ADD MEMBER [$adReaderGroupName];"

$SqlCmdMaster = New-Object System.Data.SqlClient.SqlCommand
$SqlCmdMaster.Connection = $SqlConnectionMaster
$SqlCmdMaster.CommandText = $queryMaster
$SqlConnectionMaster.Open()
$SqlCmdMaster.ExecuteNonQuery()
$SqlConnectionMaster.Close()

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $query
$SqlCmd.Connection = $SqlConnection
$SqlConnection.Open()
$SqlCmd.ExecuteNonQuery()
$SqlConnection.Close()

Write-Host "Added Web App MSI [$adReaderGroupName] to DB"

Running the Web Application

Now that you have provisioned the database, and granted access to the Managed Service Identity, we should test it with a real web application.

I have used the Web Application from the sample described in these 2 posts

To simplify things, I have added all neccessary changes in a forked repo.

Clone this repo
Change the connectionstring in appsettings.json.
- Point the to the SQL Servername
- Point the to the Database name
If your MSI is used in multiple subscriptions, change the id in the MyDatabaseContext.cs file
Open the Solution in Visual Studio
Right Click / Publish your solution to the provisioned Web App

When you published the Web Application, visit the url.. and Voila!

Hope this helps!

To get the full Powershell script for provisioning, please find the following Gist

# Login with you Azure account that has rights to create SPN’s

$sqladminSPN = “blog-sql-admin“

az login

az ad app create —display–name $sqladminSPN

#Setting up SQL

$resourcegroup = “rg-blog-sql“

$sqlservername = “blog-sqlsrv-rvo“

$location = “westeurope“

$databasename=“tododb“

az group create —name $resourcegroup —location $location

az sql server create —resource–group $resourcegroup —name $sqlservername —admin–user sqladmin —admin–password <password> —location $location

az sql db create —name $databasename —server $sqlservername —resource–group $resourcegroup

#Firewall ports

$clientIp = Invoke-WebRequest ‘https://api.ipify.org‘ | Select-Object –ExpandProperty Content

az sql server firewall–rule create —resource–group $resourcegroup —server $sqlservername —name azure —start-ip–address 0.0.0.0 —end–ip–address 0.0.0.0

az sql server firewall–rule create —resource–group $resourcegroup —server $sqlservername —name clientip1 —start-ip–address $clientIp —end–ip–address $clientIp

#Security Groups

$sqladmingroupname = “blog-sql-admingroup“

#Create a Group

az ad group create —display–name $sqladmingroupname —mail–nickname $sqladmingroupname

$adgroup = az ad group show —group $sqladmingroupname | ConvertFrom-Json

#Create an App Registration

$adsqlapp = az ad app list —display–name $sqladminSPN —query [] | ConvertFrom-Json

#Create SPN based on App Registration

$adsqlspn = az ad sp create —id $($adsqlapp.appId)

#it can be that it crashes here, because it needs some time to process. try again if this happens.

$adsqlspn = az ad sp list —display–name $sqladminSPN —query [] | ConvertFrom-Json

#Add SPN(!) to Group

az ad group member add —group $($adgroup.objectId) —member–id $($adsqlspn.objectId)

#Assign the group as SQL AAD Admin

az sql server ad–admin create —resource–group $resourcegroup —server–name $sqlservername —display–name “$sqladmingroupname“ —object–id $($adgroup.objectId)

# Web App 

$appplan=“todowebplan“

$webapp=“blogtodoweb“

#Create AppServicePlan

az appservice plan create —resource–group $resourcegroup —name $appplan —sku S1

#Create WebApp

az webapp create —resource–group $resourcegroup —name $webapp —plan $appplan

#Get Identity

az webapp identity assign —resource–group $resourcegroup —name $webapp

$spnobjectid = $($adsqlspn.objectId)

$spnpassword = (az ad sp credential reset —name $sqladminSPN | ConvertFrom-Json).password

$tenantid = (az account show | ConvertFrom-Json).tenantId

$adReaderGroupName = “blog-db-users“

#create a db reader group

az ad group create —display–name $adReaderGroupName —mail–nickname $adReaderGroupName

$adgroupreaders = az ad group show —group $adReaderGroupName | ConvertFrom-Json

#Add MSI to this group

$msiObjectId = (az webapp identity show —name $webapp —resource–group $resourcegroup | ConvertFrom-Json).principalId

az ad group member add —group $($adgroupreaders.objectId) —member–id $msiObjectId

#For whatever reason, the MSI needs to be in the AAD group, otherwise you cannot run the Entity Framework Database Migration

az ad group member add —group $($adgroup.objectId) —member–id $msiObjectId

#—————————————————————————

#Execute as SPN

#—————————————————————————

#login with SPN with sufficient rights on the subscription. Pipeline SPN should have this

az logout

az login —service–principal –u $($adsqlspn.appId) –p $spnpassword —tenant $tenantid —allow–no–subscriptions

# Get Access Token for the database

$token = az account get-access–token —resource https://database.windows.net/ | ConvertFrom-Json

Write-Host “Retrieved JWT token for SPN [$spnobjectid]“

Write-Host “AccessToken [$($token.accessToken)]“ –ForegroundColor Green

# Create a SQL connection to the User Database

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString = “Data Source=$sqlservername.database.windows.net;Initial Catalog=$databasename“

$SqlConnection.AccessToken = $token.accessToken

# Create a SQL connection to the Master Database

$SqlConnectionMaster = New-Object System.Data.SqlClient.SqlConnection

$SqlConnectionMaster.ConnectionString = “Data Source=$sqlservername.database.windows.net;Initial Catalog=master“

$SqlConnectionMaster.AccessToken = $token.accessToken

# Create a SID for the object ID of the AAD Db Reader Group

$query = ““

$query = $query + “SELECT CONVERT(VARCHAR(1000), CAST(CAST(‘$($adgroupreaders.objectId)‘ AS UNIQUEIDENTIFIER) AS VARBINARY(16)),1) SID;“

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.CommandText = $query

$SqlCmd.Connection = $SqlConnection

$SqlConnection.Open()

$sid = $SqlCmd.ExecuteScalar()

$SqlConnection.Close()

Write-Host “SID Web App MSI [$webapp] is [$sid]“

# Set MSI in right groups

$queryMaster = ““

$queryMaster = $queryMaster + “DROP USER IF EXISTS [[$adReaderGroupName];“

$queryMaster = $queryMaster + “CREATE USER [$adReaderGroupName] WITH SID = $sid, TYPE=X;“

$queryMaster = $queryMaster + “ALTER ROLE db_owner ADD MEMBER [$adReaderGroupName];“

$query = ““

$query = $query + “DROP USER IF EXISTS [$adReaderGroupName];“

$query = $query + “CREATE USER [$adReaderGroupName] WITH SID = $sid, TYPE=X;“

$query = $query + “ALTER ROLE db_owner ADD MEMBER [$adReaderGroupName];“

$SqlCmdMaster = New-Object System.Data.SqlClient.SqlCommand

$SqlCmdMaster.Connection = $SqlConnectionMaster

$SqlCmdMaster.CommandText = $queryMaster

$SqlConnectionMaster.Open()

$SqlCmdMaster.ExecuteNonQuery()

$SqlConnectionMaster.Close()

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.CommandText = $query

$SqlCmd.Connection = $SqlConnection

$SqlConnection.Open()

$SqlCmd.ExecuteNonQuery()

$SqlConnection.Close()

Write-Host “Added Web App MSI [$adReaderGroupName] to DB“