Invoke-DbaQuery throws error "cannot be changed or read after a connection string has been set"

[dsolodow]

Before submitting a bug report:

• Ensure you are able to reproduce it on the latest released version (we release often)
• Verified this bug is not already reported in an issue
• Verified errors are not related to permissions
• Can reproduce in a clean PowerShell session (clean = powershell -NoProfile)

Steps to Reproduce

Invoke-DbaQuery -SqlInstance "SQLSERVER" -Query "select top 10 * from dbo.[x_table_name]" -database DATABASE -SqlCredential $bleh

Expected Behavior

10 rows returned

Actual Behavior

WARNING: [12:23:25][Invoke-DbaQuery] Failure | Property LoginSecure cannot be changed or read after a connection string has been set.
writeErrorStream      : True
PSMessageDetails      :
Exception             : System.Exception: Property LoginSecure cannot be changed or read after a connection string has been set.

writeErrorStream      : True
PSMessageDetails      :
Exception             : System.Exception: Property LoginSecure cannot be changed or read after a connection string has been set. ---> System.Exception: Property LoginSecure cannot be changed
                        or read after a connection string has been set. ---> System.Management.Automation.SetValueInvocationException: Exception setting "LoginSecure": "Property LoginSecure
                        cannot be changed or read after a connection string has been set." ---> Microsoft.SqlServer.Management.Common.PropertyNotAvailableException: Property LoginSecure
                        cannot be changed or read after a connection string has been set.
                           at Microsoft.SqlServer.Management.Common.ConnectionSettings.ThrowIfConnectionStringIsSet(String propertyName)
                           at Microsoft.SqlServer.Management.Common.ConnectionSettings.set_LoginSecure(Boolean value)
                           at CallSite.Target(Closure , CallSite , Object , Boolean )
                           --- End of inner exception stack trace ---
                           at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
                           at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
                           at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
                           at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
                           --- End of inner exception stack trace ---
                           --- End of inner exception stack trace ---
TargetObject          : SQLSERVER
CategoryInfo          : NotSpecified: (SQLSERVER:PSObject) [Write-Error], Exception
FullyQualifiedErrorId : dbatools_Invoke-DbaQuery,Stop-Function
ErrorDetails          : Property LoginSecure cannot be changed or read after a connection string has been set.
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : at Stop-Function, <No file>: line 104432
                        at Invoke-DbaQuery<Process>, <No file>: line 63268
                        at <ScriptBlock>, <No file>: line 1
PipelineIterationInfo : {0, 1}

Environmental data

PowerShell:

**Machine One**

|Name|Value|
----         |                  -----
PSVersion      |                5.1.17763.134
PSEdition                |      Desktop
PSCompatibleVersions      |     {1.0, 2.0, 3.0, 4.0...}
BuildVersion              |     10.0.17763.134
CLRVersion                |     4.0.30319.42000
WSManStackVersion           |   3.0
PSRemotingProtocolVersion    |  2.3
SerializationVersion       |   1.1.0.1

Machine Two

Name              |             Value|
----           |                -----
PSVersion   |                   6.1.1
PSEdition       |               Core
GitCommitId     |               6.1.1
OS                |             Linux 4.4.0-17763-Microsoft #253-Microsoft Mon Dec 31 17:49:00 PST 2018
Platform       |                Unix
PSCompatibleVersions     |      {1.0, 2.0, 3.0, 4.0...}
PSRemotingProtocolVersion   |   2.3
SerializationVersion        |   1.1.0.1
WSManStackVersion       |       3.0

**SQL Server**: Microsoft SQL Server 2014 (SP3-CU1) (KB4470220) - 12.0.6205.1 (X64)   Nov 30 2018 02:59:03   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor) 

This happens on 0.9.740 on both Windows PowerShell and PowerShell Core.

[potatoqualitee]

Can confirm, thank you. only impacts credential.

[nvarscar]

I see why the error is happening, but I cannot repro on my end just yet.
Setting connection string in Connect-DbaInstance on line 368
https://github.com/sqlcollaborative/dbatools/blob/a31bb64188af7e4bbdfdc6b32820ef2f36915c80/functions/Connect-DbaInstance.ps1#L368
is indeed happening prior to setting LoginSecure on lines 389/395:
https://github.com/sqlcollaborative/dbatools/blob/a31bb64188af7e4bbdfdc6b32820ef2f36915c80/functions/Connect-DbaInstance.ps1#L389

This error is most likely happens because Invoke-DbaQuery now passes -ApplicationIntent parameter every time and connection string gets adjusted on line 364:
https://github.com/sqlcollaborative/dbatools/blob/a31bb64188af7e4bbdfdc6b32820ef2f36915c80/functions/Connect-DbaInstance.ps1#L364
which results in explicitly setting the connection string on line 368 every time:
https://github.com/sqlcollaborative/dbatools/blob/a31bb64188af7e4bbdfdc6b32820ef2f36915c80/functions/Connect-DbaInstance.ps1#L368

