I am trying to authenticate to Azure SQL database using AAD user account. According to the documentation I should use -G
to enable AAD authentication and then pass the token via -P
. So I try it with the token from az like this (from git bash):
az account get-access-token --resource https://database.windows.net | jq -j .accessToken > @token
sqlcmd -S servername.database.windows.net -d databasename -G -P @token
but get
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Failed to authenticate the user 'jan.hudec' in Active Directory (Authentication option is 'ActiveDirectoryPassword').
Error code 0xCAA90018; state 10
Could not discover a user realm..
The username is notably wrong. Is there some way to tell it the realm?
When I decode the token, it correctly says
…"unique_name":"[email protected]","upn":"[email protected]"…
(Note: actual domain, server and database name redacted, the other bits are verbatim)
I tried it in SQL Server Management Studio with the Azure Active Directory – Universal with MFA authentication and it shows the AAD login dialog and then connects, so the server should be configured correctly. With permissions being granted by virtue of me being member of the AAD admin group for the server.
Best Answer
As specified in the Connecting with sqlcmd post, ensure the token is formatted properly so the incoming Azure SQL service interprets its value properly on that side. In addition, I quoted the referenced post below and bolded the sentences of importance for version requirement specifics.
Visit sqlcmd Utility to download the latest version of
sqlcmd
per theMicrosoft Command Line Utilities 15 for SQL Server
Microsoft provides to install onto your system.Note: See the Command Example section from the referenced post for a command which formats the token into the UTF format as outlined in that post.