My colleagues and me are constructing a self-service portal for one in every of our clients. A couple of days in the past my colleague requested me if I might provision a Azure SQL Server and SQL Database for him. As a result of we wouldn’t have rights to do that manually on manufacturing (accurately!), this all must occur routinely.

The patron software is a Azure Net App. The Net App accesses the database. Regular structure, nothing to see right here. One of many nice security measures in Azure and Azure Energetic Listing is the Managed Service Identification. This makes it attainable to entry Azure assets with a particular Service Principal that’s managed by Azure. That implies that you don’t want to fret about Key rotation, expiration and most significantly, retaining monitor of usernames and passwords in your code.

After I set to work, the provisioning of the net 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 Administration Studio (guide!) with your personal person account.

What I needed, is the next straightforward setup.

  1. A script runs from the pipeline that creates the SQL Server and database
  2. The pipeline person turns into an AAD admin on the SQL Server
  3. A script runs to create and deploy a Azure Net App
  4. The Azure Net App is assigned a Managed Service Identification
  5. The MSI is granted entry to the SQL DB, in order that the Net App can get information. The pipeline SPN (SPN A), can grant this entry (as a SQL Admin)
  6. The MSI (MSI B) is person within the database

This appears trivial, however I can guarantee you, it isn’t. Let me describe the steps to make this occur.

Organising Conditions

Organising the database might be completed with ARM, Powershell, Azure CLI and so on. So long as you possibly can run it from the pipeline it’s good. With a view to run issues from a pipeline, or a minimum of simulate this, we’d like 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

Organising the SQL Server database

Organising the database might be completed with ARM, Powershell, Azure CLI and so on. So long 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 ' | Choose-Object -ExpandProperty Content material

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’ve got created a SQL server database can we add the MSI? No. As a result of 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. That is straightforward from the command line and the portal. Nevertheless, a person account is one thing you can’t and shouldn’t use from the pipeline. My person account has Multi-Issue Authentication so it isn’t even attainable, however even in case you don’t MFA, you shouldn’t use person accounts in your pipeline. We need to have a SPN as AAD admin. The pipeline person.

Sadly, the Azure Portal and CLI doesn’t let you add a SPN as AAD Admin. You possibly 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 record --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. strive once more if this occurs.
$adsqlspn = az advert sp record --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 Net App and Managed Service Identification

Now that we’ve got a SQL Server and likewise a database, we have to grant entry to the Managed Service Identification. Earlier than we do this, we have to create a Net Software 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 Identification
az webapp id assign --resource-group $resourcegroup --name $webapp

