.NET Connection Strings for SQL Server
SQL Server connection strings provide the parameters necessary to establish a successful connection to SQL Server, ensuring proper communication between the application and the database.
This guide will explain the key components of SQL Server connection strings, their construction, and best usage practices.
SQL Server connection strings
The following code snippet describes how to connect to SQL Server:
using Devart.Data.SqlServer; ... SqlConnection sqlConnection = new SqlConnection(); sqlConnection.DataSource = "127.0.0.1"; sqlConnection.UserId = "TestUser"; sqlConnection.Password = "TestPassword"; sqlConnection.Database = "TestDatabase"; sqlConnection.License Key = "**********";
Connection properties
The following table lists the valid names for values within the SQL Server connection strings.
| Name | Description |
|---|---|
| Server (Data Source, Address, Addr, Network Address) |
The name or network address of the SQL Server instance to connect to. The port number can be specified after the server name: Server=tcp:servername, portnumber When specifying a local instance, always use (local). To force this protocol, add one of the following prefixes: np:(local), tcp:(local), lpc:(local). |
| User ID (UID, User) | The SQL Server login account (not recommended). To maintain a higher security level, use Integrated Security or Trusted_Connection keywords instead. |
| Database | The name of the database. |
| Password | The password for the SQL Server account. |
| License Key | The license key. |
Advanced Connection String Options
Advanced connection string options allow additional customization and control over database connections.
Key options include:
| Name | Description |
|---|---|
| Authentication | The SQL Server authentication mode. |
| ApplicationIntent | The application workload type when connecting to a database. |
| Application Name | The name of the application. Use .NET SQLClient Data Provider if no application name is provided. |
| Async | When set to true, it enables asynchronous operation support. Recognized values are true, false, yes, and no. |
| AttachDBFilename (extended properties, Initial File Name) | The name of the primary database file along with the full path to any attachable databases. The path can be either absolute or relative, and if the DataDirectory substitution string is used, the database file must be located within a subdirectory of the directory specified by this string. |
| Connection Timeout | The time (in seconds) to wait for a server connection before aborting the attempt and reporting an error. |
| Connection Lifetime | When a connection is returned to the pool, its creation time is compared to the current time. If the time span (in seconds) exceeds the value specified by Connection Lifetime, the connection is destroyed. This helps enforce load balancing in clustered configurations, ensuring distribution between an active server and a newly brought online server. A value of zero (0) allows pooled connections to use the maximum connection timeout. |
| Connection Reset | Specifies whether the database connection is reset when retrieved from the pool. For SQL Server 7.0, setting this to false eliminates the need for an additional server round trip when acquiring a connection. However, be aware that the connection state, including the database context, will not be reset. |
| Context Connection | Is true if an in-process connection to SQL Server should be made. |
| Current Language | The SQL Server Language record name. |
| Default Command Timeout | The time (in seconds) to wait for a command to execute before aborting the attempt and throwing an error. A value of 0 indicates no time limit. |
| Encrypt | When set to true, SQL Server uses SSL encryption for all data exchanged between the client and server, provided the server has a certificate installed. Accepted values are true, false, yes, and no. |
| Enlist | Setting this to true ensures that the SQL Server connection pooler automatically enlists the connection in the current transaction context of the thread that created it. |
| Failover Partner | The name of the failover partner server where database mirroring is configured. |
| Initialization Command | Specifies the database-specific command that should be executed immediately after establishing the connection. |
| Integrated Security (Trusted_Connection) | When set to false, the connection uses a specified User ID and Password for authentication. When set to true, the current Windows account credentials are used instead. Accepted values include true, false, yes, no, and sspi (strongly recommended), which is equivalent to true. |
| Load Balance Timeout | The minimum duration (in seconds) that a connection remains in the connection pool before it is eligible for destruction. |
| Max Pool Size | The maximum number of connections allowed in the pool. The default value is 100. |
| Min Pool Size | The minimum number of connections allowed in the pool. The default value is 0. |
| MultipleActiveResultSets | When set to true, an application can handle multiple active result sets (MARS) simultaneously. When set to false, the application must process or cancel all result sets from one batch before executing another batch on the same connection. Accepted values are true and false. |
| MultiSubnetFailover | When set to true, the connection will attempt parallel connections during a multi-subnet failover or aggressively retry the TCP connection during a subnet failover. Accepted values are true and false. It is recommended to set this parameter to true when connecting to a SQL Server 2012 Availability Group listener or a SQL Server 2012 Failover Cluster Instance. For all other connection types, the default value of false should be used. |
| Network Library (Net) | The network library used to establish a connection to the SQL Server instance. Accepted values include dbnmpntw (Named Pipes), dbmsrpcn (Multiprotocol), dbmsadsn (Apple Talk), dbmsgnet (VIA), dbmslpcn (Shared Memory), dbmsspxn (IPX/SPX), dbmssocn (TCP/IP). The corresponding network DLL must be installed on the target system. If no network is specified and a local server is used (e.g., "." or "(local)"), Shared Memory is used by default. |
| Packet Size | The size (in bytes) of the network packets used for communication with a SQL Server instance. The default value is 8192. |
| Persist Security Info | When set to false or no (strongly recommended), security-sensitive information, such as the password, is not returned as part of the connection if it is open or has ever been open. Resetting the connection string will reset all values, including the password. Accepted values are true, false, yes, and no. |
| Pooling | When set to true, the SQLConnection object is retrieved from the appropriate pool. If no suitable connection exists, a new one is created and added to the pool. Accepted values are true, false, yes, and no. The default value is true. |
| Replication | Is set to true if replication is supported using the connection. |
| TrustServerCertificate | When set to true, SSL is used to encrypt the channel while bypassing the certificate chain validation to establish trust. However, if the connection string specifies TrustServerCertificate=true but does not set Encrypt=true, the channel remains unencrypted. Accepted values are true, false, yes, and no. For more details, refer to "Encryption Hierarchy" and "Using Encryption Without Validation" in SQL Server 2005 Books Online. |
| Type System Version |
A string value that indicates the expected type system for the application, such as: Type System Version=SQL Server 2000 Type System Version=SQL Server 2005 Type System Version=Latest |
| Transaction Scope Local | The value determines whether a connection automatically enlists in ambient transactions created by the .NET TransactionScope class. When set to true, the connection operates independently of the transaction scope, requiring manual transaction management. When set to false, the connection automatically enlists in the transaction scope, and its operations are governed by the scope's commit or rollback outcome. The default value is false. |
| User Instance | Indicates whether the connection should be redirected from the default SQL Server Express instance to a runtime-initiated instance running under the caller's account. |
| Workstation ID (WSID) | The name of the workstation connecting to SQL Server. The default value is the local computer name. |
| Validate Connection | Specifies whether to validate connections that are being retrieved from the pool. |
Conclusion
SQL Server connection strings play a vital role in connecting applications to SQL Server, ensuring smooth communication between the application and its back-end database engine. This article offers a comprehensive guide on configuring SQL Server connection strings, covering various parameters and implementation methods.
In this context, dotConnect for SQL Server emerges as an invaluable tool that simplifies these connections, enabling the development of high-performance applications built on SQL Server. To fully explore the capabilities of dotConnect for SQL Server, download the Free Trial and see how it performs under your specific workload!