commands

^

Connect-DbaInstance

Author Chrissy LeMaire (@cl), netnerds.net
Availability Windows, Linux, macOS

 

Aliases : cdi

Synopsis

Creates a robust, reusable SQL Server object.

Description

This command creates a robust, reusable sql server object.

It is robust because it initializes properties that do not cause enumeration by default. It also supports both Windows and SQL Server authentication methods, and detects which to use based upon the provided credentials.

By default, this command also sets the connection's ApplicationName property to "dbatools PowerShell module - dbatools.io - custom connection". If you're doing anything that requires profiling, you can look for this client name.

Alternatively, you can pass in whichever client name you'd like using the -ClientName parameter. There are a ton of other parameters for you to explore as well.

See https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx and https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.aspx, and https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx

To execute SQL commands, you can use $server.ConnectionContext.ExecuteReader($sql) or $server.Databases['master'].ExecuteNonQuery($sql)

Syntax

Connect-DbaInstance
    [-SqlInstance] <DbaInstanceParameter[]>
    [[-SqlCredential] <PSCredential>]
    [[-Database] <String>]
    [[-ApplicationIntent] <String>]
    [-AzureUnsupported]
    [[-BatchSeparator] <String>]
    [[-ClientName] <String>]
    [[-ConnectTimeout] <Int32>]
    [-EncryptConnection]
    [[-FailoverPartner] <String>]
    [[-LockTimeout] <Int32>]
    [[-MaxPoolSize] <Int32>]
    [[-MinPoolSize] <Int32>]
    [[-MinimumVersion] <Int32>]
    [-MultipleActiveResultSets]
    [-MultiSubnetFailover]
    [[-NetworkProtocol] <String>]
    [-NonPooledConnection]
    [[-PacketSize] <Int32>]
    [[-PooledConnectionLifetime] <Int32>]
    [[-SqlExecutionModes] <String>]
    [[-StatementTimeout] <Int32>]
    [-TrustServerCertificate]
    [[-WorkstationId] <String>]
    [[-AppendConnectionString] <String>]
    [-SqlConnectionOnly]
    [[-AzureDomain] <String>]
    [[-AuthenticationType] <String>]
    [[-Tenant] <String>]
    [[-Thumbprint] <String>]
    [[-Store] <String>]
    [[-AccessToken] <String>]
    [-DisableException]
    [<CommonParameters>]

 

Examples

 

Example: 1
PS C:\> Connect-DbaInstance -SqlInstance sql2014

Creates an SMO Server object that connects using Windows Authentication

Example: 2
PS C:\> $wincred = Get-Credential ad\sqladmin
PS C:\> Connect-DbaInstance -SqlInstance sql2014 -SqlCredential $wincred

Creates an SMO Server object that connects using alternative Windows credentials

Example: 3
PS C:\> $sqlcred = Get-Credential sqladmin
PS C:\> $server = Connect-DbaInstance -SqlInstance sql2014 -SqlCredential $sqlcred

Login to sql2014 as SQL login sqladmin.

Example: 4
PS C:\> $server = Connect-DbaInstance -SqlInstance sql2014 -ClientName "my connection"

Creates an SMO Server object that connects using Windows Authentication and uses the client name "my connection". So when you open up profiler or use extended events, you can search for "my
connection".

Example: 5
PS C:\> $server = Connect-DbaInstance -SqlInstance sql2014 -AppendConnectionString "Packet Size=4096;AttachDbFilename=C:\MyFolder\MyDataFile.mdf;User Instance=true;"

Creates an SMO Server object that connects to sql2014 using Windows Authentication, then it sets the packet size (this can also be done via -PacketSize) and other connection attributes.

Example: 6
PS C:\> $server = Connect-DbaInstance -SqlInstance sql2014 -NetworkProtocol TcpIp -MultiSubnetFailover

Creates an SMO Server object that connects using Windows Authentication that uses TCP/IP and has MultiSubnetFailover enabled.

