Read-/Write-SqlTableData not working with -AccessToken and managed-identity

[PatrickSpies]

We are using the new -AccessToken-parameter to connect to a database within Azure-Managed-Instance with a user-assigned managed-identity. For some cmdlets this is working as expected, but for others (e.g. Read-SqlTableData/Write-SqlTableData) the invokation leads to an error.

Using Invoke-Sqlcmd with -AccessToken returns the expected result:

Invoke-Sqlcmd -ServerInstance "$env:SQLInstance" -Database "$env:SQLDatabase" -AccessToken (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token -Query "SELECT TOP 1 * FROM sys.tables"

Instead using Read-SqlTableData with -AccessToken leads to an error:

Read-SqlTableData -ServerInstance "$env:SQLInstance" -Database "$env:SQLDatabase" -AccessToken (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token -SchemaName "sys" -TableName "tables" -TopN 1

Read-SqlTableData: Failed to connect to server <>.database.windows.net,1433.

Same error when using Write-SqlTableData

When passing the token from my personal azuread-user instead of using the managed-identity the invocation succeeds..

As both Invoke-Sqlcmd and using a personal access-token are working properly, we can not see any cause for Read-SqlTableData/Write-SqlTableData not working with -AccessToken and the managed-identity..

---

$PSVersionTable

Name                           Value
----                           -----
PSVersion                      7.3.3
PSEdition                      Core
GitCommitId                    7.3.3
OS                             Linux 5.4.0-1104-azure #110~18.04.1-Ubuntu SMP Sat Feb 11 17:41:21 UTC 2023
Platform                       Unix
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

(Get-Module SQLServer).Version

Major  Minor  Build  Revision
-----  -----  -----  --------
22     0      59     -1

Instance Name                        Version    ProductLevel UpdateLevel  HostPlatform HostDistribution
-------------                        -------    ------------ -----------  ------------ ----------------
<redacted>.database.windows.net,1433 16.0.175   RTM          CU2          Windows      Windows Server 2019 Datacenter

[potatoqualitee]

check it, @wsmelton! when MSFT figures this out, we can too 😅 I know you said it had to do with sqlbulkcopy so i think it'll have to be fixed in the sqlclient.

[PatrickSpies]

Still fails with v22.1.1

[peterbud]

Same here

[Matteo-T]

Sorry folks. I completely missed this issue.

I do not know on top of my head how to setup all that stuff and my MI is not configured for User Managed Identities.

If you have a script to set it up, it would help me debugging.

The cmdlets are not really doing much: they typically pass around the token to either SMO or the driver (Microsoft.Data.SqlClient). It is possible that the way the Read-Sql* cmdlet is passing the token to SMO is incorrect for some reason; Invoke-Sqlcmd passed the token straight to the driver... so that could be a difference.

What is the behavior if you don't pass the full token object -AccessToken (Get-AzAccessToken -ResourceUrl https://database.windows.net) instead of just the text blob -AccessToken (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token: same error?

Also, what is the real error? Can you just capture the $Error[0].Exception.ToString() and see if it reveals anything interesting?

[PatrickSpies]

Sorry for my mistake, it seems the issue had been fixed with v22.1.1 I´ve had an additional issue with multiple az-contexts within the running script.

Both of your variants (with and withoud .Token are working.

[Matteo-T]

Thanks for confirming!

[slarionoff]

... as a parent issue is marked as "closed", I shifted a description to a new issue