Connect-DbaInstance / Invoke-DbaQuery - Open a correct new connection when ConnectAsUserName is used and fix usage of AppendConnectionString

[andreasjordan]

@niphlod - please have a look at the changes. If you like them you can include them in your PR or we can use this PR.

Type of Change

• [x] Bug fix (non-breaking change, fixes #9665 )
• [ ] New feature (non-breaking change, adds functionality, fixes # )
• [ ] Breaking change (affects multiple commands or functionality, fixes # )
• [ ] Ran manual Pester test and has passed (Invoke-ManualPester)
• [ ] Adding code coverage to existing functionality
• [ ] Pester test is included
• [ ] If new file reference added for test, has is been added to github.com/dataplat/appveyor-lab ?
• [ ] Unit test is included
• [ ] Documentation
• [ ] Build system

Purpose

This PR addresses two topics:

• Connections that use a windows credential as SqlCredential must not lose this information when they are processed by Connect-DbaInstance.
• It should be able to pass additional connection information to Invoke-DbaQuery via AppendConnectionString.

Approach

While processing a Server SMO inside of Connect-DbaInstance, we identify the usage of windows credentials in case we need to change the connection context because of the given parameters. We then force a completly new connection instead of changing the current connection. We also identify the usage of windows credentials inside of Invoke-DbaQuery while we test if we can reuse an existing connection or if we have to use Connect-DbaInstance to create a new connection or modify the existing connection.

Commands to test

Screenshots

Learning

[andreasjordan]

Tests show that there are new problems with changing database context. I will try to look at that tomorrow.

[andreasjordan]

Ok, @niphlod - now you can have a look.

I basically moved your code to Connect-DbaInstance to generally support this case and just changed Invoke-DbaQuery a bit so that AppendConnectionString is forcing a new connection.

[andreasjordan]

Ups, just forgot the case when AppendConnectionString is used with a simple open connection. Will add this soon...

[niphlod]

sorry @andreasjordan , I'm a bit lost in here. Can I use this PR in lieu of #9667 to see if everything works ?

[andreasjordan]

Yes. We can just merge this PR here to solve the issue. It's mostly your code - just a bit rearranged.

[niphlod]

ok, lemme try a few combinations then, I'll close mine and approve this as soon as it works as expected.

[niphlod]

@andreasjordan , approved!

[andreasjordan]

Just added Purpose and Approach to the initial post.

@potatoqualitee if you need anything else before merging, please let me know.

[andreasjordan]

@potatoqualitee / @jpomfret

What can I do to get this merged?

[jpomfret]

Thanks @andreasjordan and @niphlod - sorry for the delay. Looks like @niphlod has tested and approved so will get this merged into dev!