January 31, 2020 
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 '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
- https://docs.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-connect-msi#use-managed-identity-connectivity
- https://docs.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-dotnetcore-sqldb#create-production-sql-database
To simplify things, I have added all neccessary changes in a forked repo.
- Clone this repo https://github.com/renevanosnabrugge/dotnetcore-sqldb-tutorial
- 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
- This is not recommended for production! Set up a pipeline instead, but this is beyond the scope of this post. If you want to do this, please follow these steps. https://docs.microsoft.com/en-us/aspnet/core/host-and-deploy/azure-apps/azure-continuous-deployment?view=aspnetcore-3.1
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
The Road to ALM 
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.
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?
Hi! The self service portal is to facilitate teams to do things themselves where they otherwise would not have rights. For example creating spn and add to resource. Also it is to ensure consistency. E.g tagging on all resources to chargeback cost to product teams.
Creation of groups etc is something we do for them.
Hope this helps
Is it possible for them to call your self-service portal from any IaC script?
Yes for most of the things there is an api as well