Error attempting to CREATE USER FROM EXTERNAL PROVIDER using a User Assigned Managed Identity

Question

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

Answer 1

@Mike Welborn, apologies for the delayed response.

The GUID for which you are getting the error, seems to be the object/Application id of the Azure Automation Account or the SQL Server itself (or the user managed identity assigned to these entities).

You can search for this GUID in Azure portal --> Entra Id --> "Enterprise Applications"

Update the filter in this view to "Application type == Managed Identities", as shown below:

You should search here for the GUID.

I tested a complete scenario, and it seems that one of the steps to assign directory read permission to sql server's assigned identity was missed. When a Microsoft Entra user executes CREATE LOGIN or CREATE USERcommands, Azure SQL's Microsoft application uses delegated permissions to impersonate the signed-in user and queries Microsoft Graph using their permissions. For details, see Microsoft Entra service principals with Azure SQL

Therefore, the Assigned identity to Azure SQL (not the Automation Account's identity), should be granted permission to be able to read user property from EntraId.

Please run the script available in the following link by updating the managedIdentity name to Azure Sql's managed identity to ensure that it has the required directory/user read permissions - Managed identities in Microsoft Entra for Azure SQL: Grant Permission

After this step, the CREATE USER cmd should run successfully.

Hope this helps.

If the answer did not help, please add more context/follow-up question for it. Else, if the answer helped, please click Accept answer so that it can help others in the community looking for help on similar topics.