Example: 7
PS C:\> $server = Connect-DbaInstance sql2016 -ApplicationIntent ReadOnly

Connects with ReadOnly ApplicationIntent.

Example: 8
PS C:\> $server = Connect-DbaInstance -SqlInstance myserver.database.windows.net -Database mydb -SqlCredential [email protected] -DisableException
PS C:\> Invoke-DbaQuery -SqlInstance $server -Query "select 1 as test"

Logs into Azure SQL DB using AAD / Azure Active Directory, then performs a sample query.

Example: 9
PS C:\> $server = Connect-DbaInstance -SqlInstance psdbatools.database.windows.net -Database dbatools -DisableException
PS C:\> Invoke-DbaQuery -SqlInstance $server -Query "select 1 as test"

Logs into Azure SQL DB using AAD Integrated Auth, then performs a sample query.

Example: 10
PS C:\> $server = Connect-DbaInstance -SqlInstance "myserver.public.cust123.database.windows.net,3342" -Database mydb -SqlCredential [email protected] -DisableException
PS C:\> Invoke-DbaQuery -SqlInstance $server -Query "select 1 as test"

Logs into Azure SQL Managed instance using AAD / Azure Active Directory, then performs a sample query.

Example: 11
PS C:\> $server = Connect-DbaInstance -SqlInstance db.mycustomazure.com -Database mydb -AzureDomain mycustomazure.com -DisableException
PS C:\> Invoke-DbaQuery -SqlInstance $server -Query "select 1 as test"

In the event your AzureSqlDb is not on a database.windows.net domain, you can set a custom domain using the AzureDomain parameter.
This tells Connect-DbaInstance to login to the database using the method that works best with Azure.

Example: 12
PS C:\> $server = Connect-DbaInstance -ConnectionString "Data Source=TCP:mydb.database.windows.net,1433;User ID=sqladmin;Password=adfasdf;MultipleActiveResultSets=False;Connect

Timeout=30;Encrypt=True;TrustServerCertificate=False;"

PS C:\> Invoke-DbaQuery -SqlInstance $server -Query "select 1 as test"

Logs into Azure using a preconstructed connstring, then performs a sample query.
ConnectionString is an alias of SqlInstance, so you can use -SqlInstance $connstring as well.

Example: 13
PS C:\> $cred = Get-Credential guid-app-id-here # appid for username, clientsecret for password
PS C:\> $server = Connect-DbaInstance -SqlInstance psdbatools.database.windows.net -Database abc -SqCredential $cred -Tenant guidheremaybename
PS C:\> Invoke-DbaQuery -SqlInstance $server -Query "select 1 as test"

When connecting from a non-Azure workstation, logs into Azure using Universal with MFA Support with a username and password, then performs a sample query.

Example: 14
PS C:\> $server = Connect-DbaInstance -SqlInstance psdbatools.database.windows.net -Database abc -AuthenticationType 'AD Universal with MFA Support'
PS C:\> Invoke-DbaQuery -SqlInstance $server -Query "select 1 as test"

When connecting from an Azure VM with .NET 4.7.2 and higher, logs into Azure using Universal with MFA Support, then performs a sample query.

Example: 15
PS C:\> $cred = Get-Credential guid-app-id-here # appid for username, clientsecret for password
PS C:\> Set-DbatoolsConfig -FullName azure.tenantid -Value 'guidheremaybename' -Passthru | Register-DbatoolsConfig
PS C:\> Set-DbatoolsConfig -FullName azure.appid -Value $cred.Username -Passthru | Register-DbatoolsConfig
PS C:\> Set-DbatoolsConfig -FullName azure.clientsecret -Value $cred.Password -Passthru | Register-DbatoolsConfig # requires securestring
PS C:\> Set-DbatoolsConfig -FullName sql.connection.database -Value abc -Passthru | Register-DbatoolsConfig
PS C:\> Connect-DbaInstance -SqlInstance psdbatools.database.windows.net

