3.2 The Connection String

When creating a connection, you must specify several pieces of required information. Typically, this includes the type of authentication or user to authenticate, the location of the database server, and the name of the database. In addition, OLE DB connection strings specify an OLE DB provider, and ODBC connection strings specify an ODBC driver. To specify this information, use the ConnectionString property.

The ConnectionString contains a series of name/value settings delimited by semicolons (;). The order of these settings is unimportant, as is the capitalization. Taken together, they specify the information needed to create a connection. Table 3-3 describes some settings you can use. Parameters that are used for connection pooling are omitted; they are discussed later in this chapter.

Connection strings are data source-specific, although they tend to have broad similarities. Most parameters in Table 3-2 are supported by the SQL Server, OLE DB, and Oracle providers, although some exceptions apply. Consult the documentation for your particular database product or your OLE DB or ODBC driver for more information.

Table 3-3. Basic connection string parameters

Parameter	Description
AttachDBFilename / Initial File Name	Used only if you want to connect to an attachable database file (for example, an .mdf file that isn't registered with the database system). Normally, you use the Initial Catalog parameter instead.
Connect Timeout / Connection Timeout	The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. Defaults to 15 seconds, and 0 seconds represents an infinite wait.
Data Source / Server / Address / Addr / Network Address	The server name or network address of the database product to connect to. Use localhost for the current computer.
Initial Catalog / Database	The name of the database to use for all subsequent operations (insertions, deletions, queries, and so on).
Integrated Security / Trusted_Connection	Defaults to false. When set to true or SSPI, the .NET provider attempts to connect to the data source using Windows integrated security.
Persist Security Info	When set to false (the default), security-sensitive information such as the password is removed from the ConnectionString property as soon as the connection is opened. Thus, you can't retrieve this information in your code.
User ID	The database account user ID.
Password/Pwd	The password corresponding to the User ID.

Table 3-4 lists some connection string settings that are specific to SQL Server.

Table 3-4. SQL Server connection string parameters

Parameter	Description
Current Language	The SQL Server language record name.
Network Library / Net	The network library used to establish a connection to an instance of SQL Server. Supported values include dbnmpntw (Named Pipes), dbmsrpcn (Multiprotocol), dbmsadsn (Apple Talk), dbmsgnet (VIA), dbmsipcn (Shared Memory), dbmsspxn (IPX/SPX), and dbmssocn (TCP/IP), which is the default.
Packet Size	The size in bytes of the network packets that communicate with an instance of SQL Server (defaults to 8192).
Workstation ID	The name of the workstation connecting to SQL Server. Defaults to the local computer name.

3.2.1 Setting Connection String Parameters

The following code snippet shows how you might set the ConnectionString property on a SqlConnection object. The actual connection string details are omitted.

SqlConnection con = new SqlConnection();
con.ConnectionString = "...";

All standard ADO.NET Connection objects also provide a constructor that accepts a value for the ConnectionString property. For example, the following code statement creates a SqlConnection object and sets the ConnectionString property in one statement. It's equivalent to the previous example.

SqlConnection con = new SqlConnection("...");

The next few sections present some sample connection strings with commonly used settings. Because the connection string varies depending on the provider, these examples are separated into provider-specific sections.

3.2.1.1 The SQL Server connection string

When using a SQL Server database, you need to specify the server name using the Data Source parameter (use localhost for the current computer), the Initial Catalog parameter (the database name), and the authentication information.

You have two options for supplying the authentication information. If your database uses SQL Server authentication, you can pass a user ID and password defined in SQL Server. This account should have permissions for the tables you want to access:

SqlConnection con = new SqlConnection("Data Source=localhost;" +
           "Initial Catalog=Northwind;user id=userid;password=password");

If your database allows integrated Windows authentication, you can signal this fact with the Integrated Security=SSPI connection string parameter. The Windows operating system then supplies the user account token for the currently logged-in user. This is more secure because the login information doesn't need to be visible in the code (or transmitted over the network):

SqlConnection con = new SqlConnection("Data Source=localhost;" +
           "Initial Catalog=Northwind;Integrated Security=SSPI");

Keep in mind that integrated security won't always execute in the security context of the application user. For example, consider a distributed application that performs a database query through a web service. If the web service connects using integrated authentication, it uses the login account of the ASP.NET worker process, not the account of the client making the request. The story is similar with a component exposed through .NET remoting, which uses the account that loaded the remote component host.

3.2.1.2 The MSDE connection string

MSDE is a scaled-down, freely distributable version of SQL Server you can use to develop very small systems with less than five users (see Appendix C for a brief overview). MSDE uses the same connection string format as SQL Server (in fact, MSDE uses the SQL Server engine under the hood). Like SQL Server, MSDE supports integrated authentication and SQL authentication. The only difference is found in the Data Source parameter, which consists of two parts: the computer name and the data source name, separated by a backslash character. The data source name will be NetSDK if MSDE was installed from the .NET framework SDK, or VSdotNET if installed as part of Visual Studio .NET. If you are using MSDE on the local machine, the server name should be set to localhost.

Here's an example that connects to an MSDE instance on the local computer that was installed from the .NET framework SDK:

SqlConnection con = new SqlConnection("Data Source=localhost\\NetSDK;" +
           "Initial Catalog=Northwind;Integrated Security=SSPI");

3.2.1.3 The OLE DB connection string

The OLE DB connection string resembles the SQL Server connection string. However, the support for some parameters depends on the OLE DB provider you use. Typically, an OLE DB connection string requires a Data Source parameter (use localhost for the current computer), the Initial Catalog parameter (the database name), and the user id and password parameters. It also requires a Provider setting that indicates which OLE DB provider to use.

The following code snippet shows a sample connection string that connects to a SQL Server database through the OLE DB provider. This is the only way to connect to a version of SQL Server earlier than 7.0:

OleDbConnection con = new OleDbConnection("Data Source=localhost;" +
           "Initial Catalog=Northwind;user id=sa;password=secret;" +
           "Provider=SQLOLEDB");

Here's an example that connects to an Access database file through the Jet provider:

OleDbConnection con = new OleDbConnection("Data Source=localhost;" +
           "Initial Catalog=c:\Nortwdind.mdb;" +
           "Provider=Microsoft.Jet.OLEDB.4.0");

3.2.1.4 The ODBC .NET connection string

The ODBC connection string resembles the SQL Server and OLE DB connection strings. However, the support for some parameters depends on the ODBC driver used. Typically, an ODBC connection string requires a Data Source parameter (use localhost for the current computer), the Initial Catalog parameter (the database name), and the user id and password parameters. It also requires a Driver setting that indicates the ODBC driver to use, or its data source name (DSN), which associates a symbolic name with a group of database settings that otherwise goes into the connection string. The DSN must be enclosed in curly braces and match exactly.

Here is an example that accesses an Excel file:

OdbcConnection con = new OdbcConnection(
           "Driver={Microsoft Excel Driver (*.xls)};" +
           "DBQ=c:\book1.xls");

Here's an example that uses the ODBC driver for MySQL (available from www.mysql.com). It adds a new connection string setting, Option, which configures certain low-level behaviors to support specific clients. For more information, refer to the MySQL documentation.

OdbcConnection con = new OdbcConnection(
           "Driver={MySQL ODBC 3.51 Driver};" +
           "Database=test;UID=root;PWD=secret;Option=3");

Use the Data Sources icon (in the Administrative Tools portion of the Control Panel) to configure ODBC DSN settings or add new drivers.

3.2.1.5 The Oracle .NET connection string

The Microsoft Oracle provider supports a smaller subset of connection-string options, as shown in Table 3-3. The Oracle provider also includes connection string settings that allow you to configure connection pooling. These are described in Chapter 5.

Here's how you can create an OracleConnection with a connection string:

OracleConnection con = new OracleConnection(
           "Data Source=Oracle8i;Integrated Security=true");

3.2.2 Security Risks of the Connection String

Be careful if you are constructing a connection string dynamically based on user input. For example, make sure you check that the user has not inserted any extra semicolons (or that all semicolons are contained inside apostrophes). Otherwise, the user can add additional connection string parameters, possibly tricking your code into connecting to the wrong database.

For example, you might request a password and place it in a connection string as follows:

connectionString = "Data Source=localhost;" +
  "Initial Catalog=Northwind;user id=" + txtUser.Text +
  ";password=" + txtPassword.Text;

In this case, a problem occurs if the user submits a password in the form ValidPassword;Initial Catalog=ValidDatabase. The connection string will now have two Initial Catalog parameters, and it will use the second one, which the user appended to the end of the password!

To overcome this sort of problem, you should never allow a user to specify connection string parameters directly. Consider storing this information in a configuration file.