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 [[email protected]] 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 [email protected] is an AzureAD user.

Principal '[email protected]' 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

Could you please tell me if you can run the command "Create User [[email protected]] From EXTERNAL PROVIDER;" in SSMS? — Jim Xu
Yes, it works through SSMS. But in SSMS I connect using my own AzureAD user whereas the script above uses SPN of the Automation account — bit

bit bit · Accepted Answer · 2019-09-10T13:08:32

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 [[email protected]] WITH SID = $hexSid, Type E;

Here $hexSid can be obtained by the following SQL query

Get the ObjectId of the AzureAD User

$objectId = (Get-AzureADUser -ObjectId "[email protected]").ObjectId

Use the SQL below to get the sid

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

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.

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

2 Answers