Az SQL DB connections destroyed by redundant Connect-DbaInstance in multiple functions

[PowerDBAKlaas]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

$conn = Connect-DbaInstance ... with a token to an Az SQL DB succeeds.
Then I try to pipe this connection to a function, e.g. Get-DbaPermission and get an error " login failed for user ' ' ."

In the 'process' block of the Get-DbaPermission function there's again Connect-DbaInstance, using a syntax I think only works on SQL Server.

Do you also think this is the problem?
And do you think we could solve this by

• checking if there already is a connection to this instance
• presenting different syntax depending on the target
• removing the Connect-DbaInstance in the Get-DbaPermission
• ...

Presumably this same issue exists in multiple functions, so a general solution would be preferred.
(Get-DbaDbUser returns a different error)

Steps to Reproduce

$token = 
$conn = Connect-DbaInstance -sqlinstance MyInstance - ... $token
Get-DbaPermission -sqlinstance $conn

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

2.1.28

Other details or mentions

Sorry, I am not permitted to copy text or screenshots.

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe)

PowerShell Host Version

7.5.0

SQL Server Edition and Build number

Az SQL DB

.NET Framework Version

4.8.04161

[niphlod]

Hi @PowerDBAKlaas , welcome "back". I'll try and see if we can come up with a general fix but IMHO it's not a problem of Connect-DbaInstance "per se" (meaning eventual problems CAN be fixed there) but rather the fact that the module was built thinking of sql instances where changing db context can be done freely without hiccups via SMO enumeration. Not sure SMO attached to a sql instance permits the same "level of freedom".
That being said let's start from something (Get-DbaPermission has less SMO than usual) or from something more "database scoped" like Get-DbaDb* and build from there.

[niphlod]

BTW, https://github.com/dataplat/dbatools/blob/7346448fefe8ee8ebb239aa09afaac41841b2869/public/Get-DbaPermission.ps1#L234C57-L234C66 is unsupported on Azure, so even if the connection was preserved, no useful results would be returned.

And, it does, in fact, error. Unsure why yours does "login failed for user"

$inst = Connect-DbaInstance -SqlInstance $server -AppendConnectionString 'Authentication=ActiveDirectoryInteractive'
PS C:> Get-DbaPermission -SqlInstance $inst -Verbose
VERBOSE: [23:17:21][Connect-DbaInstance] Starting loop for '<myservername>.database.windows.net': ComputerName = '<myservername>.database.windows.net', InstanceName = 'MSSQLSERVER', IsLocalHost = 'False', Type = 'Server'
VERBOSE: [23:17:21][Connect-DbaInstance] Azure detected
VERBOSE: [23:17:21][Connect-DbaInstance] Server object passed in, will do some checks and then return the original object
VERBOSE: [23:17:22][Get-DbaPermission] Processing [<mydbname1>] on <myservername>.database.windows.net.
WARNING: [23:17:22][Get-DbaPermission] Failure executing against <mydbname1> on <myservername>.database.windows.net | 'SUSER_SNAME' cannot be invoked with parameters in this version of SQL Server.
VERBOSE: [23:17:22][Get-DbaPermission] Processing [master] on <myservername>.database.windows.net.
WARNING: [23:17:23][Get-DbaPermission] Failure executing against master on <myservername>.database.windows.net | 'SUSER_SNAME' cannot be invoked with parameters in this version of SQL Server.
VERBOSE: [23:17:23][Get-DbaPermission] Processing [<mydbname2>] on <myservername>.database.windows.net.
WARNING: [23:17:24][Get-DbaPermission] Failure executing against <mydbname2> on <myservername>.database.windows.net | 'SUSER_SNAME' cannot be invoked with parameters in this version of SQL Server.

[PowerDBAKlaas]

Ciao Simone

The -AppendConnectionString does indeed make a difference: I get the SUSER_SNAME error now.

Maybe a bit more clarification about my logic:
I use Az CmdLets to get all subscriptions and foreach set-AzContext.
In each subscription I get AzSqlServer, AzSQLDatabase, AzSQLInstance and so on and collect the necessary properties about the Azure resources.
Still in each subscription I loop over all DB's to create a connection, using a token, and execute some sql queries to build an inventory

Of course I want to use dbatools where possible. So far, Invoke-DbaQuery works fine (as long as there are no warnings), and Get-DbaInstanceAudit too. No luck with other functions. I assume we can expand functions that use a sql query by Switch ( $_.SQLEngineType) {...} to choose between appropriate script versions?

[niphlod]

or plain old "thisIsNotSupportedOnAzure", but, alas, it's going to be a painstakingly one-by-one approach.