AAD user creation on behalf AAD Service Principal with Azure SQL DB

An interesting improvement was announced by the SQL AAD team on Monday 27th July 2020 and concerns the support for Azure AD user creation on behalf of Azure AD Applications for Azure SQL as mentioned to this Microsoft blog post.

In my company, this is something we were looking for a while with our database refresh process in Azure. Before talking this new feature, let me share a brief history of different considerations we had for this DB refresh process over the time with different approaches we went through. First let’s precise DB Refresh includes usually at least two steps: restoring backup / copying database – you have both ways in Azure SQL Database – and realigning security context with specific users regarding your targeted environment (ACC / INT …). But the latter is not as trivial as you may expect if you opted to use either a SQL Login / User or a Service Principal to carry out this operation in your process. Indeed, in both cases creating an Azure AD User or Group is not supported, and if you try you will face this error message:

‘’ is not a valid login or you do not have permission.

All the stuff (either Azure automation runbook and PowerShell modules on-prem) done so far and described afterwards meets the same following process:

164 - 1 - DB Refresh process

First, we used Invoke-SQCMD in Azure Automation runbook with T-SQL query to create a copy of a source database to the target server. T-SQL is mandatory in this case as per documented in the Microsoft BOL because PROD and ACC or INT servers are not on the same subscription. Here a simplified sample of code:

...
$CopyDBCMD = @{
    'Database' = 'master'
    'ServerInstance' = $TargetServerName
    'Username' = $SQLUser
    'Password' = $SQLPWD
    'Query' = 'CREATE DATABASE '+ '[' + $DatabaseName + '] ' + 'AS COPY OF ' + '[' + $SourceServerName + '].[' + $DatabaseName + ']'
}

Invoke-Sqlcmd @CopyDBCMD
...

But as you likely know, Invoke-SQLCMD doesn’t support AAD authentication and because SQL Login authentication was the only option here, it led us dealing with an annoying issue about the security configuration step with AAD users or groups as you may imagine.

Then, because we based authentication mainly on trust architecture and our security rules require using it including apps with managed identities or service principals, we wanted also to introduce this concept to our database refresh process. Fortunately, service principals are supported with Azure SQL DBs since v12 with access token for authentication by ADALSQL. The corresponding DLL is required on your server or if you use it from Azure Automation like us, we added the ADAL.PS module but be aware it is now deprecated, and I advise you to strongly invest in moving to MSAL. Here a sample we used:

