Connecting to a Data Source

After you have decided which of the .NET data providers you need to use, your next step is to declare and instantiate a Connection object. Listing 5.1 shows a simple example using the SqlClient data provider. This section will also discuss how to handle user names and passwords, how connection pooling is used, where to store connection string information and the importance of closing connections promptly.

Listing 5.1: A Typical SqlClient Connection String

Imports System.Data Imports System.Data.SqlClient Public Sub GetDataList()     Dim strConnect as String = _       "Data Source=localhost;Initial " & _       "Catalog=pubs; Integrated Security=SSPI; "     Dim myConn As SqlConnection = _        New SqlConnection(strConnect)     myConn.Open()       'continue with the work of this function     myConn.Close() End Sub

First, the Imports statements are placed at the top of the code module. This enables you to declare the objects with their short type names, rather than having to specify a fully qualified reference every time you use them in your code. Without the Imports statements, your declaration for the SqlConnection object would look like the following code:

Dim myConn As System.Data.SqlClient.SqlConnection = New _    System.Data.SqlClient.SqlConnection(strConnect)

We are taking advantage of the SqlConnection object’s parameterized constructor to set the ConnectionString property directly, at the same time as it is instantiated. Another option is to use the default constructor and then later set the ConnectionString property in a separate line of code, as shown here:

Dim myConn As SqlConnection = New SqlConnection() myConn.ConnectionString = _    "Data Source=localhost; Initial " & _    "Catalog=pubs; Integrated Security=SSPI; "

The connection string in this example is simple and contains the minimum information required to make a connection. The connection string must always be set before the connection is opened, and it cannot be changed after the connection is open.

Let’s examine each part of the connection string:

Data source  This is the machine name of the computer that is running SQL Server. In this case, our application is running on the same machine as SQL Server (common for web applications and server components), so we can use the generic reference localhost to indicate that.

Initial catalog  This is the name of the specific database that we want to access.

Integrated security  This indicates that the current user’s Windows credentials are being used to access SQL Server. We will discuss this further in the next section, “About Usernames and Passwords.”

Many other settings can be passed as a part of the connection string. You can use these to control the way that connection pooling works, the length of the time - out period, and security options. Some of these connection string options, particularly those that have to do with connection pooling will be discussed later in this chapter.

Working with the OleDbConnection object is similar to using the SqlConnection object. However, because the OleDb .NET data provider can be used to connect to several types of databases, you must specify a provider name in the connection string. These provider names will be the same ones that were used with earlier versions of ADO. Here is an example of a connection string for a Microsoft Access database:

Dim myConn As OleDbConnection = New _    OleDbConnection() myConn.ConnectionString = _    "Provider=Microsoft.Jet.OLEDB.4.0; Data " & _    "Source=C:\data\northwind.mdb; User ID=guest; " & _    "Password=p5n7u!N" 

While this example provides a valid connection string, putting user names and passwords directly into your source code can provide problems both with security and maintenance. In the next section we will talk about other strategies for storing this sensitive information.

Protecting Usernames and Passwords

Exposing username and password information in your connection string code is one of the greatest database security vulnerabilities. Anyone with access to your source code can take this information and use it to access the database via their own programs, perhaps getting to data that they should not be able to see or modify.

A better option is to use Windows Integrated Security. This is a more secure method and is considered a security “best practice” when your application is running in an environment enabling you to take advantage of it—that is, when all users running your application are connected to the same local network. A connection string that specifies Windows Integrated Security would look like this:

myConn.ConnectionString = _    "Data Source=localhost; Initial " & _    "Catalog=pubs; Integrated Security=SSPI; "

Windows Integrated Security also provides benefits in terms of ongoing security maintenance. A Windows group can be created specifically for users who are authorized to run the application (and to see any sensitive data that the application might be processing). Network administrators are responsible for adding new authorized users and removing those who no longer are allowed access. The SQL Server administrator can simply add the group to the list of authorized users in the application database and set the appropriate permissions.

If users of web applications are connecting to your server through the public Internet, you will have to prompt them for username and password information when they connect to your site. You can verify their credentials in a variety of ways (see Chapter 9, “Overview of Security Concepts,” for more information on security considerations). After you have established that they are valid users of your service, you can have the application connect to the database by using a designated Windows login and password for the application.

Real World Scenario—Security Considerations—Blank Passwords and SQL Injection Attacks

