Home > Data Access in Web Forms > Connecting to a Database > Securing Connection Strings

Connecting to a Database   Issuing Commands to a Database

---

Exposing connection strings on the page can leave the application open to security vulnerabilities and maintenance problems.

Unencrypted connection strings compiled into an application’s source code can be viewed using the Ildasm.exe (IL Disassembler) tool.

Moreover, if the connection string ever changes, your application must be recompiled.

For these reasons, the recommended way is to centralize the location of connection strings by storing them in the application’s web.config file, whether you are working with ADO.NET classes or data source controls.

Doing so has the following benefits:

• simplifies the management of connection strings by making them available to all pages in a Web application.
• no need to modify numerous individual pages if your connection string information changes.
• can improve the security of sensitive information stored in a connection string, such as the database name, user name, password, and so on, by encrypting the connection string section of the web.config file using protected configuration.

The following describes how to store connection strings in the connectionStrings configuration section in the web.config file.

1. Open the web.config file for your application. If a web.config file does not already exist, create a text file named web.config and add the following content:

   <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
   
   </configuration>

2. Within the <configuration> tags, create a new element named connectionStrings, as shown in the following example:

   <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
   
      <connectionStrings>
   
      </connectionStrings>
   
   </configuration>

3. Within the <connectionStrings> tag, create an add element for each connection string you will use in your Web application. Include the attributes shown in the following table.

Attribute	Description
name	A name for this connection string configuration object. This name will be used by data source controls and other features to reference the connection string information.
connectionString	The connection string to the data source.
providerName	The namespace of the NET Framework data provider to use for this connection, such as System.Data.SqlClient, System.Data.OleDb or System.Data.Odbc.

A completed connectionStrings element in a web.config file might look like the following example:

<connectionStrings>
   <add name="northWind" 
      connectionString="Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;" 
      providerName="System.Data.SqlClient" />
</connectionStrings>

4. Save and close the web.config file.

You can now reference the connection string by referring to the name you specified for the name attribute.

In the ConnectionString attribute for the SqlConnection, use the following expression to reference the connection information from the web.config file.

SqlConnection myConn = new SqlConnection ( 
   ConfigurationManager.ConnectionStrings [ name ].ToString ( ) );

where name is the named connection in the configuration file.

The following examples demonstrate using an SqlConnection in which the connection string is read from the web.config file. The first example simply displays the connection properties. For security purposes, though, the information in the ConnectionString and DataSource properties had been hidden.

Run Sample | View Source

Run Sample | View Source

In the ConnectionString attribute for your data source control, use the following expression to reference the connection information from the web.config file.

connectionstring="<%$ ConnectionStrings: name %>"

where name is the named connection in the configuration file.

The below code snippet demonstrates using an SqlDataSource control that connects to an SQL Server™ database, in which the connection string is read from the web.config file.

<asp:sqldatasource id="authors" runat="server"
   selectcommand="SELECT au_id, au_lname, au_fname, Address, City FROM Authors ORDER BY au_lname" 
   connectionstring="<%$ ConnectionStrings:aspnet %>" />

Run Sample | View Source

NOTE: This method applies only to SqlDataSource Controls. The AccessDataSource ConnectionString property cannot be set, it is automatically generated.

The AccessDataSource does not support connecting to Access databases that are protected by a user name or password, because these parameters are passed on from the ConnectionString.

In these cases, the SqlDataSource control should be used, as this control provides for specifying parameters in the connection string.

The below code demonstrates how to use an SqlDataSource control to connect to an MS Access database, in which the connection string is read from the web.config file.

<asp:sqldatasource id="products" runat="server" 
   connectionstring="<%$ ConnectionStrings: tutor %>"
   providername="System.Data.OleDb"
   selectcommand="SELECT * FROM Products" />

Having established a connection, you next need to be able to carry out commands against the database.

Connecting to a Database   Issuing Commands to a Database