Use Azure Powershell Runbook to add AzureAD user as db_owner to an Azure SQL database

Question

I have a Powershell Runbook where I am trying to add AzureAD user as Database owner on an Azure SQL database.

## Connect
$servicePrincipalConnection = Get-AutomationConnection -Name "AzureRunAsConnection"
Connect-AzureAD `
    -TenantId $servicePrincipalConnection.TenantId `
    -ApplicationId $servicePrincipalConnection.ApplicationId `
    -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint 

## Generate new access token
$cert = Get-AutomationCertificate -Name 'AzureRunAsCertificate'
# Set Resource URI to Azure Database
$resourceAppIdURI = 'https://database.windows.net/'
# Set Authority to Azure AD Tenant
$authority = 'https://login.windows.net/' + $servicePrincipalConnection.TenantId
$ClientCred = [Microsoft.IdentityModel.Clients.ActiveDirectory.ClientAssertionCertificate]::new($servicePrincipalConnection.ApplicationId, $cert)
$authContext = [Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext]::new($authority)
$authResult = $authContext.AcquireTokenAsync($resourceAppIdURI, $ClientCred)
$AccessToken = $authResult.Result.AccessToken

## Execute sql
$AccessToken
$connectionString = "Data Source=MYCOOLSQLSERVER.database.windows.net;Initial Catalog=MYCOOLDATABASE;Connect Timeout=30"
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)
$query = "Create User [abc@xyz.com] From EXTERNAL PROVIDER;"

$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $connection)
$connection.AccessToken = $AccessToken
$connection.Open()
$command.ExecuteNonQuery()
$connection.Close()

I end up getting the error below where abc@xyz.com is an AzureAD user.

Principal 'abc@xyz.com' could not be resolved.

Is there something that I have missed out?

Once the user gets created, I intend to use Alter role to make him the db_owner.

References: https://blogs.technet.microsoft.com/stefan_stranger/2018/06/06/connect-to-azure-sql-database-by-obtaining-a-token-from-azure-active-directory-aad/

https://techcommunity.microsoft.com/t5/Azure-Database-Support-Blog/Azure-SQL-Database-Token-based-authentication-with-PowerShell/ba-p/369078

Answer 1

Turns out that there is an undocumented way to do this. Discovered it with the help of Azure support team.

The SQL query to be used is actually:

CREATE USER [abc@xyz.com] WITH SID = $hexSid, Type E;

Here $hexSid can be obtained by the following SQL query

1. Get the ObjectId of the AzureAD User

$objectId = (Get-AzureADUser -ObjectId "abc@xyz.com").ObjectId

2. Use the SQL below to get the sid

DECLARE @sid = uniqueidentifier = cast('$objectId' as uniqueidentifier) select cast(@sid as varbinary(max))

3. Now ideally, the effective query would have directly used the @sid but that is not possible since the With SID needs a hard coded value. So I had to process the output of the sid query separately like below:

$result = $command.ExecuteScalar() # result is byte array
$hexSid = ($result | ForEach-Object ToString X2) -Join ''  # convert to hex format
$hexSid = "0x$hexSid"

With those changes the code in the question just works fine!

Effectively, we are only passing the AzureAD User's objectId in the Create User query.

Complete code:

$targetUser = "abc@xyz.com"
$sqlServerName = "someserver"
$dbName = "somedb"
$accessToken = "" # obtained from SPN connection  

# Get SQL SID for the targeted user
Write-Output "Getting AzureADUser $targetUser "
$objectId = (Get-AzureADUser -ObjectId $targetUser).ObjectId
$sidQuery = "DECLARE @sid uniqueidentifier = cast('$objectId' as uniqueidentifier) select cast(@sid as varbinary(max))"
$sidOutput = New-Object -TypeName HashTable
Write-Output "Invoking SQL Query execution to get SID for objectId: $objectId ($targetUser)"
Invoke-OrExecuteSqlQuery -ServerName $sqlServerName -DbName $dbName -Query $sidQuery `
    -AccessToken $accessToken -Output $sidOutput
$sidBytes = $sidOutput.Result
Write-Output "SID bytes: $sidBytes" 
$hexSid = ($sidBytes | ForEach-Object ToString X2) -Join ''  # convert to hex format
$hexSid = "0x$hexSid"
Write-Output "SQL SID is : $hexSid"

# Add AzureAD user to the database
Write-Output "Adding AzureAD user : $targetUser to the db/dw: $dbName"
$createDBUserQuery = "CREATE USER [$targetUser] WITH SID = $hexSid, Type = E;" # create db user
$createUserOutput = New-Object -TypeName HashTable
Invoke-OrExecuteSqlQuery -ServerName $sqlServerName -DbName $dbName -Query $createDBUserQuery `
    -AccessToken $accessToken -Output $createUserOutput
Write-Output "Created DB User : $targetUser on the DB : $dbName"

# Set targeted user as the database owner
Write-Output "Setting database admin for db/dw: $dbName"
$makeDBOwnerQuery = "ALTER ROLE db_owner ADD MEMBER [$targetUser];"
$alterSQLDBRoleOutput = New-Object -TypeName HashTable
Invoke-OrExecuteSqlQuery -ServerName $sqlServerName -DbName $dbName -Query $makeDBOwnerQuery `
    -AccessToken $accessToken -Output $alterSQLDBRoleOutput
Write-Output "Made $targetUser as the DB Owner"

function Invoke-OrExecuteSqlQuery {
    param (
        [string] $ServerName,
        [string] $DbName,
        [string] $Query,
        [string] $AccessToken,
        [hashtable] $Output
    )
    $server = "$ServerName.database.windows.net"
    $database = $DbName
    $query = $Query

    Write-Output "Connecting to the SQL DB: $database on the SQL Server: $server"
    $connectionString = "Data Source=$server;Initial Catalog=$database;Connect Timeout=30;"
    $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)
    $connection.AccessToken = $AccessToken

    Write-Output "Executing SQL query.."
    $command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $connection)
    $connection.Open()
    $result = $command.ExecuteScalar()
    $connection.Close()
    Write-Output "Execution of SQL query complete"

    if ($result) {
        $Output.Result = $result
    }
}

Answer 2

for this scenario, we should use user password flow to make this command work. Pls try the PS below :

$tenant='<your tenant name/id>'
$username='<SQL admin account>'
$password='<SQL admin password>'
$appid='<app id>'
$appsec='<app secret>'

$SQLServerName = '<azure sql servername>'
$DatabaseName='<db name>'

$body=@{
    "grant_type"="password";
    "resource"="https://database.windows.net/";
    "client_id"=$appid;
    "client_secret"=$appsec;
    "username"=$username;
    "password" = $password;
}
 
$result=Invoke-RestMethod -Uri "https://login.windows.net/$tenant/oauth2/token" -Method POST -Body $body 



$conn = New-Object System.Data.SqlClient.SQLConnection 
$conn.ConnectionString = "Data Source=$SQLServerName.database.windows.net;Initial Catalog=$DatabaseName;Connect Timeout=30"
$conn.AccessToken = $result.access_token

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

$query = 'CREATE USER [Account] FROM EXTERNAL PROVIDER;'

$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $conn)     
$Result = $command.ExecuteScalar()
$Result
$conn.Close() 

Before you run is script, you should grant user_impersonation Azure SQL API access permission for your SPN in Azure AD here : Grant permission after added :

Btw, pls makre sure that the SQL admin account and SPN are all members of Active Directory admin group :

It works on my side and a record has been added successfully :

If there is anything unclear , pls feel free to let me know : )