| Author | Chrissy LeMaire (@cl), netnerds.net |
| Availability | Windows, Linux, macOS |
Aliases : cdi
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.
Creates a robust, reusable SQL Server object.
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)
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>]
[-AlwaysEncrypted]
[[-AppendConnectionString] <String>]
[-SqlConnectionOnly]
[[-AzureDomain] <String>]
[[-Tenant] <String>]
[[-AccessToken] <PSObject>]
[-DedicatedAdminConnection]
[-DisableException]
[<CommonParameters>]
PS C:\> Connect-DbaInstance -SqlInstance sql2014
Creates an SMO Server object that connects using Windows Authentication
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
PS C:\> $sqlcred = Get-Credential sqladmin
PS C:\> $server = Connect-DbaInstance -SqlInstance sql2014 -SqlCredential $sqlcred
Login to sql2014 as SQL login sqladmin.
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".
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.
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.
PS C:\> $server = Connect-DbaInstance sql2016 -ApplicationIntent ReadOnly
Connects with ReadOnly ApplicationIntent.
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.
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.
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.
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.
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.
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 -SqlCredential $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.
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.
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
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.
PS C:\> $token = New-DbaAzAccessToken -Type RenewableServicePrincipal -Subtype AzureSqlDb -Tenant $tenantid -Credential $cred
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.
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.
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 |
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 |
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') |
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 |
Terminate if Azure is detected but not supported
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
A string to separate groups of SQL statements being executed. By default, this is "GO".
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
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') |
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 | ([Dataplat.Dbatools.Connection.ConnectionHost]::SqlConnectionTimeout) |
If this switch is enabled, SQL Server uses SSL encryption for all data sent between the client and server.
Beginning in .NET Framework 4.5, when TrustServerCertificate is false and EncryptConnection 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') |
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 |
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 |
Sets the maximum number of connections allowed in the connection pool for this specific connection string.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
Sets the minimum number of connections allowed in the connection pool for this specific connection string.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
Terminate if the target SQL Server instance version does not meet version requirements
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
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 |
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') |
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 |
If this switch is enabled, a non-pooled connection will be requested.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
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') |
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 |
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 |
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') |
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') |
Sets the name of the workstation connecting to SQL Server.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Sets "Column Encryption Setting=enabled" on the connection so you can work with Always Encrypted values.
For more information, see https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/develop-using-always-encrypted-with-net-framework-data-provider
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
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 |
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 |
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 |
The TenantId for an Azure Instance
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | (Get-DbatoolsConfigValue -FullName 'azure.tenantid') |
Connect to an Azure SQL Database or an Azure SQL Managed Instance with an AccessToken, that has to be generated with Get-AzAccessToken or New-DbaAzAccessToken.
Note that the token is valid for only one hour and cannot be renewed automatically.
Note that the returned SMO is not a fully functional SMO. It can only be used in a limited list of commands like Invoke-DbaQuery, Import-DbaCsv or Write-DbaDbTableData.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
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 |
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 |