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>]
    [-DedicatedAdminConnection]
    [-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:\> $connstring = "Data Source=TCP:mydb.database.windows.net,1433;User ID=sqladmin;Password=adfasdf;Connect Timeout=30;"
PS C:\> $server = Connect-DbaInstance -ConnectionString $connstring
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.
Note that generating access tokens is not supported on Core, so when using Tenant on Core, we rewrite the connection string with Active Directory Service Principal authentication instead.

Example: 14
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: 15
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:\> $azureDatabase = "MYDATABASE"
PS C:\> $server = Connect-DbaInstance -SqlInstance $azureInstance -Database $azureDatabase -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.

Example: 16
PS C:\> $token = (New-DbaAzAccessToken -Type RenewableServicePrincipal -Subtype AzureSqlDb -Tenant $tenantid -Credential $cred).GetAccessToken()
PS C:\> Connect-DbaInstance -SqlInstance sample.database.windows.net -Accesstoken $token

Uses dbatools to generate the access token for an Azure SQL Database, then logs in using that AccessToken.

Example: 17
PS C:\> $server = Connect-DbaInstance -SqlInstance srv1 -DedicatedAdminConnection
PS C:\> $dbaProcess = Get-DbaProcess -SqlInstance $server -ExcludeSystemSpids
PS C:\> $killedProcess = $dbaProcess | Out-GridView -OutputMode Multiple | Stop-DbaProcess
PS C:\> $server | Disconnect-DbaInstance

Creates a dedicated admin connection (DAC) to the default instance on server srv1.
Receives all non-system processes from the instance using the DAC.
Opens a grid view to let the user select processes to be stopped.
Closes the connection.

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 (Get-DbatoolsConfigValue -FullName 'sql.connection.multisubnetfailover')
-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. The default is read from the configuration 'sql.execution.timeout' that is currently set to 0 (unlimited). If you want to change this to 10 minutes, use: Set-DbatoolsConfig -FullName 'sql.execution.timeout' -Value 600

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

Not used in the current version.

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

Not used in the current version.

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

Not used in the current version.

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.

Alias
Required False
Pipeline false
Default Value
-DedicatedAdminConnection

Connects using "ADMIN:" to create a dedicated admin connection (DAC) as a non-pooled connection. If the instance is on a remote server, the remote access has to be enabled via "Set-DbaSpConfigure -Name RemoteDacConnectionsEnabled -Value $true" or "sp_configure 'remote admin connections', 1". The connection will not be closed if the variable holding the Server SMO is going out of scope, so it is very important to call .ConnectionContext.Disconnect() to close the connection. See example.

Alias
Required False
Pipeline false
Default Value False
-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.