Connect-DbaInstance - More information about the new code path

[andreasjordan]

A few months ago, a new code path was introduced in this central command, which has now (with the release of version 1.0.168) matured to the point where it will soon become the standard.
Therefore, I would like to discuss here the differences and especially the new possibilities that this code path brings.

To start with: For probably 95 percent of the users or use cases, there will be no visible differences. But especially behind the scenes, the code is better structured, which will help us in troubleshooting in the future.

Before we go into the details: How to use the new code path?
Add this line to you script:

Set-DbatoolsConfig -FullName sql.connection.experimental -Value $true

Or run this line once on your machine:

Set-DbatoolsConfig -FullName sql.connection.experimental -Value $true -PassThru | Register-DbatoolsConfig

Part 1: Authentication

Here we talk about the parameters SqlInstance, SqlCredential, AzureDomain, AuthenticationType, Tenant, Thumbprint, Store and AccessToken.
Let's start with what is no longer supported:
AuthenticationType of 'AD Universal with MFA Support': It uses Tenant, Thumbprint and Store to try to do the same magic as the Azure PowerShell module does to get an access token. To be honest: I can't get this to work. If you use it, please get in contact so that we have a chance to implement it again in the new code path.
So in the new code path, AuthenticationType has to be the default of 'Auto' and I will tell you shortly how that works. The parameters Tenant, Thumbprint and Store are not used anymore and will probably be removed as soon as the new code path will be the only code path inside of Connect-DbaInstance.