It was not happening before since ApplicationIntent was added to Invoke-DbaQuery quite recently.
Then, again, I can't repro on my end.

[nvarscar]

Okay, updated my repo to recent version, can 100% repro now on both windows and sql auth.

[nvarscar]

@potatoqualitee PR is ready, was able to fix it without too much fuss, but there are still some concerns about other two parameters that might not work properly with SqlCredentials

[potatoqualitee]

fantastic thank you so much i'll reopen this until the other two params are checked

[potatoqualitee]

this should be fixed 💯

[ajaydwivedi-angelone]

I am facing this error with Write-DbaDbTableData function.

PS C:\Windows\system32> $Distributor

ComputerName Name          Product              Version   HostPlatform IsAzure IsClustered ConnectedAs
------------ ----          -------              -------   ------------ ------- ----------- -----------
MyDistributorServer 192.168.1.228 Microsoft SQL Server 13.0.5026 Windows      False   False       DBMonitor  



PS C:\Windows\system32> $tokenInserted | Write-DbaDbTableData -SqlInstance $Distributor -Database $DbaDatabase -Table $ReplTokenTableName -EnableException

Property NonPooledConnection cannot be changed or read after a connection string has been set.
At C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.84\allcommands.ps1:94074 char:9
+         throw $records[0]
+         ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ConnectionError: (MyDistributorServer :String) [], Exception
    + FullyQualifiedErrorId : dbatools_Write-DbaDbTableData
 

PS C:\Windows\system32> Get-Module dbatools

ModuleType Version    Name                                ExportedCommands                                                                                                           
---------- -------    ----                                ----------------                                                                                                           
Script     1.1.84     dbatools                            {Select-DbaObject, Set-DbatoolsConfig, Add-DbaAgDatabase, Add-DbaAgListener...}                                            

[potatoqualitee]

Thank you for the report @ajaydwivedi-angelone. What settings did you set for the original $Distributorobject?

@andreasjordan would you be able to take a look?

[andreasjordan]

I'll take a look.

[andreasjordan]

I can not repro the problem at the moment. @ajaydwivedi-angelone can you please run the command with -Verbose?

[ajaydwivedi-angelone]

Want to highlight fact that my scenario involves cross domain servers. The remove SQL Server can only be connected using IP not name as name does not resolve.

Below is output with verbose & debug -

PS C:\Windows\system32> $tokenInserted | Write-DbaDbTableData -SqlInstance $Distributor -Database $DbaDatabase -Table $ReplTokenTableName -EnableException -Verbose -Debug
DEBUG: 2731 | [09:56:36][Connect-DbaInstance] Starting process block
DEBUG: 2742 | [09:56:36][Connect-DbaInstance] Immediately checking for Azure
DEBUG: 2635 | [09:56:36][Test-Azure] Test for Azure is negative
DEBUG: 2749 | [09:56:36][Connect-DbaInstance] Starting loop for 'MyDistributorServer': ComputerName = 'MyDistributorServer', InstanceName = 'MSSQLSERVER', IsLocalHost = 'False', Type = 'Server'
VERBOSE: [09:56:36][Connect-DbaInstance] Server object passed in, will do some checks and then return the original object
DEBUG: 2754 | [09:56:36][Connect-DbaInstance] Server object passed in, will do some checks and then return the original object
VERBOSE: [09:56:36][Connect-DbaInstance] Database provided. Does not match ConnectionContext.CurrentDatabase, copying ConnectionContext and setting the CurrentDatabase
DEBUG: 2808 | [09:56:36][Connect-DbaInstance] Database provided. Does not match ConnectionContext.CurrentDatabase, copying ConnectionContext and setting the CurrentDatabase
VERBOSE: [09:56:36][Connect-DbaInstance] NonPooledConnection provided. Does not match ConnectionContext.NonPooledConnection, copying ConnectionContext and setting NonPooledConnection
DEBUG: 2816 | [09:56:36][Connect-DbaInstance] NonPooledConnection provided. Does not match ConnectionContext.NonPooledConnection, copying ConnectionContext and setting NonPooledConnection
DEBUG: [09:56:36][Write-DbaDbTableData] Error occurred while establishing connection to MyDistributorServer | Property NonPooledConnection cannot be changed or read after a connection string has been set.
Property NonPooledConnection cannot be changed or read after a connection string has been set.
At C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.84\allcommands.ps1:94074 char:9
+         throw $records[0]
+         ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ConnectionError: (MyDistributorServer:String) [], Exception
    + FullyQualifiedErrorId : dbatools_Write-DbaDbTableData


PS C:\Windows\system32> 

[andreasjordan]

Thanks for the details. To dig deeper we need to introduce a new step to first change the connection context and test the resulting connection before using Write-DbaDbTableData.

So please run this:

$newSMO = Connect-DbaInstance -SqlInstance $Distributor -Database $DbaDatabase -NonPooledConnection -Verbose
$newSMO.Query("SELECT 1")

If this works, you could try to use this in the next command:

$tokenInserted | Write-DbaDbTableData -SqlInstance $newSMO -Database $DbaDatabase -Table $ReplTokenTableName -EnableException -Verbose

You could also try to use -Database $DbaDatabase -NonPooledConnection while creating $Distributor, so that the connection needs no changes when using Write-DbaDbTableData.

[ajaydwivedi-angelone]

Executed

$newSMO = Connect-DbaInstance -SqlInstance $Distributor -Database $DbaDatabase -NonPooledConnection -Verbose
$newSMO.Query("SELECT 1")

Below is output -

PS C:\Windows\system32> $newSMO = Connect-DbaInstance -SqlInstance $Distributor -Database $DbaDatabase -NonPooledConnection -Verbose
$newSMO.Query("SELECT 1")
VERBOSE: [16:48:04][Connect-DbaInstance] Server object passed in, will do some checks and then return the original object
VERBOSE: [16:48:04][Connect-DbaInstance] Database provided. Does not match ConnectionContext.CurrentDatabase, copying ConnectionContext and setting the CurrentDatabase
VERBOSE: [16:48:04][Connect-DbaInstance] NonPooledConnection provided. Does not match ConnectionContext.NonPooledConnection, copying ConnectionContext and setting NonPooledConnection
Connect-DbaInstance : Exception setting "NonPooledConnection": "Property NonPooledConnection cannot be changed or read after a connection string has been set."
At line:1 char:11
+ $newSMO = Connect-DbaInstance -SqlInstance $Distributor -Database $Db ...
+           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Connect-DbaInstance], SetValueInvocationException
    + FullyQualifiedErrorId : ExceptionWhenSetting,Connect-DbaInstance

I removed & recreated variable $Distributor like below -

Remove-Variable -Name Distributor
$Distributor = $DistributorConfig | Connect-DbaInstance -Database $DbaDatabase -NonPooledConnection
$tokenInserted | Write-DbaDbTableData -SqlInstance $Distributor -Database $DbaDatabase -Table $ReplTokenTableName -EnableException -Verbose

Getting below error -

VERBOSE: [16:56:50][Connect-DbaInstance] Server object passed in, will do some checks and then return the original object
VERBOSE: [16:56:50][Connect-DbaInstance] Database provided. Does not match ConnectionContext.CurrentDatabase, copying ConnectionContext and setting the CurrentDatabase
VERBOSE: [16:56:50][Connect-DbaInstance] NonPooledConnection provided. Does not match ConnectionContext.NonPooledConnection, copying ConnectionContext and setting NonPooledConnection
Property NonPooledConnection cannot be changed or read after a connection string has been set.
At C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.84\allcommands.ps1:94074 char:9
+         throw $records[0]
+         ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ConnectionError: (ABPLMWNODE01:String) [], Exception
    + FullyQualifiedErrorId : dbatools_Write-DbaDbTableData

[andreasjordan]

The last one is interesting: It looks like $DistributorConfig is already a connected SMO. Can you tell me more about $DistributorConfig? The parameter SqlInstance has to be a string like the IP address.

[ajaydwivedi-angelone]

I have added all Servers with IP & @@serverName values as DisplayName for Registered Server Groups using SQL Credentials.

So I scan $DistributorIP value across Registered Servers and create Server Object using Connect-DbaInstance.

$DistributorConfig = Get-DbaRegisteredServer -Group All | ? {$_.ServerName -eq $DistributorIP} | Select-Object -First 1;
$Distributor = $DistributorConfig | Connect-DbaInstance

[andreasjordan]

I would like to eleminate the use of registered servers. Can you connect to the instance without using Get-DbaRegisteredServer?

[ajaydwivedi-angelone]

Invoke-DbaQuery is working fine. Not sure why not Write-DbaDbTableData.

Sure. I'll utilize PSCredential with SqlCredential parameter to accomplish same. Thank you so much for helping me out here.

[andreasjordan]

Invoke-DbaQuery may not force a change of the connection context. Write-DbaDbTableData forces a non pooled connection and sets a database context. And that is not working properly. In any case we can only try to find a workaround as the issue is probably related on how the SMO is handling the connections. We can't do anything about that.

So I would like to see if you can open a non pooled connection to the target database with connect-dbainstance and then use that connection with Write-DbaDbTableData.

[andreasjordan]

I am closing this issue now. If any of the problems discussed here are reproducible with the current version of dbatools, please open a new issue.