sqlcmd.exe fails to use supplied authentication token on Windows

Janne Kujanpää 181

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'"  
  
  
Column1  
-------  
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'"  
  
   
-  
x  
  
(1 rows affected)  
PS C:\Users\janne.kujanpaa\sql>

-------
This is a really weird missing feature-parity between

Cmdlet and sqlcmd.exe on windows
sqlcmd on linux/mac and sqlcmd.exe on Windows

Anurag Sharma 17,566 Reputation points

2022-02-08T06:06:50.863+00:00

Hi @Janne Kujanpää , welcome to Microsoft Q&A forum.

You are right in mentioning that the sqlcmd.exe does not work with AAD using tokens and same is mentioned in the below article. We need to use the user principal to connect which is what worked for you when you used -U. This is kind of a limitation.

Azure AD token
Alexandre 346 Reputation points

2022-02-09T14:06:05.767+00:00

You are right in mentioning that the sqlcmd.exe does not work with AAD using tokens and same is mentioned in the below article. We need to use the user principal to connect which is what worked for you when you used -U. This is kind of a limitation.

Anything planned to improve this? This is a problem because it means we cannot use sqlcmd in an azure pipeline with the built-in token of the pipeline, it means having to put a service principal secret in a pipeline which does not seem a great idea (even if we store it in secret variables or a keyvault.
Anurag Sharma 17,566 Reputation points

2022-02-10T05:31:58.017+00:00

Thanks for your response. I can pass this feedback to the product group and seek their inputs. However we can use PowerShell script and call on Invoke-Sqlcmd to achieve the desired result. Please let me know if my understanding is not right here and we can discuss more.
Janne Kujanpää 181 Reputation points

2022-02-10T06:40:36.303+00:00

Invoke-Sqlcmd does not support interactive mode.
Anurag Sharma 17,566 Reputation points

2022-02-10T07:02:53.797+00:00

Thanks for your response. I have reached out to product group on this asking for their suggestions.
Alexandre 346 Reputation points

2022-02-11T12:57:00.587+00:00

You're right, it's just that Microsoft documentation always refers to sqlcmd and I have not tried with Invoke-Sqlcmd.
Jan Hudec 1 Reputation point

2022-11-23T08:39:24.157+00:00

The documentation clearly says that it should work with tokens, and the error message suggests that it almost works, but there is a bug in parsing of the user principal name where the domain gets stripped and then the user is not found.