< Back
New-DbConnection
Post
NAME New-DbConnection
SYNOPSIS
Create a SQL Server connection or connection string.
SYNTAX
New-DbConnection [-ApplicationIntent <Object>] [-ApplicationName <Object>] [-AsynchronousProcessing <Object>]
[-AttachDBFilename <Object>] [-Authentication <Object>] [-BrowsableConnectionString <Object>]
[-ColumnEncryptionSetting <Object>] [-ConnectionReset <Object>] [-ConnectRetryCount <Object>]
[-ConnectRetryInterval <Object>] [-ConnectTimeout <Object>] [-ContextConnection <Object>] [-CurrentLanguage
<Object>] [[-DataSource] <String>] [-Encrypt <Object>] [-Enlist <Object>] [-FailoverPartner <Object>]
[[-InitialCatalog] <String>] [-IntegratedSecurity <Object>] [-LoadBalanceTimeout <Object>] [-MaxPoolSize <Object>]
[-MinPoolSize <Object>] [-MultipleActiveResultSets <Object>] [-MultiSubnetFailover <Object>] [-NetworkLibrary
<Object>] [-PacketSize <Object>] [[-Password] <String>] [-PersistSecurityInfo <Object>] [-Pooling <Object>]
[-Replication <Object>] [-TransactionBinding <Object>] [-TransparentNetworkIPResolution <Object>]
[-TrustServerCertificate <Object>] [-TypeSystemVersion <Object>] [[-UserID] <String>] [-UserInstance <Object>]
[-WorkstationID <String>] [-ConnectionString <Object>] [-SqlCredential <Object>] [-AsString] [-Open]
[-FireInfoMessageEventOnUserErrors] [<CommonParameters>]
DESCRIPTION
This is a safe way of building a connection string without joining them together manually.
PARAMETERS
-ApplicationIntent <Object>
ReadOnly or ReadWrite for AlwaysOn Availability Groups.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-ApplicationName <Object>
Free text recorded by SQL Server so that DBAs can identify what a session is being used for. Also useful for
connection pooling.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-AsynchronousProcessing <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-AttachDBFilename <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-Authentication <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-BrowsableConnectionString <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-ColumnEncryptionSetting <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-ConnectionReset <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-ConnectRetryCount <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-ConnectRetryInterval <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-ConnectTimeout <Object>
The number of seconds to wait before timing out the connection.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-ContextConnection <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-CurrentLanguage <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-DataSource <String>
Server name and instance.
Required? false
Position? 1
Default value
Accept pipeline input? true (ByValue, ByPropertyName)
Accept wildcard characters? false
-Encrypt <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-Enlist <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-FailoverPartner <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-InitialCatalog <String>
Database name. If not specified the login's default database is used. It's strongly recommended to always
specify master so that connection pooling can be used.
Required? false
Position? 2
Default value
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? false
-IntegratedSecurity <Object>
Enabled automatically if no UserID, Password, or SqlCredential is passed in. However you can explicitly set it
also.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-LoadBalanceTimeout <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-MaxPoolSize <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-MinPoolSize <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-MultipleActiveResultSets <Object>
A switch to enable this functionality which is required for Entity Framework (but not LINQ to SQL).
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-MultiSubnetFailover <Object>
For High Availability scenarios connections will be concurrently attempted on multiple IPs concurrently (but
only for Availability Group listeners and Failover Clusters on SQL 2012+). This is not required as of .NET
Framework 4.6.1 as it's enabled automatically.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-NetworkLibrary <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-PacketSize <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-Password <String>
For SQL Authentication. Otherwise Integrated Security is used.
Required? false
Position? 4
Default value
Accept pipeline input? false
Accept wildcard characters? false
-PersistSecurityInfo <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-Pooling <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-Replication <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-TransactionBinding <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-TransparentNetworkIPResolution <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-TrustServerCertificate <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-TypeSystemVersion <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-UserID <String>
For SQL Authentication. Otherwise Integrated Security is used.
Required? false
Position? 3
Default value
Accept pipeline input? false
Accept wildcard characters? false
-UserInstance <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-WorkstationID <String>
By default this is populated with $env:COMPUTERNAME by the .NET Framework, however, it can be spoofed here.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-ConnectionString <Object>
A connection string to start off with.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-SqlCredential <Object>
As of .NET Framework 4.5 the preferred method of passing SQL Authentication credentials is using
System.Data.SqlClient.SqlCredential. PSCredentials will be converted to this automatically. This is added to
the connection object, and so, cannot be used if you are requesting only a connection string.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-AsString [<SwitchParameter>]
Return a connection string instead of a connection object. This cannot be used with SqlCredential.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-Open [<SwitchParameter>]
Open the connection for you before returning it. Make sure you close it at some stage.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-FireInfoMessageEventOnUserErrors [<SwitchParameter>]
Required? false
Position? named
Default value False
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
None. You cannot pipe objects.
OUTPUTS
A connection object or string.
NOTES
For descriptions of other parameter values please check MSDN.
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>$serverInstance = ".\\SQL2016"
New-DbConnection $serverInstance master
Connect to a local server. Returns the connection object.
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>$serverInstance = ".\\SQL2016"
New-DbConnection $serverInstance master some_user unsafe_password
Connect to a server with a username and password. There are better ways to do this. Returns:
Data Source=.\\SQL2016;Initial Catalog=master;User ID=some_user;Password=unsafe_password
RELATED LINKS
https://msdn.microsoft.com/en-us/librar ... aspx?f=255&
MSPPError=-2147217396
SYNOPSIS
Create a SQL Server connection or connection string.
SYNTAX
New-DbConnection [-ApplicationIntent <Object>] [-ApplicationName <Object>] [-AsynchronousProcessing <Object>]
[-AttachDBFilename <Object>] [-Authentication <Object>] [-BrowsableConnectionString <Object>]
[-ColumnEncryptionSetting <Object>] [-ConnectionReset <Object>] [-ConnectRetryCount <Object>]
[-ConnectRetryInterval <Object>] [-ConnectTimeout <Object>] [-ContextConnection <Object>] [-CurrentLanguage
<Object>] [[-DataSource] <String>] [-Encrypt <Object>] [-Enlist <Object>] [-FailoverPartner <Object>]
[[-InitialCatalog] <String>] [-IntegratedSecurity <Object>] [-LoadBalanceTimeout <Object>] [-MaxPoolSize <Object>]
[-MinPoolSize <Object>] [-MultipleActiveResultSets <Object>] [-MultiSubnetFailover <Object>] [-NetworkLibrary
<Object>] [-PacketSize <Object>] [[-Password] <String>] [-PersistSecurityInfo <Object>] [-Pooling <Object>]
[-Replication <Object>] [-TransactionBinding <Object>] [-TransparentNetworkIPResolution <Object>]
[-TrustServerCertificate <Object>] [-TypeSystemVersion <Object>] [[-UserID] <String>] [-UserInstance <Object>]
[-WorkstationID <String>] [-ConnectionString <Object>] [-SqlCredential <Object>] [-AsString] [-Open]
[-FireInfoMessageEventOnUserErrors] [<CommonParameters>]
DESCRIPTION
This is a safe way of building a connection string without joining them together manually.
PARAMETERS
-ApplicationIntent <Object>
ReadOnly or ReadWrite for AlwaysOn Availability Groups.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-ApplicationName <Object>
Free text recorded by SQL Server so that DBAs can identify what a session is being used for. Also useful for
connection pooling.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-AsynchronousProcessing <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-AttachDBFilename <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-Authentication <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-BrowsableConnectionString <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-ColumnEncryptionSetting <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-ConnectionReset <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-ConnectRetryCount <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-ConnectRetryInterval <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-ConnectTimeout <Object>
The number of seconds to wait before timing out the connection.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-ContextConnection <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-CurrentLanguage <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-DataSource <String>
Server name and instance.
Required? false
Position? 1
Default value
Accept pipeline input? true (ByValue, ByPropertyName)
Accept wildcard characters? false
-Encrypt <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-Enlist <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-FailoverPartner <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-InitialCatalog <String>
Database name. If not specified the login's default database is used. It's strongly recommended to always
specify master so that connection pooling can be used.
Required? false
Position? 2
Default value
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? false
-IntegratedSecurity <Object>
Enabled automatically if no UserID, Password, or SqlCredential is passed in. However you can explicitly set it
also.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-LoadBalanceTimeout <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-MaxPoolSize <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-MinPoolSize <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-MultipleActiveResultSets <Object>
A switch to enable this functionality which is required for Entity Framework (but not LINQ to SQL).
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-MultiSubnetFailover <Object>
For High Availability scenarios connections will be concurrently attempted on multiple IPs concurrently (but
only for Availability Group listeners and Failover Clusters on SQL 2012+). This is not required as of .NET
Framework 4.6.1 as it's enabled automatically.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-NetworkLibrary <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-PacketSize <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-Password <String>
For SQL Authentication. Otherwise Integrated Security is used.
Required? false
Position? 4
Default value
Accept pipeline input? false
Accept wildcard characters? false
-PersistSecurityInfo <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-Pooling <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-Replication <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-TransactionBinding <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-TransparentNetworkIPResolution <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-TrustServerCertificate <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-TypeSystemVersion <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-UserID <String>
For SQL Authentication. Otherwise Integrated Security is used.
Required? false
Position? 3
Default value
Accept pipeline input? false
Accept wildcard characters? false
-UserInstance <Object>
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-WorkstationID <String>
By default this is populated with $env:COMPUTERNAME by the .NET Framework, however, it can be spoofed here.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-ConnectionString <Object>
A connection string to start off with.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-SqlCredential <Object>
As of .NET Framework 4.5 the preferred method of passing SQL Authentication credentials is using
System.Data.SqlClient.SqlCredential. PSCredentials will be converted to this automatically. This is added to
the connection object, and so, cannot be used if you are requesting only a connection string.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-AsString [<SwitchParameter>]
Return a connection string instead of a connection object. This cannot be used with SqlCredential.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-Open [<SwitchParameter>]
Open the connection for you before returning it. Make sure you close it at some stage.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-FireInfoMessageEventOnUserErrors [<SwitchParameter>]
Required? false
Position? named
Default value False
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
None. You cannot pipe objects.
OUTPUTS
A connection object or string.
NOTES
For descriptions of other parameter values please check MSDN.
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>$serverInstance = ".\\SQL2016"
New-DbConnection $serverInstance master
Connect to a local server. Returns the connection object.
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>$serverInstance = ".\\SQL2016"
New-DbConnection $serverInstance master some_user unsafe_password
Connect to a server with a username and password. There are better ways to do this. Returns:
Data Source=.\\SQL2016;Initial Catalog=master;User ID=some_user;Password=unsafe_password
RELATED LINKS
https://msdn.microsoft.com/en-us/librar ... aspx?f=255&
MSPPError=-2147217396