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

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.

  1. A script runs from the pipeline that creates the SQL Server and database
  2. The pipeline user becomes an AAD admin on the SQL Server
  3. A script runs to create and deploy a Azure Web App
  4. The Azure Web App is assigned a Managed Service Identity
  5. 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)
  6. 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 '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

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 https://identitydocs.azurewebsites.net/static/aad/preauthorization.html 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 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"

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.

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

5 Responses to “Automatically provision a Azure SQL DB with a Managed Service Identity (MSI)”

  1. Great post. Thank you for sharing.

    I remember not getting this issue solved at some point. I ended up creating a normal AD user which is only used as a sql admin and uses ActiveDirectoryPassword Sql Authentication in the Pipeline, after which `CREATE USER [username] FROM EXTERNAL PROVIDER` had no issues 🙂 Which is a bit ugly.

  2. Nice post René! I wonder why are you building a self-service portal and how it could be consume by others? Should they use a form of API to make IaC scripts?

    Moreover, creating groups in AAD and playing with security is something we don’t want to leave to all teams… May be it’s the purpose of the portal?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: