Connect-DbaInstance - Report whether a new connection was opened

[andreasjordan]

First step of #10554. Adds the ownership signal and uses it in Invoke-DbaQuery; the other seven sites follow in their own pull requests.

What was wrong

A command that connects and cleans up afterwards had no way to tell whether Connect-DbaInstance opened a connection for it or handed back the object the caller passed in. Invoke-DbaQuery inferred it from its own parameters:

if ($connDbaInstanceParams.NonPooledConnection -and -not $startedWithAnOpenConnection) {
    $null = $server | Disconnect-DbaInstance -Verbose:$false
}

Connect-DbaInstance only copies the connection context when something about it actually has to change, so it frequently returns the very object that was passed in - and then this closed the caller's connection. With -NonPooledConnection that takes the session with it:

$server = Connect-DbaInstance -SqlInstance $instance -NonPooledConnection
$null = $server.ConnectionContext.ExecuteNonQuery("CREATE TABLE #t (id int)")
$null = Invoke-DbaQuery -SqlInstance $server -Database master -Query "SELECT 1"
$server.ConnectionContext.ExecuteScalar("SELECT COUNT(*) FROM #t")
# Invalid object name '#t'.

What this changes

Connect-DbaInstance gets -IsNewConnectionReference, which reports whether it opened a new connection, and Invoke-DbaQuery only disconnects when it did.

The value is already known internally as $isNewConnection: $false for Server and SqlConnection inputs, $true for string, connection string and RegisteredServer inputs, and $true when a connection context has to be copied. Only its exposure is new; no connection logic changed. Both places that emit a server set it, including the -SqlConnectionOnly path.

Why a reference and not a variable name

The plan in the issue proposed -IsNewConnectionVariable written through $PSCmdlet.SessionState.PSVariable.Set(). That works for a caller outside of dbatools, but not for a caller inside it, which is exactly the case that matters here. Both commands share the module session state, so the value lands in the local scope of Connect-DbaInstance instead of the scope of the calling command:

external caller, name : set via SessionState
in-module caller, name : unset
in-module caller, ref  : set via ref

Set-Variable -Scope 1 does not help either - from a module function the parent scope is the module scope, not the caller. A [ref] behaves the same way in both cases, so that is what the parameter takes.

The first test run caught this: with the variable-name version, Invoke-DbaQuery never learned that it owned its connections and the new session-count test failed with five leaked sessions. That test is in this pull request.

Tests

• Connect-DbaInstance: the parameter list, plus four assertions that the reference is $true for a string, $false when a server object is passed back in, $true when the context has to be copied, and that the same object comes back when nothing has to change
• Invoke-DbaQuery: the caller's connection stays open and its temp table survives, and the connections the command opens itself are still closed, so [Bug] Invoke-DbaQuery doesn't close connections which it creates #6210 does not come back

Both files pass against SQL Server 2025 and 2022: Connect-DbaInstance 32 passed, 1 skipped (Azure), Invoke-DbaQuery 29 passed.

---

This text was created by Claude and reviewed by Andreas Jordan.

[andreasjordan]

One unrelated test failed:

Running C:\github\dbatools\tests\Install-DbaFirstResponderKit.Tests.ps1 ...[gha] test started: Install-DbaFirstResponderKit.Tests.ps1
WARNING: [06:05:26][Install-DbaFirstResponderKit] Certificate signing failed for dbatoolsci_frk_819873629 on dbatoolsTDZLC4\sql2022. | Cannot find the object 'sp_BlitzFirst', because it does not exist or you do not have permission.

Will try to analyze later.

[andreasjordan]

Superseded by #10564, which combines this with #10563. On its own this change breaks Install-DbaFirstResponderKit: it removes a disconnect that was accidentally repairing a database context leak, and #10563 is what makes the repair unnecessary. Details in #10564.