Sqlcmd AAD token authentication fails with cannot discover user realm

authenticationazureazure-activedirectorysql server

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 the Microsoft 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.

Connecting with sqlcmd

-G

This switch is used by the client when connecting to SQL Database or Azure Synapse Analytics to specify that the user be authenticated using Azure Active Directory authentication. It can be combined with just the -P option to use access token authentication (v17.8+). This option sets the sqlcmd scripting variable SQLCMDUSEAAD = true. The -G switch requires at least sqlcmd version 17.6. To determine your version, execute sqlcmd -?.

-P

Specify a user password. When used with the -G option without -U, specifies a file that contains an access token (v17.8+). The token file should be in UTF-16LE (no BOM) format.

Access tokens can be obtained via various methods. It's important to ensure the access token is correct byte-for-byte, as it will be sent as-is. Below is an example command that obtains an access token. The command uses the Azure CLI and Linux commands and saves it to a file in the proper format. If your system or terminal's default encoding isn't ASCII or UTF-8, you may need to adjust the iconv options. Be sure to carefully secure the resulting file and delete it when it's no longer required.

Important

The -G option only applies to Azure SQL Database and Azure Synapse Analytics. AAD Interactive Authentication isn't currently supported on Linux or macOS. AAD Integrated Authentication requires Microsoft ODBC Driver 17 for SQL Server version 17.6.1 or higher and a properly configured Kerberos environment.

Command Example

az account get-access-token --resource https://database.windows.net --output tsv | cut -f 1 | tr -d '\n' | > iconv -f ascii -t UTF-16LE > /tmp/tokenFile

SoUrCe

Related Question