What permissions are needed for an Azure Runbook to execute the SQL command CREATE USER FROM EXTERNAL PROVIDER

Hi friend,

The error message you're encountering indicates an issue with the resolution of the principal 'mike.w-------@-----.com'. This might be caused by several factors related to Azure Active Directory (AAD) or Microsoft Entra ID configurations. Here are some steps to troubleshoot and resolve the issue:

1. Verify User Principal Name (UPN): Ensure that the UPN mike.w-------@-----.com is correct and exists in the directory. You can do this by searching for the user in the Azure portal under Azure Active Directory > Users.
2. Check Application Registration: The error message suggests that the application with identifier c55cf4e8-b97f-452f-a445-daebb9dfcaf8 was not found. This might indicate an issue with the Azure AD application registration used for authentication. Ensure the application is correctly registered in the Azure AD tenant.
3. Directory Permissions: Verify that the user-assigned managed identity (UAMI) and system-assigned managed identity (SAMI) have the necessary permissions in Azure AD. They should have the Directory.Read.All permission if they need to read directory information.
4. Correct Tenant Context: Ensure that the authentication request is being sent to the correct tenant. Sometimes, the default tenant context might not be correctly set. Verify the tenant ID and ensure your script is connecting to the right Azure AD tenant.
5. Token Acquisition: Confirm that the access token is correctly acquired for Azure SQL Database and that it includes the necessary scopes/permissions. Here is an improved script with additional error handling and logging:

# 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"
# Connect using the Managed Service Identity of the named user-assigned managed identity
$identity = Get-AzUserAssignedIdentity -ResourceGroupName $ResourceGroup -Name $UAMI -SubscriptionId $SubscriptionId
Write-Output "Identity: " $identity.name
# Validate assignment
$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
# Get the access token for Azure SQL
try {
    $access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token 
}
catch {
    Write-Output "Failed to acquire access token for Azure SQL"
    exit
}
# Construct the query
$query = 'CREATE USER [mike.w-------@-----.com] FROM EXTERNAL PROVIDER'
# Execute the query
try {
    Invoke-Sqlcmd -ServerInstance $TargetServerName -AccessToken $access_token -Database $DatabaseName -Query $query
} catch {
    Write-Output "Failed to execute SQL command: $_"
    exit
}

6. Check SQL Database Permissions: Ensure that the managed identity has the db_owner role on the SQL database. You can verify this by connecting to the database and running a query to list the database roles for the managed identity.

SELECT dp.name, dp.type_desc, p.permission_name
FROM sys.database_principals AS dp
LEFT JOIN sys.database_permissions AS p ON dp.principal_id = p.grantee_principal_id
WHERE dp.name = 'sql-prod-db-id';

By following these steps, you should be able to identify the root cause of the issue and resolve it. If the problem persists, consider checking the Azure Activity Logs for any additional details or error messages related to the operation. I hope you have a nice day!!

@Mike Welborn, thank you for the reply and detailed information.

I assume that you had allocated these permissions (directory read and db_Owner) to Azure Automation Account's managed identity. However, the directory/user read permission is required for the sql server's managed identity for it to resolve the user. The following information, sourced from this question should help in this case where I had tested the complete scenario.

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.