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:
- 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. - 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. - 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. - 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.
- 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
}
- 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!!