Permanently sets some app id config values. To set them temporarily (just for a session), remove -Passthru | Register-DbatoolsConfig
When connecting from a non-Azure workstation or an Azure VM without .NET 4.7.2 and higher, logs into Azure using Universal with MFA Support, then performs a sample query.

Example: 16
PS C:\> $server = Connect-DbaInstance -SqlInstance psdbatools.database.windows.net -Thumbprint FF6361E82F21664F64A2576BB49EAC429BD5ABB6 -Store CurrentUser -Tenant tenant-guid -SqlCredential

app-id-guid-here -Database abc

PS C:\> Invoke-DbaQuery -SqlInstance $server -Query "select 1 as test"

Logs into Azure using Universal with MFA Support with a certificate, then performs a sample query. Note that you will be prompted for a password but the password can be left blank and the certificate
will be used instead.

Example: 17
PS C:\> Set-DbatoolsConfig -FullName sql.connection.experimental -Value $true
PS C:\> $sqlcred = Get-Credential sqladmin
PS C:\> $server = Connect-DbaInstance -SqlInstance sql2014 -SqlCredential $sqlcred
PS C:\> Invoke-DbaQuery -SqlInstance $server -Query "select 1 as test"

Use the new code path for handling connections. Especially when you have problems with connection pooling, try this.
We also have added additional -Verbose and -Debug output to help us understand your problem if you open an issue related to connections.
For additional information about how the new code path works, please have a look at the code: https://github.com/sqlcollaborative/dbatools/blob/development/functions/Connect-DbaInstance.ps1
If you like to use the new code path permanently, register this config:

PS C:\> Set-DbatoolsConfig -FullName sql.connection.experimental -Value $true -Passthru | Register-DbatoolsConfig

As we would like to use the new code path as a default in the future, please give feedback if you are using it in your environment.

Example: 18
PS C:\> Set-DbatoolsConfig -FullName sql.connection.experimental -Value $true
PS C:\> $azureCredential = Get-Credential -Message 'Azure Credential'
PS C:\> $azureAccount = Connect-AzAccount -Credential $azureCredential
PS C:\> $azureToken = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
PS C:\> $azureInstance = "YOURSERVER.database.windows.net"
PS C:\> $server = Connect-DbaInstance -SqlInstance $azureInstance -AccessToken $azureToken
PS C:\> Invoke-DbaQuery -SqlInstance $server -Query "select 1 as test"

Connect to an Azure SQL Database or an Azure SQL Managed Instance with an AccessToken.
Note that the token is valid for only one hour and cannot be renewed automatically.
This is only available in the new code path for handling connections (see example above).

Required Parameters

-SqlInstance

The target SQL Server instance or instances. This can be a collection and receive pipeline input to allow the function to be executed against multiple SQL Server instances.

Alias Connstring,ConnectionString
Required True
Pipeline true (ByValue)
Default Value

Optional Parameters

-SqlCredential

Credential object used to connect to the SQL Server Instance as a different user. This can be a Windows or SQL Server account. Windows users are determined by the existence of a backslash, so if you are intending to use an alternative Windows connection instead of a SQL login, ensure it contains a backslash.

Alias
Required False
Pipeline false
Default Value
-Database

The database(s) to process. This list is auto-populated from the server.

Alias
Required False
Pipeline false
Default Value (Get-DbatoolsConfigValue -FullName 'sql.connection.database')
-ApplicationIntent

Declares the application workload type when connecting to a server. Valid values are "ReadOnly" and "ReadWrite".

Alias
Required False
Pipeline false
Default Value
Accepted Values ReadOnly,ReadWrite
-AzureUnsupported

Terminate if Azure is detected but not supported

Alias
Required False
Pipeline false
Default Value False
-BatchSeparator

A string to separate groups of SQL statements being executed. By default, this is "GO".

