I have an Azure Automation runbook (PowerShell 5.1) that attempts to run "CREATE USER <UserName> FROM EXTERNAL PROVIDER". This is part of a larger program, but this is the offending command so I created a test runbook to try and resolve this issue.
The script is
$ResourceGroup = "xxxx-xxx-xxxxxxxxxx-rg"
$UAMI = "sql-prod-db-id"
$automationAccount = "xx-xx-Automation"
$FullServerName = "xxxxsql01.database.windows.net"
$SubscriptionId = "xxxxxxxx-xxxx-xxxx-xxxx-2ad019f3319a"
$DatabaseName = 'xxxxx_New'
$TargetServerName = "xxxxsql01.database.windows.net"
# Ensures you do not inherit an AzContext in your runbook
$null = Disable-AzContextAutosave -Scope Process
# Connect using a Managed Service Identity
try {
$AzureConnection = (Connect-AzAccount -Identity).context
}
catch {
Write-Output "There is no system-assigned user identity. Aborting."
exit
}
# set and store context
$AzureContext = Set-AzContext -SubscriptionId $SubscriptionId -DefaultProfile $AzureConnection
Write-Output "Using user-assigned managed identity: $UAMI"
# Connects using the Managed Service Identity of the named user-assigned managed identity
$identity = Get-AzUserAssignedIdentity -ResourceGroupName $ResourceGroup -Name $UAMI -SubscriptionId $SubscriptionId #-DefaultProfile $AzureContext
Write-Output "Identity: " $identity.name
# validates assignment only, not perms
$AzAutomationAccount = Get-AzAutomationAccount -ResourceGroupName $ResourceGroup -Name $automationAccount -DefaultProfile $AzureContext
if ($AzAutomationAccount.Identity.UserAssignedIdentities.Values.PrincipalId.Contains($identity.PrincipalId))
{
$AzureConnection = (Connect-AzAccount -Identity -AccountId $identity.ClientId).context
# set and store context
$AzureContext = Set-AzContext -SubscriptionName $AzureConnection.Subscription -DefaultProfile $AzureConnection
}
else
{
Write-Output "Invalid or unassigned user-assigned managed identity"
exit
}
Write-Output "Account ID of current context: " $AzureContext.Account.Id
Write-Output "Get the access token for Azure SQL"
$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
$query = 'SELECT TOP 1 * FROM <schema>.Clinic'
Write-Output $query
Invoke-Sqlcmd -ServerInstance $TargetServerName -AccessToken $access_token -Database $DatabaseName -Query $query
$query = 'CREATE USER [mike.w-----@-----.com] FROM EXTERNAL PROVIDER'
Write-Output $query
Invoke-Sqlcmd -ServerInstance $TargetServerName -AccessToken $access_token -Database $DatabaseName -Query $query
The output from the
Using user-assigned managed identity: sql-prod-db-id
Identity:
sql-prod-db-id
Account ID of current context:
xxxxxxxx-xxxx-xxxx-xxxx-1eb8e29d5407
Get the access token for Azure SQL
SELECT TOP 1 * FROM <schema>.Clinic
ClinicId : xxxxxxxx-xxxx-xxxx-xxxx-0008a3e117f7
Email :
Fax :
Name : Hand & Upper Extremity Specialist ----------
Phone :
URL :
InactiveDate :
CreatedUserId : 00000000-0000-0000-0000-000000000000
UpdatedUserId : 00000000-0000-0000-0000-000000000000
CreatedDate : 3/22/2017 3:46:36 PM +00:00
UpdatedDate : 3/22/2017 3:46:36 PM +00:00
CREATE USER [mike.w-----@-----.com] FROM EXTERNAL PROVIDER
The runbook fails with the following message:
Invoke-Sqlcmd : Principal 'mike.w-----@-----.com' could not be resolved. Error message: 'AADSTS700016: Application with identifier 'c55cf4e8-b97f-452f-a445-daebb9dfcaf8' was not found in the directory '-----'. This can happen if the application has not been installed by the administrator of the tenant or consented to by any user in the tenant. You may have sent your authentication request to the wrong tenant. Trace ID: fdc66cc5-afc4-4643-bea1-049d79960700 Correlation ID: 568e2b90-1098-4379-a6ad-0648de5f5a11 Timestamp: 2024-05-13 14:30:07Z' Msg 33134, Level 16, State 1, Procedure , Line 1. At line:56 char:1 + Invoke-Sqlcmd -ServerInstance $TargetServerName -AccessToken $access_ ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
So to sum up the issue, I have a runbook that is authenticating with a user-assigned managed identity(UAMI): sql-prod-db-id
I confirm that I am using the UAMI that I think I am using.
This UAMI is a db_owner on the database
We can see that it can execute successfully the SELECT statement
When it attempts to execute the CREATE USER command, it fails with the message above.
It appears that the UAMI is failing when attempting to interact with Microsoft Entra Id
I checked Microsoft Entra Id and the UAMI has Directory Reader permissions
So I don't know why it is failing - presumably a permission is missing but I don't know which one.
Additionally there is a reference to "Application with identifier 'c55cf4e8-b97f-452f-a445-daebb9dfcaf8'" I can find that GUID anywhere in Azure. I did check All Applications but the GUID wasn't there. I have no idea how to identify this GUID.
Any help or direction would be greatly appreciated