< Back
Connect-DbaInstance
Post
NAME Connect-DbaInstance
SYNOPSIS
Creates a robust, reusable SQL Server object.
SYNTAX
Connect-DbaInstance [-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [[-SqlCredential]
<Pscredential>] [[-Database] <String>] [[-ApplicationIntent] <String>] [-AzureUnsupported <Switch>]
[[-BatchSeparator] <String>] [[-ClientName] <String>] [[-ConnectTimeout] <Int>] [-EncryptConnection <Switch>]
[[-FailoverPartner] <String>] [[-LockTimeout] <Int>] [[-MaxPoolSize] <Int>] [[-MinPoolSize] <Int>]
[[-MinimumVersion] <Int>] [-MultipleActiveResultSets <Switch>] [-MultiSubnetFailover <Switch>] [[-NetworkProtocol]
<String>] [-NonPooledConnection <Switch>] [[-PacketSize] <Int>] [[-PooledConnectionLifetime] <Int>]
[[-SqlExecutionModes] <String>] [[-StatementTimeout] <Int>] [-TrustServerCertificate <Switch>] [[-WorkstationId]
<String>] [[-AppendConnectionString] <String>] [-SqlConnectionOnly <Switch>] [[-AzureDomain] <String>]
[[-AuthenticationType] <String>] [[-Tenant] <String>] [[-Thumbprint] <String>] [[-Store] <String>]
[-DisableException <Switch>] [<CommonParameters>]
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/librar ... tring.aspx
and https://msdn.microsoft.com/en-us/librar ... ilder.aspx,
and https://msdn.microsoft.com/en-us/librar ... ction.aspx
To execute SQL commands, you can use $server.ConnectionContext.ExecuteReader($sql) or
$server.Databases['master'].ExecuteNonQuery($sql)
PARAMETERS
-AppendConnectionString [<String>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ApplicationIntent [<String>]
Declares the application workload type when connecting to a server.
Valid values are "ReadOnly" and "ReadWrite".
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-AuthenticationType [<String>]
Basically used to force AD Universal with MFA Support when other types have been detected
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-AzureDomain [<String>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-AzureUnsupported [<Switch>]
Terminate if Azure is detected but not supported
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-BatchSeparator [<String>]
A string to separate groups of SQL statements being executed. By default, this is "GO".
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ClientName [<String>]
By default, this command sets the client'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. Using -ClientName allows you to set your own custom client application name.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ConnectTimeout [<Int>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Database [<String>]
The database(s) to process. This list is auto-populated from the server.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DisableException [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EncryptConnection [<Switch>]
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 ... ing-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/hel ... cards-used
-by-server-certificates-for-server-authenticati
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-FailoverPartner [<String>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-LockTimeout [<Int>]
Sets the time in seconds required for the connection to time out when the current transaction is locked.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-MaxPoolSize [<Int>]
Sets the maximum number of connections allowed in the connection pool for this specific connection string.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-MinimumVersion [<Int>]
Terminate if the target SQL Server instance version does not meet version requirements
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-MinPoolSize [<Int>]
Sets the minimum number of connections allowed in the connection pool for this specific connection string.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-MultipleActiveResultSets [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-MultiSubnetFailover [<Switch>]
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/dot
net/framework/data/adonet/sql/sqlclient-support-for-high-availability-disaster-recovery
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NetworkProtocol [<String>]
Explicitly sets the network protocol used to connect to the server.
Valid values are "TcpIp","NamedPipes","Multiprotocol","AppleTalk","BanyanVines","Via","SharedMemory" and
"NWLinkIpxSpx"
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NonPooledConnection [<Switch>]
If this switch is enabled, a non-pooled connection will be requested.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-PacketSize [<Int>]
Sets the size in bytes of the network packets used to communicate with an instance of SQL Server. Must match
at server.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-PooledConnectionLifetime [<Int>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlConnectionOnly [<Switch>]
Instead of returning a rich SMO server object, this command will only return a SqlConnection object when
setting this switch.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlExecutionModes [<String>]
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".
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-StatementTimeout [<Int>]
Sets the number of seconds a statement is given to run before failing with a timeout error.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Store [<String>]
Store where the Azure MSI certificate is stored
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Tenant [<String>]
The TenantId for an Azure Instance
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Thumbprint [<String>]
Thumbprint for connections to Azure MSI
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-TrustServerCertificate [<Switch>]
When this switch is enabled, the channel will be encrypted while bypassing walking the certificate chain to
validate trust.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-WorkstationId [<String>]
Sets the name of the workstation connecting to SQL Server.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Connect, Connection
Author: Chrissy LeMaire (@cl), netnerds.net
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- 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
me@mydomain.onmicrosoft.com -DisableException
PS C:\\> Invoke-Query -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-Query -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 me@mydomain.onmicrosoft.com -DisableException
PS C:\\> Invoke-Query -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-Query -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-Query -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-Query -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-Query -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-Query -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.
RELATED LINKS
https://dbatools.io/Connect-DbaInstance
SYNOPSIS
Creates a robust, reusable SQL Server object.
SYNTAX
Connect-DbaInstance [-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [[-SqlCredential]
<Pscredential>] [[-Database] <String>] [[-ApplicationIntent] <String>] [-AzureUnsupported <Switch>]
[[-BatchSeparator] <String>] [[-ClientName] <String>] [[-ConnectTimeout] <Int>] [-EncryptConnection <Switch>]
[[-FailoverPartner] <String>] [[-LockTimeout] <Int>] [[-MaxPoolSize] <Int>] [[-MinPoolSize] <Int>]
[[-MinimumVersion] <Int>] [-MultipleActiveResultSets <Switch>] [-MultiSubnetFailover <Switch>] [[-NetworkProtocol]
<String>] [-NonPooledConnection <Switch>] [[-PacketSize] <Int>] [[-PooledConnectionLifetime] <Int>]
[[-SqlExecutionModes] <String>] [[-StatementTimeout] <Int>] [-TrustServerCertificate <Switch>] [[-WorkstationId]
<String>] [[-AppendConnectionString] <String>] [-SqlConnectionOnly <Switch>] [[-AzureDomain] <String>]
[[-AuthenticationType] <String>] [[-Tenant] <String>] [[-Thumbprint] <String>] [[-Store] <String>]
[-DisableException <Switch>] [<CommonParameters>]
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/librar ... tring.aspx
and https://msdn.microsoft.com/en-us/librar ... ilder.aspx,
and https://msdn.microsoft.com/en-us/librar ... ction.aspx
To execute SQL commands, you can use $server.ConnectionContext.ExecuteReader($sql) or
$server.Databases['master'].ExecuteNonQuery($sql)
PARAMETERS
-AppendConnectionString [<String>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ApplicationIntent [<String>]
Declares the application workload type when connecting to a server.
Valid values are "ReadOnly" and "ReadWrite".
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-AuthenticationType [<String>]
Basically used to force AD Universal with MFA Support when other types have been detected
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-AzureDomain [<String>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-AzureUnsupported [<Switch>]
Terminate if Azure is detected but not supported
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-BatchSeparator [<String>]
A string to separate groups of SQL statements being executed. By default, this is "GO".
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ClientName [<String>]
By default, this command sets the client'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. Using -ClientName allows you to set your own custom client application name.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ConnectTimeout [<Int>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Database [<String>]
The database(s) to process. This list is auto-populated from the server.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DisableException [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EncryptConnection [<Switch>]
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 ... ing-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/hel ... cards-used
-by-server-certificates-for-server-authenticati
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-FailoverPartner [<String>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-LockTimeout [<Int>]
Sets the time in seconds required for the connection to time out when the current transaction is locked.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-MaxPoolSize [<Int>]
Sets the maximum number of connections allowed in the connection pool for this specific connection string.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-MinimumVersion [<Int>]
Terminate if the target SQL Server instance version does not meet version requirements
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-MinPoolSize [<Int>]
Sets the minimum number of connections allowed in the connection pool for this specific connection string.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-MultipleActiveResultSets [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-MultiSubnetFailover [<Switch>]
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/dot
net/framework/data/adonet/sql/sqlclient-support-for-high-availability-disaster-recovery
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NetworkProtocol [<String>]
Explicitly sets the network protocol used to connect to the server.
Valid values are "TcpIp","NamedPipes","Multiprotocol","AppleTalk","BanyanVines","Via","SharedMemory" and
"NWLinkIpxSpx"
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NonPooledConnection [<Switch>]
If this switch is enabled, a non-pooled connection will be requested.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-PacketSize [<Int>]
Sets the size in bytes of the network packets used to communicate with an instance of SQL Server. Must match
at server.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-PooledConnectionLifetime [<Int>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlConnectionOnly [<Switch>]
Instead of returning a rich SMO server object, this command will only return a SqlConnection object when
setting this switch.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlExecutionModes [<String>]
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".
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-StatementTimeout [<Int>]
Sets the number of seconds a statement is given to run before failing with a timeout error.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Store [<String>]
Store where the Azure MSI certificate is stored
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Tenant [<String>]
The TenantId for an Azure Instance
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Thumbprint [<String>]
Thumbprint for connections to Azure MSI
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-TrustServerCertificate [<Switch>]
When this switch is enabled, the channel will be encrypted while bypassing walking the certificate chain to
validate trust.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-WorkstationId [<String>]
Sets the name of the workstation connecting to SQL Server.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Connect, Connection
Author: Chrissy LeMaire (@cl), netnerds.net
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- 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
me@mydomain.onmicrosoft.com -DisableException
PS C:\\> Invoke-Query -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-Query -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 me@mydomain.onmicrosoft.com -DisableException
PS C:\\> Invoke-Query -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-Query -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-Query -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-Query -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-Query -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-Query -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.
RELATED LINKS
https://dbatools.io/Connect-DbaInstance