ADO.NET 3.5 Cookbook, 2nd Edition

1.1. Storing Connection Strings

Problem

You need to choose the best place to store connection strings that you need in your application to increase maintainability, simplify future modifications, and eliminate the need to recompile the application when it is modified.

Solution

Solutions include storing the connection string in an application configuration file or the Windows registry, hardcoding the connection string in your application, representing it using a Universal Data Link (UDL) file, and storing it in a custom file. Some of these techniques are better than others, and some are completely inadvisable. This solution explores and discusses the alternatives.

The .NET Framework 2.0 introduced protected configuration as a mechanism for encrypting stored connection strings used by ASP.NET applications. It also introduced the connectionStrings configuration element to specify a collection of database strings. In prior versions, connection strings were stored in the appSettings element.

Discussion

A connection string is made up of a semicolon delimited collection of attribute/value pairs that define how to connect a data source. Although connection strings tend to look similar, the available and required attributes are different depending on the data provider and on the underlying data source. There are a variety of options providing differing degrees of flexibility and security.

ora: Persist Security Info

The Persist Security Info connection string attribute specifies whether the data source can hang on to, or persist, sensitive information such as user authentication credentials. Its value should be kept at the default false. If its value is true, the connection information—including the password—can be obtained by querying the connection, allowing an untrusted party to have access to sensitive information when a Connection is passed or persisted to a disk. This is an issue only when passing connected objects such as Connection or DataAdapter; disconnected objects such as DataSet and DataTable do not store information about the original source of their data.

Before a data source object is initialized for the first time, sensitive information can be retrieved from it regardless of the setting of the Persist Security Info property. Avoid passing uninitialized data source objects.

The Persist Security Info connection string attribute is supported by the SQL Server, OLE DB, and Oracle .NET Framework data providers. Although not supported by the ODBC .NET Framework data provider, its behavior is as if Persist Security Info is false and cannot be changed. Check the documentation for other data providers to determine specific implementation details.

Connecting to a database server requires passing credentials—username and password—to the server in a connection string. These credentials, together with the data source name, need to be kept private to protect unauthorized access to the data source. There are three approaches for obtaining these credentials:

• Prompting for connection credentials at runtime

• Storing predetermined connection credentials on the server and using them at runtime to connect to the database server

• Using integrated security, which passes current credentials to the server

ora: Integrated Security

Integrated security is the most secure way to connect to a SQL Server and should be used unless it is impractical to do so. Integrated security uses the identity of executing code rather than an explicit user ID and password in the connection string to authorize access to the database. Integrated security avoids storing usernames and passwords in connection strings and its use is recommended where possible instead of SQL Server Authentication.

To use integrated security in the connection string, specify the value SSPI or true for the Integrated Security attribute and do not specify User ID and Password connection string attributes:

	Integrated Security=SSPI

See Recipe 1.6 for information about connecting to SQL Server using integrated security from ASP.NET.

Additionally, the web site http://www.connectionstrings.comshows you how to construct connection strings for a wide variety of database servers and other data sources.

Often, it is not practical to prompt for connection credentials because of disadvantages including:

Security

Transferring connection information from the client to the server can expose connection credentials if they are not encrypted.

Connection pooling

The server must recognize each user separately. This results in different connection strings for each user and prevents using connection pooling, which in turn limits application scalability. For more on connection pooling, see Recipe 1.17.

Single sign-on

It is difficult to integrate with single sign-on strategies, which are becoming increasingly important in enterprise environments (for example, where numerous applications are aggregated into portals).

Server-based applications

Cannot be used by applications that otherwise have no user interface, such as an XML web service.

There are a number of techniques that you can use to store predetermined connection credentials. These, together with their advantages and drawbacks, are discussed in the following subsections.

Tip

Here are a few tips:

• Always configure predetermined accounts with the minimum permissions required.

• Use integrated security whenever possible.

• Always encrypt stored credentials and carefully control access to the associated encryption keys.

• Never use sa or any other administrative account.

• Never use blank or weak passwords.

Application configuration file

An application configuration file is an XML-based text file that is used to store application-specific settings used at runtime by the application. The naming convention for and deployment location of the file depend on the type of application:

Executable application

The name of the configuration file is the name of the application executable with a .config extension—for example, myApplication.exe.config. It is located in the same directory as the executable file.

ASP.NET application

A web application can have multiple configuration files all named web.config. Each configuration file supplies configuration settings for its directory and all of its child directories; it also overrides any configuration settings inherited from parent directories.

Tip

The machine configuration file—machine.config, located in the CONFIG subdirectory of the .NET runtime installation—contains configuration information that applies to the computer. The machine.config file is checked for configuration settings before the application configuration file is checked.

It is best to put application settings in the application configuration file both to facilitate deployment and to keep the machine configuration file manageable and secure.

The <connectionStrings> element of the application configuration file is used to store a collection of connection strings as name/value pairs. You can store a connection string as shown:

	<configuration>
	    <connectionStrings>
	        <add key="ConnectionString"
	value="Data Source=(local);Initial Catalog=AdventureWorks;
	User ID=sa;password=;"
	        />
	    </connectionStrings>
	</configuration>

The ConnectionStrings property of the System.Configuration class is used to retrieve the value for a specific key within the connectionStrings element; the System.Configuration class cannot be used to write settings to a configuration file.

Application configuration files facilitate deployment because the files are simply installed alongside other application files. One drawback is that application configuration files are not inherently secure since they store information as clear text in a file that is accessible through the filesystem. Encrypt the connection and other sensitive information within the configuration file and ensure that NTFS file permissions are set to restrict access to the file. Recipe 6.11 shows techniques to encrypt data.

