Arbeiten mit Managed Identities in Azure SQL

Paul Jeschke -

Dieser Blogpost beschreibt wie man im Azure eine Anwendung mit einer Datenbank verbindet mit Hilfe von Azure AD Authentifzierung und Managed Identities.

Erweitern der Rechte des Deployment Service Principal

Das Service Principal welches man für das Deployment seiner Anwendung benutzt über z.B. Azure DevOps, Github, etc., benötigt es die Owner Roller einer Azure AD Gruppe. Das Ziel dieser Gruppe ist es jedem Member in der Gruppe die Azure AD Rolle Directory-Readers zu geben. Dies wird später dem Deployment erlauben die Identität des SQL Servers in diese Gruppe automatisiert hinzuzufügen, so das dieser im Azure AD nach Identitäten suchen kann.

Deployment des SQL Dienstes

Das Deployment des SQL Servers ist zweistufig. Zuerst werden wir die Service Instance bereitstellen und dieser danach das Recht geben, im Azure AD nach anderen Identitäten zu suchen.

Service Instance bereitstellen

Bei dem bereitstellen der Service Instance ist darauf zu achten, dass in dem ARM Template die folgenden Eigenschaften aktiviert sind:

  • Azure SQL (Microsoft.Sql/servers)
"identity": {
    "type": "SystemAssigned"
}
  • Azure SQL for MySql (Microsoft.DBforMySQL/servers)‌‌
"identity": {
    "type": "SystemAssigned"
}
  • Azure SQL for Postgre SQL (Microsoft.DBforPostgreSQL/servers)
"identity": {
    "type": "SystemAssigned"
}

‌‌Dies vergibt während des Deployments im Azure Active Directory eine Managed Identity an den Server. Mit dieser Identity kann der Server mit dem AAD interagieren.‌‌Um die Id der Managed Identity abzufragen benötigt man eine PowerShell mit dem Module Az. Damit kann man dann folgenden Befehl ausführen:

(Get-AzResource -Name 'name des SQL Servers' -ResourceGroupName 'name der Ressourcen Gruppe').Identity.PrincipalId

Service Instance Lese-Rechte im AAD erteilen

Im vorhergehenden Schritt haben wir zum Schluss die Id der Managed Identity der Service Instance ausgelesen. Diese ID muss nun der Azure AD Gruppe hinzugefügt werden, welche im ersten Schritt erstellt wurde. Dafür benötigt man folgende PowerShell Function:

Note: Die folgende Function führt selbstständig die Befehle über die RestAPI aus, da das PowerShell Module AzureAd aktuell nicht mit Service Principals funktioniert.
function Add-ManagedIdentityToAADGroup {
    [CmdletBinding()]
    param (
        # The object id of your AAD group, you wanna add the managed identity to
        [Parameter(Mandatory = $true)]
        [string]
        $GroupId,
        # The Id of the Managed Identity your wanna add
        [Parameter(Mandatory = $true)]
        [string]
        $MemberId,
        # The client Id of your deployment service principal
	    [Parameter(Mandatory = $true)]
	    [string]
	    $ClientId,
	    # The client secret of your deplyoment service principal
	    [Parameter(Mandatory = $true)]
	    [string]
	    $ClientSecrect,
	    # The tenant Id of the landingzone
	    [Parameter(Mandatory = $false)]
	    [string]
	    $TenantId = '628242bd-7e70-4aa9-8ee1-72586b4540fe'
    )
    # oAuth body
    $body = "grant_type=client_credentials&client_id=$ClientId&client_secret=$ClientSecrect&scope=https://graph.microsoft.com/.default";
    # auth request with the service principal
    $Token = Invoke-RestMethod -Method Post -Uri "https://login.microsoftonline.com/$TenantId/oauth2/v2.0/token" -Body $body -ContentType 'application/x-www-form-urlencoded';
    # the JWT token with the AAD permissions
    $valueForAuthorizationHeader = "$($Token.token_type) $($Token.access_token)";
    # retrive the current group members from the group
    $currentGroupMembers = Invoke-RestMethod -Method Get -Uri "https://graph.microsoft.com/beta/groups/$GroupId/members" -ContentType 'application/json' -Headers @{Authorization= $valueForAuthorizationHeader};
    # check if the managed identity is already an member
    if($currentGroupMembers.value.Length -gt 0 -and $currentGroupMembers.value.id.Contains($MemberId)){
        # managed identity is already an member
	    Write-Verbose "Member already added.";
	    return;
    }else{
        # run the Post request to add the managed identity, because it is missing in the group
	    Invoke-RestMethod -Method Post -Uri "https://graph.microsoft.com/v1.0/groups/$GroupId/members/`$ref" -Body ('{
		"@odata.id": "https://graph.microsoft.com/v1.0/directoryObjects/' + $MemberId + '"
	  }') -ContentType 'application/json' -Headers @{Authorization= $valueForAuthorizationHeader};
    }
}

