azure user-assigned identity for azure sql fails

Question

I am trying to use a user assigned identity (from a Azure container instance) to connect to an Azure sql database.

following this guide: https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/tutorial-windows-vm-access-sql

it works for System assigned identities, but not for user assigned ones. when trying to create the contained user, using the user assigned identity i get this error

Msg 33134, Level 16, State 1, Line 1
Principal 'user-assigned-identity-name' could not be resolved. Error message: 'AADSTS7000219: 'client_assertion' or 'client_secret' is required for the 'urn:ietf:params:oauth:grant-type:jwt-bearer' grant type

Am i missing something here or have i misunderstood user assigned identities? Can they be used to login in to and query sql server?

Pamela Peng Pamela Peng · Accepted Answer · 2020-06-11T05:58:27

The guide you followed is used for system-assigned managed identity, because it enables you to authenticate to services that support Azure AD authentication, without needing to insert credentials into your code. But credential is necessary for user-assigned managed identity.

You could refer to this, and use the way to get access_token.

$response = Invoke-WebRequest -Uri 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&client_id=af825a31-b0e0-471f-baea-96de555632f9&resource=https://management.azure.com/' -Method GET -Headers @{Metadata="true"}
$content = $response.Content | ConvertFrom-Json
$ArmToken = $content.access_token

azure user-assigned identity for azure sql fails

1 Answers