Can't get AccessToken to persist, but only sometimes

[potatoqualitee]

I successfully added AccessToken support to dbatools but it seems to only work partially. Here's some code for a repro:

$params = @{
    Type            = 'ManagedIdentity'
    Subtype         = 'AzureSqlDb'
    EnableException = $true
}
$token = New-DbaAzAccessToken @params

$params = @{
    SqlInstance = 'psdbatools.database.windows.net'
    Database    = 'abc'
    AccessToken = $token
}

# This will connect successfully. You can even do $azSqlCn.Databases.Name and get the db names
$azSqlCn = Connect-DbaInstance @params

# This will break
Get-DbatoolsLog | Select -First 1 | Write-DbaDataTable -SqlInstance $azSqlCn -Database abc -Table ThisisErrorLog -AutoCreateTable

The last command should write to the $azSqlCn instance. But instead I get System.Data.SqlClient.SqlException: Login failed for user ''. This unanswered question had similar things and I wondered if it was because it changed the db context or something? Either way, I tried for like a week and could not get it to work - kept getting that Login failed for user ''.

Any advice would be appreciated.

[kburtram]

@potatoqualitee I'm not sure this question is related to this code repo, at least I can't immediately see the direct relationship.

@Matteo-T @shueybubbles do you have more context on this question?

[Matteo-T]

Yeah - unrelated to this repo. Could @potatoqualitee (aka Chrissy) elaborate on the "sometimes"? And, more importantly, on some SMO code that is being executed behind the scenes? Probably easier to ping me and @shueybubbles directly (it seems a SMO thingie).

[potatoqualitee]

@kburtram - my apologies, I saw some SMO related questions and thought this would be the place.

@Matteo-T Yes, I can elaborate on sometimes.

This will succeed 100% of the time:

$azSqlCn = Connect-DbaInstance @params
$azSqlCn.Databases.Name

This will fail 100% of the time with the blank login failure:

Get-DbatoolsLog | Select -First 1 | Write-DbaDataTable -SqlInstance $azSqlCn -Database abc -Table ThisisErrorLog -AutoCreateTable

In the future, shall I just ping you guys from the dbatools repo? Thanks, Chrissy

[Matteo-T]

Not sure how effective that is going to be: I get way too much traffic from GitHub and it is easy for me to miss communications. The important thing, IMHO, is that if this is a SMO issue (under the hood), you should give me/David/etc something that is SMO code ore than asking us to dig into the implementation of the DbaTools module (it does not have to be C# code, PowerShell is fine. But make it look like a few lines of .NET/SMO code).

[potatoqualitee]

Thanks

$accesstoken = "bigolaccesstoken"
$databasename = "abc"
$connstring = 'Data Source=TCP:psdbatools.database.windows.net,1433;Initial Catalog=abc;Persist Security Info=True;MultipleActiveResultSets=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False;Application Name="dbatools PowerShell module - dbatools.io"'

$sqlconn = New-Object System.Data.SqlClient.SqlConnection $connstring
$sqlconn.AccessToken = $AccessToken
$serverconn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection $sqlconn
$null = $serverconn.Connect()
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $serverconn

# works
$server.Databases.Name

# also works
$db = $server.Databases[$databaseName]
$db.Name

# breaks
$db.Tables.Name

# also breaks
$db.Tables.Refresh()

breaks with:

The following exception occurred while trying to enumerate the collection: "Failed to connect to server TCP:psdbatools.database.windows.net,1433.".

and more detailed

PSMessageDetails      :
Exception             : System.Management.Automation.ExtendedTypeSystemException: The following exception occurred while trying to enumerate the collection: "Failed to
                        connect to server TCP:psdbatools.database.windows.net,1433.". ---> Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to
                        connect to server TCP:psdbatools.database.windows.net,1433. ---> System.Data.SqlClient.SqlException: Login failed for user ''.

[shueybubbles]

If you can use .Net 4.7.2, I recommend using 'Authentication=Active Directory Interactive' in the connection string, and install a SqlAuthenticationProvider implementation to provide the token. Look up SqlAuthenticationProvider.SetProvider.

Otherwise, the only way the ServerConnection object can assign an access token to SqlConnection objects it creates is if you pass in an IRenewableToken implementation to its constructor. SMO sometimes creates new connections behind your back, which leads to these errors if you don't use either Interactive auth or IRenewableToken.

[potatoqualitee]

Thanks shuey! I'll take a look.

[potatoqualitee]

can confirm this appraoch works! (from azurevm, still trying to get it using a SqlAuthenticationProvider implementation

[potatoqualitee]

Got it using SqlAuthenticationProvider as well 💯 incredibly fast!