Let me explain the magic behind AuthenticationType 'Auto':
First of all we take the computer name from SqlInstance and compare it to the value of AzureDomain, which has a default of 'database.windows.net'. That tells us if we are going to connect to Azure or to a local computer.
Then we take a look at SqlCredential and test if it is set or not. If it is not set, we use integrated security (either locally or in Azure). If it is set, we search for "@" or "\" which would indicate a domain account (so either a local domain - including a local computer account - or an account in an Azure Active Directory). If it is just a simple name, we have a classical SQL Login (either locally or in Azure).
Last (and brand new) we have a parameter called AccessToken, that takes an Azure Access Token returned from (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token if you are connected to your Azure subscription (which you can do with Connect-AzAccount). Yes, you need the Azure PowerShell module installed on your machine. And please note that this is an access token only, so it is valid for exactly one hour. If you would like to improve this, feel free to provide the necessary code.
So we use the various parameters do decide which of the following situations we have:

• local integrated
• local ad
• local sql
• azure integrated
• azure ad
• azure sql
• azure token

You want to know what we have to set for each of these cases? Search the source code for these terms as they are named exactly this way in the code. And we are open source, here is the link: https://github.com/sqlcollaborative/dbatools/blob/master/functions/Connect-DbaInstance.ps1

One last note: This only applies if you pass in a string to the parameter SqlInstance with a computer name and optionally instance name and port. The parameter SqlInstance also accepts objects of type Server (that is the output type of Connect-DbaInstance), SqlConnection (that could be the output type of Connect-DbaInstance when used with the parameter SqlConnectionOnly, but you can also build one on your own), a string that is a complete connection string (including the authentication information) or a RegisteredServer (as returned from Get-DbaRegServer). In all of these cases, the parameters related to authentication are silently ignored.

Part 2: Reusing the output of Connect-DbaInstance

Most users will run commands like $logins = Get-DbaLogin -SqlInstance SRV1\SQL2017, and this is totally ok. If you want to get databases and logins from an instance where you have to use a SQL Login to be sysadmin you would use:

$instance = 'SRV1\SQL2017'
$sysadminCred = Get-Credential -Message 'sysadmin' -UserName 'myAdmin'
$databases = Get-DbaDatabase -SqlInstance $instance -SqlCredential $sysadminCred
$logins = Get-DbaLogin -SqlInstance $instance -SqlCredential $sysadminCred

In this case, both Get-DbaDatabase and Get-DbaLogin would internally run Connect-DbaInstance with the SqlCredential parameter to connect to the instance. Because the used SMO (SMO = Server Management Objects, you find the documentation here: https://docs.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo) uses connection pooling by default, this would probably not open a second connection to the instance, but all the code explained in part 1 would be run. And you have to specify the SqlCredential parameter in every call. To avoid this, you could first just connect to the instance and than later on use this connection and the returned Server SMO (Server is the name of the type, you find the documentation here: https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.server) as the SqlInstance parameter of every call. This also helps with analyzing problems with a command because we can better see where the problem is located.

So it would look like this:

$instance = 'SRV1\SQL2017'
$sysadminCred = Get-Credential -Message 'sysadmin' -UserName 'myAdmin'
$server = Connect-DbaInstance -SqlInstance $instance -SqlCredential $sysadminCred
$databases = Get-DbaDatabase -SqlInstance $server
$logins = Get-DbaLogin -SqlInstance $server

Ok, it may save some keystrokes. But when do I need this? You need this if you want to specify additional parameters of Connect-DbaInstance to configure the connection. Like an ApplicationIntent of 'ReadOnly', a different ClientName, a special StatementTimeout or if you want to disable connection pooling by using NonPooledConnection.

How does this work? Inside of Connect-DbaInstance we test the input object for being already a Server SMO (yes, because the object type is named Server, we call the variable that holds that object $server inside of our commands - and I do that outside as well). If so, we basically just pass it out again and do nothing with it - so that might even save you some time if you have a lot of calls.

And now, after a long introduction, I come to the new features of the new code path. Because some commands run the internal call of Connect-DbaInstance with additional parameters, in the new code path Connect-DbaInstance also observes these parameters if they are passed in together with a Server SMO:

• Database
• ApplicationIntent
• NonPooledConnection
• StatementTimeout

When using these parameters together with a Server SMO, the current connection context is copied with .ConnectionContext.Copy() and a new Server SMO is build and configured based on this new connection context.

So you can do something like:

$serverNonPooled = Connect-DbaInstance -SqlInstance $server -NonPooledConnection
$result = Invoke-DbaQuery -SqlInstance $serverNonPooled -Query $queryThatNeedsNonPooledConnection 

In this case $serverNonPooled uses the same credentials and settings that were used when $server was created. Only that this new Server SMO now is linked to a single non pooled connection to the instance.

Reusing the same Server SMO can also help against exhausting the connection pool when connecting to Azure.

Part 3: More flexibility in setting the value for the output property ComputerName

Nearly every dbatools command returns the ComputerName as the first property of the resulting objects. But how and where is the value set? It is set inside of Connect-DbaInstance while building a new Server SMO based on these default rules:

• If the target instance is an Azure SQL Database ($server.DatabaseEngineType -eq "SqlAzureDatabase"), use ComputerName of the input SqlInstance ($instance.ComputerName).
• If the target instance is on Linux ($server.HostPlatform -eq 'Linux'), also use ComputerName of the input SqlInstance ($instance.ComputerName). (Why? Because this is often a docker container where the NetName is not very helpful.)
• If the Server SMO property NetName is set ($server.NetName), then use this value which is the first part of what you get if you query @@SERVERNAME.
• If the Server SMO property NetName is not set, then use ComputerName of the input SqlInstance ($instance.ComputerName).

These rules are the same in the old and the new code path. So what is new? We have introduced a new config parameter named "commands.connect-dbainstance.smo.computername.source" where you can set the source object and property to override these rules. I have two examples.

This sets the ComputerName always to the input value, so you can always have the full qualified name of the computer in all output:

PS C:\> Set-DbatoolsConfig -FullName commands.connect-dbainstance.smo.computername.source -Value 'instance.ComputerName'
PS C:\> Get-DbaDatabase -SqlInstance SRV1.Company.Pri\SQL2016 | Format-Table -Property ComputerName, InstanceName, SqlInstance, Name, Status

ComputerName     InstanceName SqlInstance  Name           Status
------------     ------------ -----------  ----           ------
SRV1.Company.Pri SQL2016      SRV1\SQL2016 master         Normal
SRV1.Company.Pri SQL2016      SRV1\SQL2016 tempdb         Normal
SRV1.Company.Pri SQL2016      SRV1\SQL2016 model          Normal
SRV1.Company.Pri SQL2016      SRV1\SQL2016 msdb           Normal
SRV1.Company.Pri SQL2016      SRV1\SQL2016 AdventureWorks Normal

This sets the ComputerName always to ComputerNamePhysicalNetBIOS, so you can see on which node of a clustered instance the instance is currently running in all output:

PS C:\> Set-DbatoolsConfig -FullName commands.connect-dbainstance.smo.computername.source -Value 'server.ComputerNamePhysicalNetBIOS'
PS C:\> Get-DbaDatabase -SqlInstance SqlCluster | Format-Table -Property ComputerName, InstanceName, SqlInstance, Name, Status

ComputerName InstanceName SqlInstance Name           Status
------------ ------------ ----------- ----           ------
SRV1         MSSQLSERVER  SqlCluster  master         Normal
SRV1         MSSQLSERVER  SqlCluster  tempdb         Normal
SRV1         MSSQLSERVER  SqlCluster  model          Normal
SRV1         MSSQLSERVER  SqlCluster  msdb           Normal
SRV1         MSSQLSERVER  SqlCluster  AdventureWorks Normal

Part 4: Where we need your help

In general, we are happy if the new code path is often used to find bugs or needs for optimization. Especially the use in complex environments would help a lot.
There is also a constellation that we have not yet been able to test: The case "azure integrated" is covered, but has never been used. If you have a suitable environment, that would help us a lot.

Any further questions? Please comment down below.

[andreasjordan]

In case you have problems using Connect-DbaInstance to connect to the SQL Server instance, it might help to run the individual steps one by one.

Note: This only works well (and is tested) with PowerShell 5.1

Part 1: Connecting without dbatools, but the same way dbatools does

You will need the custom types from Microsoft.SqlServer.Smo.dll. You can find the file inside of dbatools (.\bin\smo\Microsoft.SqlServer.Smo.dll) with the current product version 15.0.18246.0 or take the one from SQL Server Management Studio if you have it installed (C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Microsoft.SqlServer.Smo.dll) with the current product version 16.100.46367.54. I will use the later for this demo.

Add-Type -Path 'C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Microsoft.SqlServer.Smo.dll'

$serverName = 'SRV1'

$sqlConnectionInfo = New-Object -TypeName Microsoft.SqlServer.Management.Common.SqlConnectionInfo -ArgumentList $serverName
$serverConnection = New-Object -TypeName Microsoft.SqlServer.Management.Common.ServerConnection -ArgumentList $sqlConnectionInfo
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $serverConnection
$server.ConnectionContext.ProcessID  # This will actually opens the connection and will query @@SPID

# Show some defaults:
$server.ConnectionContext.ConnectTimeout       # 15
$server.ConnectionContext.PacketSize           # 8192
$server.ConnectionContext.StatementTimeout     # 600
$server.ConnectionContext.NonPooledConnection  # $false

Part 2: Connecting like dbatools with integrated security and all the defaults

The following is equivalent to Connect-DbaInstance -SqlInstance SRV1. Some parameters have defaults and some are set every time. So this is the code that is run:

Add-Type -Path 'C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Microsoft.SqlServer.Smo.dll'

$serverName = 'SRV1'

$ClientName = 'dbatools PowerShell module - dbatools.io'
$ConnectTimeout = 15
$PacketSize = 4096
$StatementTimeout = 0

$sqlConnectionInfo = New-Object -TypeName Microsoft.SqlServer.Management.Common.SqlConnectionInfo -ArgumentList $serverName
$sqlConnectionInfo.ApplicationName = $ClientName
$sqlConnectionInfo.ConnectionTimeout = $ConnectTimeout
$sqlConnectionInfo.PacketSize = $PacketSize
$sqlConnectionInfo.Pooled = $true
$serverConnection = New-Object -TypeName Microsoft.SqlServer.Management.Common.ServerConnection -ArgumentList $sqlConnectionInfo
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $serverConnection
$server.ConnectionContext.StatementTimeout = $StatementTimeout
$null = $server.ConnectionContext.ExecuteWithResults("SELECT 'dbatools is opening a new connection'")

# Show some connection context properties:
$server.ConnectionContext.ConnectTimeout       # 15
$server.ConnectionContext.PacketSize           # 4096 (not 8192 anymore)
$server.ConnectionContext.StatementTimeout     # 0 (not 600 anymore)
$server.ConnectionContext.NonPooledConnection  # $false

Part 3: Connecting with SQL Login

Again with only the bare minimum like in part 1, but now using a SQL Login as the parameter -SqlCredential. This is done by adding username and password to the connection info object.

$serverName = 'SRV1'
$SqlCredential = Get-Credential -Message 'SQL Login' -UserName sqllogin

$sqlConnectionInfo = New-Object -TypeName Microsoft.SqlServer.Management.Common.SqlConnectionInfo -ArgumentList $serverName
$sqlConnectionInfo.SecurePassword = $SqlCredential.Password
$sqlConnectionInfo.UserName = $SqlCredential.UserName
$serverConnection = New-Object -TypeName Microsoft.SqlServer.Management.Common.ServerConnection -ArgumentList $sqlConnectionInfo
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $serverConnection
$server.ConnectionContext.ProcessID  # This will actually opens the connection and will query @@SPID

Part 4: Connecting with Windows Login

Again with only the bare minimum like in part 1, but now using a Windows Login as the parameter -SqlCredential. This is done by adding username and password to the server connection object.

$serverName = 'SRV1'
$SqlCredential = Get-Credential -Message 'Windows Login' -UserName COMPANY\noadmin

$username = ($SqlCredential.UserName).TrimStart("\")
if ($username -like "*\*") {
    $domain, $login = $username.Split("\")
    $username = "$login@$domain"
}
$sqlConnectionInfo = New-Object -TypeName Microsoft.SqlServer.Management.Common.SqlConnectionInfo -ArgumentList $serverName
$serverConnection = New-Object -TypeName Microsoft.SqlServer.Management.Common.ServerConnection -ArgumentList $sqlConnectionInfo
$serverConnection.ConnectAsUser = $true
$serverConnection.ConnectAsUserName = $username
$serverConnection.ConnectAsUserPassword = $SqlCredential.GetNetworkCredential().Password
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $serverConnection
$server.ConnectionContext.TrueLogin  # COMPANY\noadmin

[andreasjordan]

Are you wondering why the $username is changed? Mee too: #7525
If you know the reason, please comment.

[potatoqualitee]

I just saw this -- may help for some of our issues. I've been working on the super new SMO and i'm making progress

ConfigurationManager.ConnectionStrings["db"].ConnectionString;