Can't get AccessToken to persist, but only sometimes about sqltoolsservice HOT 9 CLOSED

@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?

from sqltoolsservice.

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).

from sqltoolsservice.

@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

from sqltoolsservice.

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).

from sqltoolsservice.

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 ''.

from sqltoolsservice.

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.

from sqltoolsservice.

Thanks shuey! I'll take a look.

from sqltoolsservice.

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

from sqltoolsservice.

Got it using SqlAuthenticationProvider as well 💯 incredibly fast!

from sqltoolsservice.