search

microsoft / go-sqlcmd

The new sqlcmd, CLI for SQL Server and Azure SQL (winget install sqlcmd / sqlcmd create mssql / sqlcmd open ads)
https://learn.microsoft.com/sql/tools/sqlcmd/go-sqlcmd-utility
MIT License
395 stars 63 forks source link

Questions on using ActiveDirectoryManagedIdentity #38

Open arvindshmicrosoft opened 2 years ago

arvindshmicrosoft commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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 https://github.com/microsoft/go-sqlcmd/blob/32462b7a4e59f35479550702032763ba1a66905a/pkg/sqlcmd/azure_auth.go#L78

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.