Diese Function kann man dann z.B. folgendermaßen verwenden:

Add-ManagedIdentityToAADGroup -GroupId 'c4f48ab3-1220-4714-9094-74442432589e' -MemberId '30947564-bbfb-4249-98a3-1ab5a68d8f24' -ClientId 'bae6792a-bf0b-47f6-ba7a-799328e95ce6' -ClientSecrect 'sdgkhjdfolghjahjehjpjöf' -Verbose

Das wird dazuführen, dass die Managed Identity 30947564-bbfb-4249-98a3-1ab5a68d8f24 (von der SQL Service Instance) zu der Gruppe c4f48ab3-1220-4714-9094-74442432589e (die Azure AD Gruppe mit der Rolle Diretory-Readers) hinzugefügt wird über das Service Principal bae6792a-bf0b-47f6-ba7a-799328e95ce6.

Warning: Bis die Berechtigung auf dem SQL Server und im AAD wirksam wird, können bis zu 15 min vergehen.

Apps deployen

Damit Apps sich über AAD authentifizieren können brauchen dieser erstmal wieder eine Managed Identity im Azure AD. Da es viele verschiedene Arten von Services gibt ist dieses Kapitel nur sehr grob beschrieben. Bei den meisten Azure Services reicht es allerdings im ARM Template folgendes Snipett mit hinzufügen:

"identity": {
    "type": "SystemAssigned"
}

Nach dem Deployment der App kann man die angelegte Identität wie folgt in den verschiedenen SQL Diensten anlegen:

App User im Azure SQL anlegen

Für Azure SQL braucht man nun lediglich den Namen seiner App-Ressource und kann diesen über T-SQL anlegen:

CREATE USER [name der app] FROM EXTERNAL PROVIDER

Um die Managed Identity benutzen zu können, kann man hier das Tutorial lesen. Ein Beispiel Connection-String sieht so aus:

Server=tcp:{sqlservername}.database.windows.net,1433;Initial Catalog={datenbank};Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;Authentication=Active Directory Managed Identity;

App User im Azure SQL for MySql anlegen

Für Azure SQL for MySql muss man nun die ObjectId für die Manged Identity für seine Azure Ressource herrausfinden. Dies geht am besten mit:

(Get-AzResource -Name 'name der app' -ResourceGroupName 'name der Ressourcen Gruppe').Identity.PrincipalId

Hat man nun die Id zu seiner App kann man nun diese über SQL anlegen:

CREATE AADUSER 'myuser' IDENTIFIED BY 'CLIENTID_Of_App';

Um die Managed Identity benutzen zu können, kann man hier das Tutorial lesen.

App User im Azure SQL for PostgreSQL anlegen

Für Azure SQL for PostgreSQL muss man nun die ObjectId für die Manged Identity für seine Azure Ressource herrausfinden. Dies geht am besten mit:

(Get-AzResource -Name 'name der app' -ResourceGroupName 'name der Ressourcen Gruppe').Identity.PrincipalId

Hat man nun die Id zu seiner App kann man nun diese über SQL anlegen:

CREATE ROLE myuser WITH LOGIN PASSWORD 'CLIENTID_Of_App' IN ROLE azure_ad_user;

Um die Managed Identity benutzen zu können, kann man hier das Tutorial lesen