Now we’ve got assigned an Identification, we have to add this to SQL Server. In lots of the documentation on the web, you will note 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 software 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 an extended search, I discovered 4 issues

  • The SQL Server Admin SPN can not entry AD. If you attempt to use the development `FROM EXTERNAL PROVIDER’ you get a nasty error. If 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 occasion functions and assets should be configured through preauthorization. Go to 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 might want to add these to a gaggle as properly and add the group
  • For some bizarre, frantic and completely unusual cause, you can’t 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 how one can 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. Under 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
$adReaderGroupName = "blog-db-users"

#create a db reader group
az advert group create --display-name $adReaderGroupName --mail-nickname $adReaderGroupName
$adgroupreaders = az advert group present --group $adReaderGroupName | ConvertFrom-Json

#Add MSI to this group
$msiObjectId = (az webapp id present --name $webapp --resource-group $resourcegroup | ConvertFrom-Json).principalId
az advert group member add --group $($adgroupreaders.objectId) --member-id $msiObjectId

#For no matter cause, the MSI must be within the AAD group, in any other case you can't run the Entity Framework Database Migration
az advert group member add --group $($adgroup.objectId) --member-id $msiObjectId


#---------------------------------------------------------------------------
#Execute as SPN
#---------------------------------------------------------------------------
#login with SPN with ample 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 Entry 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 Inexperienced

# Create a SQL connection to the Person Database
$SqlConnection = New-Object System.Information.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Information Supply=$sqlservername.database.home windows.web;Preliminary Catalog=$databasename"
$SqlConnection.AccessToken = $token.accessToken

# Create a SQL connection to the Grasp Database
$SqlConnectionMaster = New-Object System.Information.SqlClient.SqlConnection
$SqlConnectionMaster.ConnectionString = "Information Supply=$sqlservername.database.home windows.web;Preliminary Catalog=grasp"
$SqlConnectionMaster.AccessToken = $token.accessToken

# Create a SID for the item 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.Information.SqlClient.SqlCommand
$SqlCmd.CommandText = $question
$SqlCmd.Connection = $SqlConnection
$SqlConnection.Open()
$sid = $SqlCmd.ExecuteScalar()
$SqlConnection.Shut()
Write-Host "SID Net App MSI [$webapp] is [$sid]"

# Set MSI in proper teams
$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];"

$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.Information.SqlClient.SqlCommand
$SqlCmdMaster.Connection = $SqlConnectionMaster
$SqlCmdMaster.CommandText = $queryMaster
$SqlConnectionMaster.Open()
$SqlCmdMaster.ExecuteNonQuery()
$SqlConnectionMaster.Shut()

$SqlCmd = New-Object System.Information.SqlClient.SqlCommand
$SqlCmd.CommandText = $question
$SqlCmd.Connection = $SqlConnection
$SqlConnection.Open()
$SqlCmd.ExecuteNonQuery()
$SqlConnection.Shut()

Write-Host "Added Net App MSI [$adReaderGroupName] to DB"

Working the Net Software

Now that you’ve got provisioned the database, and granted entry to the Managed Service Identification, we should always take a look at it with an actual internet software.

I’ve used the Net Software 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.
    • Level the to the SQL Servername
    • Level the to the Database title
  • In case your MSI is utilized in a number of subscriptions, change the id within the MyDatabaseContext.cs file
  • Open the Resolution in Visible Studio
  • Proper Click on / Publish your answer to the provisioned Net App

If you printed the Net Software, go to the url.. and Voila!

Hope this helps!

To get the complete Powershell script for provisioning, please discover the next Gist

# Login with you Azure account that has rights to create SPN’s
$sqladminSPN = “blog-sql-admin“
az login
az advert app create —show–title $sqladminSPN
#Organising SQL
$resourcegroup = “rg-blog-sql“
$sqlservername = “blog-sqlsrv-rvo“
$location = “westeurope“
$databasename=“tododb“
az group create —title $resourcegroup —location $location
az sql server create —useful resource–group $resourcegroup —title $sqlservername —admin–person sqladmin —admin–password <password> —location $location
az sql db create —title $databasename —server $sqlservername —useful resource–group $resourcegroup
#Firewall ports
$clientIp = Invoke-WebRequest ‘https://api.ipify.org‘ | Choose-Object –ExpandProperty Content material
az sql server firewall–rule create —useful resource–group $resourcegroup —server $sqlservername —title azure —start-ip–deal with 0.0.0.0 —finish–ip–deal with 0.0.0.0
az sql server firewall–rule create —useful resource–group $resourcegroup —server $sqlservername —title clientip1 —start-ip–deal with $clientIp —finish–ip–deal with $clientIp
#Safety Teams
$sqladmingroupname = “blog-sql-admingroup“
#Create a Group
az advert group create —show–title $sqladmingroupname —mail–nickname $sqladmingroupname
$adgroup = az advert group present —group $sqladmingroupname | ConvertFrom-Json
#Create an App Registration
$adsqlapp = az advert app record —show–title $sqladminSPN —question [] | 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. strive once more if this occurs.
$adsqlspn = az advert sp record —show–title $sqladminSPN —question [] | 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 advert–admin create —useful resource–group $resourcegroup —server–title $sqlservername —show–title “$sqladmingroupname“ —object–id $($adgroup.objectId)
# Net App
$appplan=“todowebplan“
$webapp=“blogtodoweb“
#Create AppServicePlan
az appservice plan create —useful resource–group $resourcegroup —title $appplan —sku S1
#Create WebApp
az webapp create —useful resource–group $resourcegroup —title $webapp —plan $appplan
#Get Identification
az webapp id assign —useful resource–group $resourcegroup —title $webapp
$spnobjectid = $($adsqlspn.objectId)
$spnpassword = (az advert sp credential reset —title $sqladminSPN | ConvertFrom-Json).password
$tenantid = (az account present | ConvertFrom-Json).tenantId
$adReaderGroupName = “blog-db-users“
#create a db reader group
az advert group create —show–title $adReaderGroupName —mail–nickname $adReaderGroupName
$adgroupreaders = az advert group present —group $adReaderGroupName | ConvertFrom-Json
#Add MSI to this group
$msiObjectId = (az webapp id present —title $webapp —useful resource–group $resourcegroup | ConvertFrom-Json).principalId
az advert group member add —group $($adgroupreaders.objectId) —member–id $msiObjectId
#For no matter cause, the MSI must be within the AAD group, in any other case you can’t run the Entity Framework Database Migration
az advert group member add —group $($adgroup.objectId) —member–id $msiObjectId
#—————————————————————————
#Execute as SPN
#—————————————————————————
#login with SPN with ample rights on the subscription. Pipeline SPN ought to have this
az logout
az login —service–principal –u $($adsqlspn.appId) –p $spnpassword —tenant $tenantid —enable–no–subscriptions
# Get Entry Token for the database
$token = az account get-access–token —useful resource https://database.home windows.web/ | ConvertFrom-Json
Write-Host “Retrieved JWT token for SPN [$spnobjectid]“
Write-Host “AccessToken [$($token.accessToken)]“ –ForegroundColor Inexperienced
# Create a SQL connection to the Person Database
$SqlConnection = New-Object System.Information.SqlClient.SqlConnection
$SqlConnection.ConnectionString = “Information Supply=$sqlservername.database.home windows.web;Preliminary Catalog=$databasename“
$SqlConnection.AccessToken = $token.accessToken
# Create a SQL connection to the Grasp Database
$SqlConnectionMaster = New-Object System.Information.SqlClient.SqlConnection
$SqlConnectionMaster.ConnectionString = “Information Supply=$sqlservername.database.home windows.web;Preliminary Catalog=grasp“
$SqlConnectionMaster.AccessToken = $token.accessToken
# Create a SID for the item 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.Information.SqlClient.SqlCommand
$SqlCmd.CommandText = $question
$SqlCmd.Connection = $SqlConnection
$SqlConnection.Open()
$sid = $SqlCmd.ExecuteScalar()
$SqlConnection.Shut()
Write-Host “SID Net App MSI [$webapp] is [$sid]“
# Set MSI in proper teams
$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];“
$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.Information.SqlClient.SqlCommand
$SqlCmdMaster.Connection = $SqlConnectionMaster
$SqlCmdMaster.CommandText = $queryMaster
$SqlConnectionMaster.Open()
$SqlCmdMaster.ExecuteNonQuery()
$SqlConnectionMaster.Shut()
$SqlCmd = New-Object System.Information.SqlClient.SqlCommand
$SqlCmd.CommandText = $question
$SqlCmd.Connection = $SqlConnection
$SqlConnection.Open()
$SqlCmd.ExecuteNonQuery()
$SqlConnection.Shut()
Write-Host “Added Net App MSI [$adReaderGroupName] to DB“

Source link

Related posts:

The Maktab Madrasah System – A Very Important System!
Les 5 principes pour concevoir une bonne interface vocale
Patrick Debois talks about changing the quality of life of others - Yves Hanoulle
Voraussetzung um in den flow zu kommen
Difference between Microservices, API, and API Gateway
8 Tipps um die remote Kommunikationsbereitschaft in Team zu fördern
The new Scrum Guide: Less is more? - Coach Lankford
Remembering Jerry Weinberg: Perfect Software… and other illusions about testing
Do I want feedback?
What is git? How does it work?
Agile thinking for Impediment resolution!
Creating the foundation of trust
Tags: , , , , , , ,