Alias
Required False
Pipeline false
Default Value
-ClientName

By default, this command sets the client's ApplicationName property to "dbatools PowerShell module - dbatools.io". If you're doing anything that requires profiling, you can look for this client name. Using -ClientName allows you to set your own custom client application name.

Alias
Required False
Pipeline false
Default Value (Get-DbatoolsConfigValue -FullName 'sql.connection.clientname')
-ConnectTimeout

The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. Valid values are integers between 0 and 2147483647. When opening a connection to a Azure SQL Database, set the connection timeout to 30 seconds.

Alias
Required False
Pipeline false
Default Value ([Sqlcollaborative.Dbatools.Connection.ConnectionHost]::SqlConnectionTimeout)
-EncryptConnection

If this switch is enabled, SQL Server uses SSL encryption for all data sent between the client and server if the server has a certificate installed. For more information, see Connection String Syntax. https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/connection-string-syntax Beginning in .NET Framework 4.5, when TrustServerCertificate is false and Encrypt is true, the server name (or IP address) in a SQL Server SSL certificate must exactly match the server name (or IP address) specified in the connection string. Otherwise, the connection attempt will fail. For information about support for certificates whose subject starts with a wildcard character (*), see Accepted wildcards used by server certificates for server authentication. https://support.microsoft.com/en-us/help/258858/accepted-wildcards-used-by-server-certificates-for-server-authenticati

Alias
Required False
Pipeline false
Default Value (Get-DbatoolsConfigValue -FullName 'sql.connection.encrypt')
-FailoverPartner

The name of the failover partner server where database mirroring is configured. If the value of this key is "" (an empty string), then Initial Catalog must be present in the connection string, and its value must not be "". The server name can be 128 characters or less. If you specify a failover partner but the failover partner server is not configured for database mirroring and the primary server (specified with the Server keyword) is not available, then the connection will fail. If you specify a failover partner and the primary server is not configured for database mirroring, the connection to the primary server (specified with the Server keyword) will succeed if the primary server is available.

Alias
Required False
Pipeline false
Default Value
-LockTimeout

Sets the time in seconds required for the connection to time out when the current transaction is locked.

Alias
Required False
Pipeline false
Default Value 0
-MaxPoolSize

Sets the maximum number of connections allowed in the connection pool for this specific connection string.

Alias
Required False
Pipeline false
Default Value 0
-MinPoolSize

Sets the minimum number of connections allowed in the connection pool for this specific connection string.

Alias
Required False
Pipeline false
Default Value 0
-MinimumVersion

Terminate if the target SQL Server instance version does not meet version requirements

Alias
Required False
Pipeline false
Default Value 0
-MultipleActiveResultSets

If this switch is enabled, an application can maintain multiple active result sets (MARS). If this switch is not enabled, an application must process or cancel all result sets from one batch before it can execute any other batch on that connection.

Alias
Required False
Pipeline false
Default Value False
-MultiSubnetFailover

If this switch is enabled, and your application is connecting to an AlwaysOn availability group (AG) on different subnets, detection of and connection to the currently active server will be faster. For more information about SqlClient support for Always On Availability Groups, see https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/sqlclient-support-for-high-availability-disaster-recovery

Alias
Required False
Pipeline false
Default Value False
-NetworkProtocol

Explicitly sets the network protocol used to connect to the server. Valid values are "TcpIp","NamedPipes","Multiprotocol","AppleTalk","BanyanVines","Via","SharedMemory" and "NWLinkIpxSpx"

Alias
Required False
Pipeline false
Default Value (Get-DbatoolsConfigValue -FullName 'sql.connection.protocol')
Accepted Values TcpIp,NamedPipes,Multiprotocol,AppleTalk,BanyanVines,Via,SharedMemory,NWLinkIpxSpx
-NonPooledConnection

If this switch is enabled, a non-pooled connection will be requested.

