sqlcmd.exe fails to use supplied authentication token on Windows

Janne Kujanpää 111 Reputation points

Is usage of AAD access tokens supported on linux and mac only?

https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/connecting-with-sqlcmd?view=sql-server-ver15 mentions following:

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.

Windows documentation does not include similar note for -P and -G: https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15

How can I pass authentication token to sqlcmd on windows?

Passing access token to Invoke-Sqlcmd Cmdlet works fine on Windows but fails with sqlcmd.exe:

  PS C:\Users\janne.kujanpaa\sql> Invoke-Sqlcmd -ServerInstance dev-sqlserver.database.windows.net -Database master -AccessToken $access_token -Query "select 'x'"  
PS C:\Users\janne.kujanpaa\sql> $access_token | Set-Content -path at  
PS C:\Users\janne.kujanpaa\sql> SQLCMD.EXE -S dev-sqlserver.database.windows.net -d master -G -P at -Q "SELECT 'x'"    
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Failed to authenticate the user 'janne.kujanpaa' in Active Directory (Authentication option is 'ActiveDirectoryPassword').  
Error code 0xCAA90018; state 10  
Could not discover a user realm..  
PS C:\Users\janne.kujanpaa\sql> SQLCMD.EXE -S dev-sqlserver.database.windows.net -d master -G -U adm-janne.kujanpaa@xxxxx -Q "SELECT 'x'"  
(1 rows affected)  
PS C:\Users\janne.kujanpaa\sql>   

This is a really weird missing feature-parity between

  1. Cmdlet and sqlcmd.exe on windows
  2. sqlcmd on linux/mac and sqlcmd.exe on Windows
Azure SQL Database
{count} votes