azureazure-sql-databaseazure-managed-identityazure-runbook

Can't connect to managed identity with User Administrator role

After applying the solution provided in this thread, which was basically adding User Administrator role to the managed identity I am using, I am getting the following error when connecting to the managed identity, which didn't happen before:

Unable to acquire token for tenant 'organizations' with error 'ManagedIdentityCredential authentication failed: Internal Server Error occured with identity passed!

Status: 500 (Internal Server Error)
   

Content:
   

Headers:

Transfer-Encoding: chunked

Content-Type: application/json; charset=utf-8

Date: Tue, 14 Nov 2023 12:34:53 GMT

Server: Microsoft-HTTPAPI/2.0


See the troubleshooting guide for more information. https://aka.ms/azsdk/net/identity/managedidentitycredential/troubleshoot'
ManagedIdentityCredential authentication failed: Internal Server Error occured with identity passed!

Status: 500 (Internal Server Error)



Content:


Headers:

Transfer-Encoding: chunked

Content-Type: application/json; charset=utf-8

Date: Tue, 14 Nov 2023 12:34:53 GMT

Server: Microsoft-HTTPAPI/2.0


See the troubleshooting guide for more information. https://aka.ms/azsdk/net/identity/managedidentitycredential/troubleshoot
Run Connect-AzAccount to login.

UPDATE Here's the automation account identity section: enter image description here

Here's the contributor role: enter image description here

Here's the user administrator role: enter image description here

Solution

  • The error might occur if you missed adding that user-assigned managed identity in your automation account, that you are specifying in AccountId parameter.

    I have one managed identity with Contributor role under subscription:

    enter image description here

    Now, I added User Administrator directory role to that user-assigned managed identity like this:

    enter image description here

    Initially, I have not added any user assigned managed identity in automation account as below:

    enter image description here

    When I ran below script to create SQL server by connecting via user assigned managed identity, I got same error as below:

    Disable-AzContextAutosave -Scope Process
$context = (Connect-AzAccount -Identity -AccountId "<account-client-id>").context 
$subscriptionId = "subId"
Select-AzSubscription -SubscriptionId $subscriptionId
$context = Set-AzContext -SubscriptionName $context.Subscription -DefaultProfile $context

$rgName = "Sri"
$newServerName = "sqlserver151123"
$location = "Central US"
$adminAccount = "Testuser"
New-AzSqlServer -ResourceGroupName $rgName -ServerName $newServerName -ServerVersion "12.0" -Location $location -AssignIdentity -EnableActiveDirectoryOnlyAuthentication -ExternalAdminName $adminAccount

    Response:

    enter image description here

    To resolve the error, make sure to add the user-assigned managed identity that you are specifying in AccountId under the automation account like this:

    enter image description here

    When I ran the same script again now, I got response successfully as below:

    Disable-AzContextAutosave -Scope Process
$context = (Connect-AzAccount -Identity -AccountId "<account-client-id>").context 
$subscriptionId = "subId"
Select-AzSubscription -SubscriptionId $subscriptionId
$context = Set-AzContext -SubscriptionName $context.Subscription -DefaultProfile $context

$rgName = "Sri"
$newServerName = "sqlserver151123"
$location = "Central US"
$adminAccount = "Testuser"
New-AzSqlServer -ResourceGroupName $rgName -ServerName $newServerName -ServerVersion "12.0" -Location $location -AssignIdentity -EnableActiveDirectoryOnlyAuthentication -ExternalAdminName $adminAccount

    Response:

    enter image description here

    To confirm that, I checked the same in Portal where SQL server created successfully with below properties:

    enter image description here