Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
# 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"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment