ProvisionSQLMSI.ps1

# 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"