...
$response = Get-ADALToken `
                -ClientId $clientId `
                -ClientSecret $clientSecret `
                -Resource $resourceUri `
                -Authority $authorityUri `
                -TenantId $tenantName

...

$connectionString = "Server=tcp:$SqlInstanceFQDN,1433;Initial Catalog=master;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;"
# Create the connection object
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
# Set AAD generated token to SQL connection token
$connection.AccessToken = $response.AccessToken

Try {
    $connection.Open()
    ...
}
...

But again, even if the copy or restore steps are well managed, we still got stuck with security reconfiguration, because service principals were not supported for creating AAD users or groups so far …

In the meantime, we found out a temporary and interesting solution based on dbatools framework and the Invoke-dbaquery command which supports AAD authentication (Login + Password). As we may not rely on service principal in this case, using a dedicated AAD account was an acceptable tradeoff to manage all the database refresh process steps. But going through this way comes with some disadvantages because running Invoke-dbaquery in a full Azure automation mode is not possible with missing ADALsql.dll. Workaround may be to use hybrid-worker, but we didn’t want to add complexity to our current architecture only for this special case. Instead we decided to move the logic of the Azure automation runbook into on-prem PowerShell framework which already include logic for DB refresh for on-prem SQL Server instances.

Here a simplified sample of code we are using:

...
Try {
    # Connect to get access to Key Vault info
    Connect-AzAccount | Out-Null

    [String]$user = (Get-AzKeyVaultSecret -VaultName $KeyvaultName -Name "AZSQL-SQLBCKUSER").SecretValueText
    [System.Security.SecureString]$pwd =  ConvertTo-SecureString (Get-AzKeyVaultSecret -VaultName $KeyvaultName -Name "AZSQL-SQLBCKPWD").SecretValueText -AsPlainText -Force
    [String]$SourceServerName = (Get-AzKeyVaultSecret -VaultName $KeyvaultName -Name "AZSQL-NAME").SecretValueText
    [String]$TargetServerName = (Get-AzKeyVaultSecret -VaultName $KeyvaultName -Name "AZSQL-TARGETNAME").SecretValueText + '.database.windows.net'

    # DB Restore will be performed in the context of dedicated AAD account
    $pscredential = New-Object -TypeName System.Management.Automation.PSCredential($user, $pwd)

    Write-Host "Restoring DB:$DatabaseName from Source Server: $SourceServerName to Target Server: $TargetServerName"
   
    $Query = "CREATE DATABASE [$DatabaseName] AS COPY OF [$SourceServerName].[$DatabaseName]"
    Invoke-DbaQuery `
        -SqlInstance $TargetServerName `
        -Database master `
        -SqlCredential $pscredential `
        -Query $Query `
        -EnableException

    # Wait for DB online and ready ...
    # Code should be implemented for this check


    Write-Output "Applying security configuration to DB: $DatabaseName on Server:$TargetServerName"

    $Query = "
        DROP USER [az_sql_ro];CREATE USER [az_sql_ro] FROM EXTERNAL PROVIDER;
    "
    Invoke-DbaQuery `
        -SqlInstance $TargetServerName `
        -Database $DatabaseName `
        -SqlCredential $pscredential `
        -Query $Query `
        -EnableException

}
Catch {
    Write-Host "Error encountered: $($_.Exception.Message)"
}
...

Referring to the PowerShell code above, in the second step, we create an AAG group [az_sql_ro] on behalf of the AAD dedicated account with the CLAUSE FROM EXTERNAL PROVIDER.

Finally, with the latest news published by the SQL AAD team, we will likely consider using back service principal instead of dedicated Windows AAD account. This Microsoft blog post explains in details how it works and what you have to setup to make it work correctly. I don’t want to duplicate what is already explained so I will apply the new stuff to my context.

Referring to the above blog post, you need first to setup a server identity for your Azure SQL Server as below:

Set-AzSqlServer `
    -ResourceGroupName sandox-rg `
    -ServerName a-s-sql02 `
    -AssignIdentity

