Questions on using ActiveDirectoryManagedIdentity

[arvindshmicrosoft]

The documentation states that when ActiveDirectoryManagedIdentity is used, we need to specify the Managed Identity name as the user name in sqlcmd. So I tried:

.\sqlcmd.exe --authentication-method=ActiveDirectoryManagedIdentity -U vm_msi_name -S someserver.database.windows.net

Unfortunately, this errors out with the message The requested identity isn't assigned to this resource

On a hunch, I tried without the -U parameter:

.\sqlcmd.exe --authentication-method=ActiveDirectoryManagedIdentity -S someserver.database.windows.net

... and it worked. So it seems that specifying the managed identity name may not be mandatory. If this is true, can the README / docs be updated?

As a follow up question, if we do need to specify the managed identity name, how can we disambiguate when multiple identities have the same name, but have different client IDs? Can we allow for either the name, or the client ID, being provided to sqlcmd? For example, the Azure Portal Azure Active Directory - All applications blade does allow searching by either name or client ID.

[shueybubbles]

is it a user-assigned identity, or system-assigned? System-assigned doesn't need user name set.
I recommend using ActiveDirectoryDefault and setting environment variables per https://github.com/Azure/azure-sdk-for-go/tree/main/sdk/azidentity#defaultazurecredential

[arvindshmicrosoft]

is it a user-assigned identity, or system-assigned? System-assigned doesn't need user name set. I recommend using ActiveDirectoryDefault and setting environment variables per https://github.com/Azure/azure-sdk-for-go/tree/main/sdk/azidentity#defaultazurecredential

It is a user-assigned managed identity. I don't prefer setting environment variables etc. - that defeats the whole purpose of using managed identities. As I mentioned, I was unblocked by not using -U. But it would be good to get this to work correctly for user-assigned managed identities.

[shueybubbles]

I don't know how it's able to authenticate a user-assigned identity without providing the client id. I thought it only worked that way for system-assigned.

We pass the user name to the azidentity code here

go-sqlcmd/pkg/sqlcmd/azure_auth.go

Line 78 in 32462b7

cred, err = azidentity.NewManagedIdentityCredential(user, nil)

Using ActiveDirectoryDefault is generally more friendly to using the same script in your dev machine and your production environment. I find it lets me connect to my Azure DBs without extra prompts on my dev machine.

[dlevy-msft-sql]

Closing as resolved. The documentation has been updated to clarify managed identity usage:

• System-assigned identity: Leave username empty (no -U parameter needed)
• User-assigned identity: Set username to the client ID of the managed identity

See the current README section on \ActiveDirectoryManagedIdentity\ for the updated guidance.