In the discussion of databases in general and Microsoft SQL Server in particular, there are two common security risks that you should be aware of.

The first is that SQL Server is often installed with default settings. It is not at all uncommon to find servers that allow applications to connect with a login name of sa (system administrator) and a blank password. Any client program that can access your SQL Server database, including those run on unknown hosts around the Internet if your server is Internet-accessible, can access the database if they know the login name and password used.

The second security-related problem is that developers often accept user input and then pass that input string directly into a SQL query, without performing any checks for validity. Some developers think that this doesn’t matter because the application is coded to access only certain data and run specific queries, so users won’t be able to do any harm. However, attackers have found a way to exploit this lack of security. Your code might be asking the user to supply something innocent such as a name to search for, but the attacker can send additional instructions along with the innocent data. For example, your code might accept user input and build a query something like this:

SELECT * FROM Customers WHERE LastName LIKE userinput 

This works fine for regular users who will enter only plausible data. But it leaves an open opportunity for the attacker who will try to inject additional SQL instructions along with the simple data. An attacker might try to send something like this as an input string:

Smith; DROP TABLE importantTable 

Your innocuous query will execute, finding matching customer names, but the semicolon character indicates to SQL Server that a second command is to be performed—and the attacker has sent along an additional, destructive command. If the connection is made under a highly privileged account, such as sa, the attacker could be successful in destroying valuable data.

Another SQL injection approach is for the attacker to add instructions to set their own username, password, and permissions, so they can access your complete database later on, at their convenience.

It’s the combination of leaving defaults in place, running code under highly privileged accounts, and not checking user input that makes you vulnerable to this type of attack.

Using Connection Pooling to Optimize Performance

Connection pooling is a mechanism that maintains a group of already initialized connections to the database. When a user requests a connection, an existing one in the pool can be made available more quickly than if it were being initialized at the user’s request. When the user releases the connection, it can be returned to the pool and recycled for the next user.

One disadvantage of Integrated Security is that each connection to the database is made under an individual username. This defeats the connection pooling mechanism of the .NET data providers. If your application needs to take advantage of the performance enhancement of connection pooling, every connection to the database must use exactly the same connection string. This requires a model in which individual users are authorized by the application as necessary, but a single username and password for the application are used in the connection string for every access to the database.

You can also make settings such as minimum and maximum pool size and connection lifetime. Use these settings to optimize performance. If you don’t maintain enough connections in the pool, users will have to wait for a connection to be created or to become available. If the connection string’s Connect Timeout period expires before a connection is available, an error occurs. If you create too many connections, you will be using memory unnecessarily. Some additional items that can be added to the connection string to control connection pooling behavior are listed in Table 5.1.

Table 5.1: Additional Connection String Properties to Control Connection Pooling

Property	Descriptions
Connection Lifetime	Determines how long a connection will be maintained in the pool. A value of zero (0), the default, will cause pooled connections to have the maximum time-out.
Connection Reset	Determines whether the database connection is reset when being removed from the pool. If the connection is not reset, the next user might inherit some properties that were set by the previous user. The default is True.
Enlist	Determines whether the connection will be enlisted in the current transaction. The default is True.
Max Pool Size	Determines the maximum number of connections allowed in the pool. The default is 100.
Min Pool Size	Determines the minimum number of connections maintained in the pool. The default is 0.
Pooling	Determines whether pooling is enabled. The default is True.

Storing Connection String Information

Connection strings are considered sensitive data because they contain server names (or worse IP addresses!), database names, usernames, and passwords. Because of security concerns, this information must be in a secure location where those who might try to break into your database cannot read it.

Connection strings also require ongoing maintenance because over time, and in different installations, this information might need to be changed. Because of the ongoing maintenance requirements, it is preferable to store the information outside of compiled code, in a location where the application can read it at runtime. This is usually accomplished by putting the information into an application’s configuration file. Information specific to configuring various types of components can be found in Chapter 10, “Deploying, Securing, and Configuring Windows-based Applications,” and Chapter 11, “Deploying, Securing, and Configuring XML Web Services.”

Closing Connections

When working with data providers, it is important to make sure that you explicitly call the Connection object’s Close or Dispose method when you have completed your work with the database. Ideally, you will open and close a connection within the scope of one method call. Doing this releases the user’s connection to the database (which in some cases might be limited to a specific number of concurrent users due to licensing) and enables other users to access this resource.