# Check server identity
Get-AzSqlServer `
    -ResourceGroupName sandox-rg `
    -ServerName a-s-sql02 | `
    Select-Object ServerName, Identity
ServerName Identity                                              
---------- --------                                              
a-s-sql02  Microsoft.Azure.Management.Sql.Models.ResourceIdentity

Let’s have a look at the server identity

# Get identity details
$identity = Get-AzSqlServer `
        -ResourceGroupName sandox-rg `
        -ServerName a-s-sql02

$identity.identity
PrincipalId                          Type           TenantId                            
-----------                          ----           --------                            
7f0d16f7-b172-4c97-94d3-34f0f7ed93cf SystemAssigned 2fcd19a7-ab24-4aef-802b-6851ef5d1ed5

In fact, assigning a server identity means creating a system assigned managed identity in the Azure AD tenant that’s trusted by the subscription of the instance. To keep things simple, let’s say that System Managed Identity in Azure is like to Managed Account or Group Managed Account on-prem. Those identities are self-managed by the system. Then you need to grant this identity the Azure AD « Directory Readers « permission to get rights for creating AAD Users or Groups on behalf of this identity. A PowerShell script is provided by Microsoft here a sample of code I applied in my context for testing:

...
Try {
    $DatabaseName = "test-DBA"  
     
    # Connect to get access to Key Vault info
    Connect-AzAccount | Out-Null

    [String]$user = (Get-AzKeyVaultSecret -VaultName $KeyvaultName -Name "AZSQL-SQLBCKAPPID").SecretValueText
    [System.Security.SecureString]$pwd =  ConvertTo-SecureString (Get-AzKeyVaultSecret -VaultName $KeyvaultName -Name "AZSQL-SQLBCKAPPSECRET").SecretValueText -AsPlainText -Force
    [String]$SourceServerName = (Get-AzKeyVaultSecret -VaultName $KeyvaultName -Name "AZSQL-NAME").SecretValueText
    [String]$TargetServerName = (Get-AzKeyVaultSecret -VaultName $KeyvaultName -Name "AZSQL-TARGETNAME").SecretValueText + '.database.windows.net'

    # DB Restore will be performed in the context of dedicated AAD account
    $pscredential = New-Object -TypeName System.Management.Automation.PSCredential($user, $pwd)

    $adalPath  = "${env:ProgramFiles}\WindowsPowerShell\Modules\Az.Profile.7.0\PreloadAssemblies"
    # To install the latest AzureRM.profile version execute  -Install-Module -Name AzureRM.profile
    $adal      = "$adalPath\Microsoft.IdentityModel.Clients.ActiveDirectory.dll"
    $adalforms = "$adalPath\Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms.dll"
    [System.Reflection.Assembly]::LoadFrom($adal) | Out-Null
    $resourceAppIdURI = 'https://database.windows.net/'

    # Set Authority to Azure AD Tenant
    $authority = 'https://login.windows.net/' + $tenantId

    $ClientCred = [Microsoft.IdentityModel.Clients.ActiveDirectory.ClientCredential]::new($clientId, $clientSecret)
    $authContext = [Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext]::new($authority)
    $authResult = $authContext.AcquireTokenAsync($resourceAppIdURI,$ClientCred)
    $Tok = $authResult.Result.CreateAuthorizationHeader()
    $Tok=$Tok.Replace("Bearer ","")
   
    Write-host "Token generated is ..."
    $Tok
    Write-host  ""

    Write-Host "Create SQL connectionstring"
    $conn = New-Object System.Data.SqlClient.SQLConnection
   
    $conn.ConnectionString = "Data Source=$TargetServerName;Initial Catalog=master;Connect Timeout=30"
    $conn.AccessToken = $Tok

    Write-host "Connect to database and execute SQL script"
    $conn.Open()

    Write-Host "Check connected user ..."
    $Query = "SELECT USER_NAME() AS [user_name];"
    $command = New-Object -TypeName System.Data.SqlClient.SqlCommand($Query, $conn)
    $Command.ExecuteScalar()
    $conn.Close()

    Write-Host "Restoring DB:$DatabaseName from Source Server: $SourceServerName to Target Server: $TargetServerName"

    $conn.ConnectionString = "Data Source=$TargetServerName;Initial Catalog=master;Connect Timeout=30"
    $conn.AccessToken = $Tok
    $conn.Open()
    $Query = "DROP DATABASE IF EXISTS [$DatabaseName]; CREATE DATABASE [$DatabaseName] AS COPY OF [$SourceServerName].[$DatabaseName]"
    $command = New-Object -TypeName System.Data.SqlClient.SqlCommand($Query, $conn)
    $command.CommandTimeout = 1200
    $command.ExecuteNonQuery()
    $conn.Close()

    # Wait for DB online and ready ...
    # Code should be implemented for this check

   
    Write-Output "Applying security configuration to DB: $DatabaseName on Server:$TargetServerName"

    $conn.ConnectionString = "Data Source=$TargetServerName;Initial Catalog=$DatabaseName;Connect Timeout=30"
    $conn.AccessToken = $Tok
    $conn.Open()
    $Query = 'CREATE USER [az_sql_ro] FROM EXTERNAL PROVIDER;'
    $command = New-Object -TypeName System.Data.SqlClient.SqlCommand($Query, $conn)      
    $command.ExecuteNonQuery()
    $conn.Close()

}
Catch {
    Write-Output "Error encountered: $($_.Exception.Message)"
}
...

Using service principal required few changes in my case. I now get credentials of the service principal (ClientId and Secret) from Azure Key Vault instead of the AAD dedicated account used in previous example. I also changed the way to connect to SQL Server by relying on ADALSQL to get the access token instead of using dbatools commands. Indeed, as far as I know, dbatools doesn’t support this authentication way (yet?).

The authentication process becomes as follows:

164 - 3 - new auth process

My first test seems to be relevant:

164 - 4 - test with SP

This improvement looks promise and may cover broader scenarios as the one I described in this blog post. This feature is in preview at the moment of this write-up and I hope to see it coming soon in GA as well as a potential support of preferred PowerShell framework DBAtools :)

See you!

Laisser un commentaire