My colleagues and me are constructing a self-service portal for one in every of our prospects. Just a few days in the past my colleague requested me if I might provision a Azure SQL Server and SQL Database for him. Because we don’t have rights to do that manually on manufacturing (correctly!), this all must occur mechanically.
The shopper utility is a Azure Web App. The Web App accesses the database. Normal structure, nothing to see right here. One of the good security measures in Azure and Azure Active Directory is the Managed Service Identity. This makes it potential to entry Azure sources with a particular Service Principal that’s managed by Azure. That signifies that you don’t want to fret about Key rotation, expiration and most significantly, conserving monitor of usernames and passwords in your code.
When I set to work, the provisioning of the online app and the database was pretty straightforward. Granting entry to the MSI was documented, however already a bit shady, however worst of all, all documentation pointed to a technique the place you grant the MSI entry with the Azure portal or SQL Management Studio (guide!) with your personal person account.
What I wished, is the next straightforward setup.
- A script runs from the pipeline that creates the SQL Server and database
- The pipeline person turns into 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 entry to the SQL DB, in order that the Web App can get knowledge. The pipeline SPN (SPN A), can grant this entry (as a SQL Admin)
- The MSI (MSI B) is person within the database
This appears trivial, however I can guarantee you, it’s not. Let me describe the steps to make this occur.
Setting up Prerequisites
Setting up the database might be accomplished with ARM, Powershell, Azure CLI and so on. As lengthy as you possibly can run it from the pipeline it’s good. In order to run issues from a pipeline, or a minimum of simulate this, we want a SPN.
# Login with you Azure account that has rights to create SPN's
$sqladminSPN = "blog-sql-admin"
az login
az advert app create --display-name $sqladminSPN
Setting up the SQL Server database
Setting up the database might be accomplished with ARM, Powershell, Azure CLI and so on. As lengthy as you possibly can run it from the pipeline it’s 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 now have created a SQL server database can we add the MSI? No. Because the MSI is an AAD account, we can not add it. The server was created with a SQL sevrer login. This account can not add customers from AAD.
Now we have to add a AAD person as admin of the SQL Server. This is straightforward from the command line and the portal. However, a person account is one thing you can not and shouldn’t use from the pipeline. My person account has Multi-Factor Authentication so it’s not even potential, however even should you don’t MFA, you shouldn’t use person accounts in your pipeline. We wish to have a SPN as AAD admin. The pipeline person.
Unfortunately, the Azure Portal and CLI doesn’t let you add a SPN as AAD Admin. You can work round this, by creating an AAD group, put the SPN on this group make the group SQL Server Admin.
$sqladmingroupname = "blog-sql-admingroup"
#Create a Group
az advert group create --display-name $sqladmingroupname --mail-nickname $sqladmingroupname
$adgroup = az advert group present --group $sqladmingroupname | ConvertFrom-Json
#Create an App Registration
$adsqlapp = az advert app checklist --display-name $sqladminSPN --query [] | ConvertFrom-Json
#Create SPN primarily based on App Registration
$adsqlspn = az advert sp create --id $($adsqlapp.appId)
#it may be that it crashes right here, as a result of it wants a while to course of. attempt once more if this occurs.
$adsqlspn = az advert sp checklist --display-name $sqladminSPN --query [] | ConvertFrom-Json
#Add SPN(!) to Group
az advert 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 now have a SQL Server and likewise a database, we have to grant entry to the Managed Service Identity. Before we do this, we have 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 identification assign --resource-group $resourcegroup --name $webapp
Now we now have assigned an Identity, we have to add this to SQL Server. In most of the documentation on the web, you will notice which you could add a DB person to the SQL Server, by logging in to the SQL Server, and execute the command. This works nice, however NOT if you wish to do that from a command line, a powershell script or a console utility that makes use of THE SPN because the SQL Administrator.
#THIS DOES NOT WORK WHEN YOU LOG IN WITH SPN!
`CREATE USER [username] FROM EXTERNAL PROVIDER;`
After a protracted search, I discovered 4 issues
- The SQL Server Admin SPN can not entry AD. When you attempt to use the development `FROM EXTERNAL PROVIDER’ you get a nasty error. When you learn this documentation (behind a login), it turns into shady…
>Principal ‘XXXXX-483d-436b-b037-5a3123b01a58’ couldn’t be resolved. Error message: ‘AADSTS65002: Consent between first social gathering functions and sources have to be configured through preauthorization. Visit for particulars
- When utilizing the SPN to grant rights, you have to use the development
CREATE USER [user] WITH SID = $sid, TYPE=X;
- When granting entry to a Database to a SPN/MSI, it doesn’t work. You want so as to add these to a bunch as properly and add the group
- For some bizarre, frantic and completely unusual motive, you can not run Entity Framework Database migration when utilizing an MSI accesstoken. When including the MSI to the SQL Server Admin Group, it really works..If anyone has an concept why that is, or the best way to overcome this, I’m open for it !
So again to work. We have to create an AAD Group (for DbReaders), add the MSI to the AAD Group, after which grant entry to this AAD Group within the SQL DB. Below you discover the script with inline feedback.
$spnobjectid = $($adsqlspn.objectId)
$spnpassword = (az advert sp credential reset --name $sqladminSPN | ConvertFrom-Json).password
$tenantid = (az account present | ConvertFrom-Json).tenantId
$advertReaderGroupTitle = "blog-db-users"
#create a db reader group
az advert group create --display-name $advertReaderGroupTitle --mail-nickname $advertReaderGroupTitle
$adgroupreaders = az advert group present --group $advertReaderGroupTitle | ConvertFrom-Json
#Add MSI to this group
$msiObjectId = (az webapp identification present --name $webapp --resource-group $resourcegroup | ConvertFrom-Json).principalId
az advert group member add --group $($adgroupreaders.objectId) --member-id $msiObjectId
#For no matter motive, the MSI must be within the AAD group, in any other case you can not run the Entity Framework Database Migration
az advert group member add --group $($adgroup.objectId) --member-id $msiObjectId
#---------------------------------------------------------------------------
#Execute as SPN
#---------------------------------------------------------------------------
#login with SPN with adequate rights on the subscription. Pipeline SPN ought to 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.home windows.internet;Initial Catalog=$databasename"
$SqlConnection.AccessToken = $token.entryToken
# Create a SQL connection to the Master Database
$SqlConnectionMaster = New-Object System.Data.SqlClient.SqlConnection
$SqlConnectionMaster.ConnectionString = "Data Source=$sqlservername.database.home windows.internet;Initial Catalog=grasp"
$SqlConnectionMaster.AccessToken = $token.entryToken
# Create a SID for the thing ID of the AAD Db Reader Group
$question = ""
$question = $question + "SELECT CONVERT(VARCHAR(1000), CAST(CAST('$($adgroupreaders.objectId)' AS UNIQUEIDENTIFIER) AS VARBINARY(16)),1) SID;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $question
$SqlCmd.Connection = $SqlConnection
$SqlConnection.Open()
$sid = $SqlCmd.ExecuteScalar()
$SqlConnection.Close()
Write-Host "SID Web App MSI [$webapp] is [$sid]"
# Set MSI in proper teams
$questionMaster = ""
$questionMaster = $questionMaster + "DROP USER IF EXISTS [[$adReaderGroupName];"
$questionMaster = $questionMaster + "CREATE USER [$adReaderGroupName] WITH SID = $sid, TYPE=X;"
$questionMaster = $questionMaster + "ALTER ROLE db_owner ADD MEMBER [$adReaderGroupName];"
$question = ""
$question = $question + "DROP USER IF EXISTS [$adReaderGroupName];"
$question = $question + "CREATE USER [$adReaderGroupName] WITH SID = $sid, TYPE=X;"
$question = $question + "ALTER ROLE db_owner ADD MEMBER [$adReaderGroupName];"
$SqlCmdMaster = New-Object System.Data.SqlClient.SqlCommand
$SqlCmdMaster.Connection = $SqlConnectionMaster
$SqlCmdMaster.CommandText = $questionMaster
$SqlConnectionMaster.Open()
$SqlCmdMaster.ExecuteNonQuery()
$SqlConnectionMaster.Close()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $question
$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’ve got provisioned the database, and granted entry to the Managed Service Identity, we must always check it with an actual net utility.
I’ve used the Web Application from the pattern described in these 2 posts
To simplify issues, I’ve added all neccessary modifications in a forked repo.
- Clone this repo
- Change the connectionstring in appsettings.json.
- Point the to the SQL Servername
- Point the to the Database identify
- If your MSI is utilized in a number of subscriptions, change the id within the MyDatabaseContext.cs file
- Open the Solution in Visual Studio
- Right Click / Publish your resolution to the provisioned Web App
When you printed the Web Application, go to the url.. and Voila!
Hope this helps!
To get the complete Powershell script for provisioning, please discover the next Gist