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.
There are several alternatives for storing connection strings, including hard-coding the connection string in your application, storing it in an application configuration file or the Windows Registry, representing it using a Universal Data Link (UDL) file, or in a custom file.
A connection string is made up of a semi-colon 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.
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 two 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.
Often, it is not practical to prompt for connection credentials because of disadvantages including:
- Security
Transferring connection information from the browser to the server can expose connection credentials if they are not encrypted.
- Connection pooling
Each user must be recognized separately by the server. This does not allow effective connection pooling and can limit the scalability of the application. For more on connection pooling, see Recipe 1.15.
- 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 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.
Note
Always configure predetermined accounts with the minimum permissions required.
Never use sa or any other administrative account.
Never use blank passwords.
An obvious technique for storing connection strings is hardcoding them into the application. Although this approach results in the best 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.
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
.configextension—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.
Note
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 defined in an
<appSettings> element 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 <appSettings> element of the application
file is used to store custom application settings as a collection of
key-value pairs. You can store a connection string as shown:
<configuration>
<appSettings>
<add key="ConnectionString"
value="Data Source=(local);Initial Catalog=Northwind;User ID=sa;password=;"
/>
</appSettings>
</configuration>The AppSettings property of the
System.Configuration.ConfigurationSettings class
is used to retrieve the value for a specific key within the
appSettings element; the
ConfigurationSettings 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 file system. 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 5.7 shows techniques to encrypt data.
Note
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.config within 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.
The OLE DB .NET data providers supports 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.
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.
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.
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.
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
