connection or connection info from Connect-DbaInstance not reused by Invoke-DbaQuery using Windows credential as SqlCredential

[MennoAgema]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

When using the object obtained by Connect-DbaInstance with a domain user credential for running queries against that database, invoke-dbaquery creates a new connection your own logged in user credential instead of the credential provided to connect dba instances. Even if you supply invoke-dbaquery with the same credential as you provided to connect-dbainstance. Using query method from the dbainstance object uses the correct credential.

I would expect to have invoke-dbaquery to use the same credential as connect-dbainstance.

Steps to Reproduce

# Name credentials
$username = 'DOMAIN\USER'
$password = 'password' | ConvertTo-SecureString -AsPlainText -Force
$credential = [PSCredential]::New($username,$password) 
$connectionParams = @{
    'SqlInstance' = "servername.domain.local"
    'Database' = "databasename"
    'DisableException' = $true
    'SqlCredential' =$credential
    'clientname' = "TestAPP$((Get-Date).ToString())" # $UAJob.ScriptFullPath
  }
  
$dbaInstance = Connect-DbaInstance @connectionParams -Verbose

$query = "SELECT 
    session_id,
    login_name,
    DatabaseName = DB_NAME(),
    program_name
FROM sys.dm_exec_sessions S
WHERE session_id = @@SPID;"

# running the query in multiple ways to show the differences
invoke-DbaQuery -SqlInstance $dbaInstance -Query $query
invoke-DbaQuery -SqlInstance $dbaInstance -SqlCredential $credential -Query $query
invoke-DbaQuery -SqlInstance "servername" -SqlCredential $credential -Query $query
invoke-DbaQuery -SqlInstance "servername.domain.local" -SqlCredential $credential -Query $query
$dbaInstance.Query($query)

the script returns a confirmation that connect-dbainstance is using the local AD
VERBOSE: [10:54:47][Connect-DbaInstance] authentication method is 'local ad'

And that invoke-dbaquery uses my own logged in windows user to make a connection if I use the dbainstance object to provide the connection parameters.
id login_name DatabaseName program_name

---

    74 DOMAIN\my_logged_in_user DatabaseName TestAPP25-1-2025 10:54:47
    74 DOMAIN\my_logged_in_user DatabaseName TestAPP25-1-2025 10:54:47
    78 DOMAIN\USER              DatabaseName dbatools PowerShell module - dbatools.io
    78 DOMAIN\USER              DatabaseName dbatools PowerShell module - dbatools.io
    69 DOMAIN\USER              DatabaseName TestAPP25-1-2025 10:54:47

Please confirm that you are running the most recent version of dbatools

2.1.28

Other details or mentions

using a sql server login as a credential works as expected

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe), Windows PowerShell ISE (powershell_ise.exe), VS Code (integrated terminal)

PowerShell Host Version

Name Value

---

PSVersion 7.4.6
PSEdition Core
GitCommitId 7.4.6
OS Microsoft Windows 10.0.20348
Platform Win32NT
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
WSManStackVersion 3.0

Name Value

---

PSVersion 5.1.20348.2849
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.20348.2849
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

Microsoft SQL Server 2019 (RTM-CU29-GDR) (KB5046860) - 15.0.4410.1 (X64)
Oct 23 2024 15:50:49
Copyright (C) 2019 Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor)

.NET Framework Version

.NET 8.0.10