Alias
Required False
Pipeline false
Default Value False
-PacketSize

Sets the size in bytes of the network packets used to communicate with an instance of SQL Server. Must match at server.

Alias
Required False
Pipeline false
Default Value (Get-DbatoolsConfigValue -FullName 'sql.connection.packetsize')
-PooledConnectionLifetime

When a connection is returned to the pool, its creation time is compared with the current time and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. This is useful in clustered configurations to force load balancing between a running server and a server just brought online. A value of zero (0) causes pooled connections to have the maximum connection timeout.

Alias
Required False
Pipeline false
Default Value 0
-SqlExecutionModes

The SqlExecutionModes enumeration contains values that are used to specify whether the commands sent to the referenced connection to the server are executed immediately or saved in a buffer. Valid values include "CaptureSql", "ExecuteAndCaptureSql" and "ExecuteSql".

Alias
Required False
Pipeline false
Default Value
Accepted Values CaptureSql,ExecuteAndCaptureSql,ExecuteSql
-StatementTimeout

Sets the number of seconds a statement is given to run before failing with a timeout error.

Alias
Required False
Pipeline false
Default Value (Get-DbatoolsConfigValue -FullName 'sql.execution.timeout')
-TrustServerCertificate

When this switch is enabled, the channel will be encrypted while bypassing walking the certificate chain to validate trust.

Alias
Required False
Pipeline false
Default Value (Get-DbatoolsConfigValue -FullName 'sql.connection.trustcert')
-WorkstationId

Sets the name of the workstation connecting to SQL Server.

Alias
Required False
Pipeline false
Default Value
-AppendConnectionString

Appends to the current connection string. Note that you cannot pass authentication information using this method. Use -SqlInstance and optionally -SqlCredential to set authentication information.

Alias
Required False
Pipeline false
Default Value
-SqlConnectionOnly

Instead of returning a rich SMO server object, this command will only return a SqlConnection object when setting this switch.

Alias
Required False
Pipeline false
Default Value False
-AzureDomain

By default, this is set to database.windows.net In the event your AzureSqlDb is not on a database.windows.net domain, you can set a custom domain using the AzureDomain parameter. This tells Connect-DbaInstance to login to the database using the method that works best with Azure.

Alias
Required False
Pipeline false
Default Value database.windows.net
-AuthenticationType

Basically used to force AD Universal with MFA Support when other types have been detected

Alias
Required False
Pipeline false
Default Value Auto
Accepted Values Auto,AD Universal with MFA Support
-Tenant

The TenantId for an Azure Instance

Alias
Required False
Pipeline false
Default Value (Get-DbatoolsConfigValue -FullName 'azure.tenantid')
-Thumbprint

Thumbprint for connections to Azure MSI

Alias
Required False
Pipeline false
Default Value (Get-DbatoolsConfigValue -FullName 'azure.certificate.thumbprint')
-Store

Store where the Azure MSI certificate is stored

Alias
Required False
Pipeline false
Default Value (Get-DbatoolsConfigValue -FullName 'azure.certificate.store')
Accepted Values CurrentUser,LocalMachine
-AccessToken

Connect to an Azure SQL Database or an Azure SQL Managed Instance with an AccessToken, that has to be generated with Get-AzAccessToken. Note that the token is valid for only one hour and cannot be renewed automatically. This is only available in the new code path for handling connections (see the last two examples).

Alias
Required False
Pipeline false
Default Value
-DisableException

By default in most of our commands, when something goes wrong we try to catch it, interpret it and give you a friendly warning message. This command, however, gifts you with "sea of red" exceptions, by default, because it is useful for advanced scripting. Using this switch turns our "nice by default" feature on which makes errors into pretty warnings.

Alias
Required False
Pipeline false
Default Value False

 

Want to see the source code for this command? Check out Connect-DbaInstance on GitHub.
Want to see the Bill Of Health for this command? Check out Connect-DbaInstance.