Tip

Make sure you name the application configuration file for a Windows Forms application App.config—this is the default. At build time, this file is automatically copied into the startup directory by Visual Studio .NET with the name applicationName.exe.config.

If you name the application configuration file applicationName.exe.configwithin your solution, you will have to copy it to the startup directory each time you modify it and each time you build the solution; the build process deletes it from the startup directory.

1. Create a new C# console application named StoredConnectionStringConfig.

2. In the Solution Explorer pane, right-click the project and select Add → New Item from the context menu to open the Add New Item dialog. Select the Application Configuration File template, accept the default name App.config, and click the Add button to add the configuration file.

3. Add a SQL Server connection string within a connectionStrings element in the file App.config as shown in Example 1-1.

   Example 1-1. File: App.config

   <?xml version="1.0" encoding="utf-8" ?>
   <configuration>
       <connectionStrings>
           <add name="AdventureWorks"
            providerName="System.Data.SqlClient"
            connectionString="Data Source=(local);
             Integrated security=SSPI;Initial Catalog=AdventureWorks;" />
       </connectionStrings>
   </configuration>

4. Add a reference to the System.Configuration assembly.

The C# code in Program.cs in the project StoreConnectionStringConfig is shown in Example 1-2.

Example 1-2. File: Program.cs for StoreConnectionStringConfig solution

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace StoreConnectionStringConfig
{
    class Program
    {
        static void Main(string[] args)
        {
            // Enumerate connection strings
            Console.WriteLine("---Connection string enumeration---");
            foreach (ConnectionStringSettings css in
                ConfigurationManager.ConnectionStrings)
            {
                Console.WriteLine(css.Name);
                Console.WriteLine(css.ProviderName);
                Console.WriteLine(css.ConnectionString);
            }

            // Retrieve a connection string and open/close it
            Console.WriteLine("\n---Using a connection string---");
            Console.WriteLine("-> Retrieving connection string AdventureWorks");
            string sqlConnectString =
                ConfigurationManager.ConnectionStrings[
                "AdventureWorks"].ConnectionString;
            SqlConnection connection = new SqlConnection(sqlConnectString);
            Console.WriteLine("-> Opening connection string.");
            connection.Open(  );
            Console.WriteLine("Connection string state = {0}", connection.State);
            connection.Close(  );
            Console.WriteLine("-> Closing connection string.");
            Console.WriteLine("Connection string state = {0}", connection.State);

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey(  );
        }
    }
}

The output is shown in Figure 1-1.

Protected configuration

You can use protected configuration to encrypt sensitive information, including database connection strings in a web application configuration file. Values are saved in encrypted form rather than as clear text. The .NET Framework decrypts the information when the configuration file is processed and makes it available to your application. Protected configuration requires IIS 6.0 or later. For more information about using protected configuration, search for "Protected Configuration" in MSDN.

Figure 1-1. Output for StoreConnectionStringConfig solution

Hardcode in the application

An obvious, but poor, technique for storing connection strings is hardcoding them into the application. Although this approach results in the good performance, it has poor flexibility; the application needs to be recompiled if the connection string needs to be changed for any reason. Security is poor. The code can be disassembled to expose connection string information. Caching techniques together with external storage techniques eliminate nearly all performance benefits of hardcoding over external storage techniques.

Hardcoding connection string information is not advised; external server-side storage is preferred in nearly all cases because of the increased flexibility, security, and configuration ease. A discussion of available external storage options follows.

Universal data link (UDL) file

The OLE DB .NET data providers support UDL filenames in its connection string. The UDL file is a resource external to the application that encapsulates connection properties in a separate file. It must be protected using NTFS security to prevent connection information from being exposed or altered. The SQL Server .NET data provider does not support UDL files in its connection string. UDL files are not encrypted; cryptography cannot be used to increase security. NTFS directory and file encryption can secure a UDL file so that even if unauthorized access is gained to the file or the physical disk is stolen, the user ID and password of the user who encrypted the file would still be required to access its contents.

Windows registry

You can store connection strings in the Windows registry as a subkey of HKEY_LOCAL_MACHINE\SOFTWARE. You can encrypt these settings within the registry subkey and restrict access to the subkey to increase the security of this technique. This technique is easy to use because of programmatic support for registry access in .NET classes Registry and RegistryKey in the Microsoft.Win32 namespace.

ora: NTFS Encryption

NTFS was enhanced in Windows 2000 with the Encrypted File System (EFS) that provides file-and directory-level encryption. Actually, EFS encrypts only files—directories are simply marked so that new files in the directory are encrypted. Encryption and decryption of files is both automatic and transparent for the user who set the encryption.

Encrypted files are visible to any user who can access the system but the contents of the encrypted files can only be viewed by the user who set the encryption. If necessary, standard NT security methods can hide directories and files from the view of specific users and user groups.

EFS is a separate mechanism that is used together with the standard security subsystem.

Storing connection strings in the registry is usually discouraged because of deployment issues; the registry settings must be deployed with the application, defeating benefits of xcopy deployment. Application code can also be restricted in its access to the registry, further complicating deployment.

Custom file

A custom file is any file that is used to for proprietary storage of application settings that are typically used at runtime. There is generally no particular advantage to using a custom file to store connection information so the technique is not recommended. The approach requires extra coding and forces concurrency and other issues to be explicitly addressed.