Read-/Write-SqlTableData not working with -AccessToken and managed-identity (Azure Function to work with Azure SQL DB)

[slarionoff]

Dear colleagues, seems that I have an issue, similar to this. The difference is in a setup (possibly) and an error message. I have an Azure PS1 function with Managed Identity, that tries to read data from my Azure SQL DB. User was created there, read access was given as well.

In a function I have an initialization section where some variables are set, also I have a Import-Module SqlServer statement there. Then I have two lines of code, representing two different commands but with almost exactly the same parameters (as far as it is possible):

# Some initialization
$Token = (Get-AzAccessToken -ResourceUrl "https://database.windows.net").Token
$SQLInstance = "<removed for safety>"
$SQLDatabase = "<removed for safety>"
$SQLSchema = "<removed for safety>"
$SQLTable = "<removed for safety>"
$SQLText = "SELECT TOP 1 * FROM $SQLSchema.$SQLTable"

# Two lines of code:
Invoke-Sqlcmd     -ServerInstance $SQLInstance -Database $SQLDatabase -AccessToken $Token -Query $SQLText | Format-Table

Read-SqlTableData -ServerInstance $SQLInstance -Database $SQLDatabase -AccessToken $Token -SchemaName $SQLSchema -TableName $SQLTable -TopN 1 | Format-Table

The first command runs just fine, the second one tells me:

[Error]   ERROR: Failed to connect to server <SQL Server Name>.database.windows.net.

Exception             : 
    Type           : Microsoft.SqlServer.Management.Common.ConnectionFailureException
    TargetSite     : 
        Name          : Connect
        DeclaringType : Microsoft.SqlServer.Management.Common.ConnectionManager
        MemberType    : Method
        Module        : Microsoft.SqlServer.ConnectionInfo.dll
    Message        : Failed to connect to server <SQL Server Name>.database.windows.net.
    InnerException : 
        Type               : Microsoft.Data.SqlClient.SqlException
        Errors             : 
            Source     : Core Microsoft SqlClient Data Provider
            Number     : 18456
            State      : 1
            Class      : 14
            Server     : <SQL Server Name>.database.windows.net
            Message    : Login failed for user '<token-identified principal>'.
            LineNumber : 1
        ClientConnectionId : 9d64f728-ab26-455e-93c3-95395ccec1f8
        Class              : 14
        LineNumber         : 1
        Number             : 18456
        Server             : <SQL Server Name>.database.windows.net
        State              : 1
        Source             : Core Microsoft SqlClient Data Provider
        ErrorCode          : -2146232060
        TargetSite         : 
            Name          : CheckPoolBlockingPeriod
            DeclaringType : Microsoft.Data.ProviderBase.DbConnectionPool, Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5
            MemberType    : Method
            Module        : Microsoft.Data.SqlClient.dll
        Message            : Login failed for user '<token-identified principal>'.
        Data               : System.Collections.ListDictionaryInternal
        HResult            : -2146232060
        StackTrace         : 
   at Microsoft.Data.ProviderBase.DbConnectionPool.CheckPoolBlockingPeriod(Exception e)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnectImpl()
   at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect()
   at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
    Source         : Microsoft.SqlServer.ConnectionInfo
    HResult        : -2146233087
    StackTrace     : 
   at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
   at Microsoft.SqlServer.Management.PowerShell.SmoCmdlet.ConnectToServerModern(String instance, PSCredential cred, Nullable`1 timeout)
   at Microsoft.SqlServer.Management.PowerShell.SmoCmdlet.ConnectToServer(String instance, PSCredential cred, Nullable`1 timeout)
   at Microsoft.SqlServer.Management.PowerShell.SmoContextSensitiveTargetedWithServerInstanceCmdlet`1.ResolveTargets()+MoveNext()
TargetObject          : <SQL Server Name>.database.windows.net
CategoryInfo          : ObjectNotFound: (<SQL Server Name>.database.windows.net:String) [Read-SqlTableData], ConnectionFailureException
FullyQualifiedErrorId : ConnectionToServerFailed,Microsoft.SqlServer.Management.PowerShell.ReadSqlTableData
InvocationInfo        : 
    MyCommand        : Read-SqlTableData
    ScriptLineNumber : 50
    OffsetInLine     : 1
    HistoryId        : 1
    ScriptName       : C:\...\run.ps1
    Line             : Read-SqlTableData -ServerInstance $SQLInstance -Database $SQLDatabase -AccessToken $Token -SchemaName $SQLSchema -TableName $SQLTable -TopN 1 | Format-Table

    PositionMessage  : At C:\...\run.ps1:50 char:1
                       + Read-SqlTableData -ServerInstance $SQLInstance -Database $SQLDatabase …
                       + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    PSScriptRoot     : C:\...
    PSCommandPath    : C:\...\run.ps1
    InvocationName   : Read-SqlTableData
    CommandOrigin    : Internal
ScriptStackTrace      : at <ScriptBlock>, C:\...\run.ps1: line 50
PipelineIterationInfo : 

$PSVersionTable

Name                           Value
----                           -----
PSVersion                      7.2.13
PSEdition                      Core
GitCommitId                    7.2.13
OS                             Microsoft Windows 10.0.14393
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

(Get-Module SQLServer).Version | Format-Table

Major  Minor  Build  Revision
-----  -----  -----  --------
22     1      1      -1

Also in my case if I don't provide .Token after a Get-AzAccessToken command, the 1st line doesn't work either but with another errors. I can add them later if needed.

[slarionoff]

Hi @Matteo-T ! Any news on this issue? Thank you!

[Matteo-T]

I may have finally figure out what was wrong.

...
Read-SqlTableData -ServerInstance "$($ServerName).database.windows.net" -Database $databaseName -AccessToken $access_token -SchemaName SalesLT -TableName Customer -TopN 1 | Format-Table

is now spitting out this, which I like A LOT:

CustomerID NameStyle Title FirstName MiddleName LastName Suffix CompanyName  SalesPerson             EmailAddress
---------- --------- ----- --------- ---------- -------- ------ -----------  -----------             ------------
         1     False Mr.   Orlando   N.         Gee             A Bike Store adventure-works\pamela0 orlando0@adventur…

Now I need to wire up the whole thing, run some more tests and... push it out!

[Matteo-T]

The fix for this issue should roll out in v22.3.

[Matteo-T]

Fixed in v22.3.0

[slarionoff]

Great, will try to test an report. Thank you!