ASP.NET: Connection Strings

By Steven Smith from aspalliance.com


In ASP.NET there are several options for storing connection strings. These include the Web.config file, Application variables, custom text files, and hardcoded into pages or components. In Classic ASP, it was common practice to use the Application object to store the connection string information for an application, so it was common to find code like this in the global.asa file:

Sub Application_OnStart
        Application("Conn1_ConnectionString") = _
        "Provider=SQLOLEDB.1;UID=sa;PWD=p;Initial Catalog=src;Data Source=localhost;"
End Sub

This was the "best practice" approach for many websites. Another option that some sites, such as ASPAlliance.com, used was to have these settings in a common text file that was then called with Server.Execute, such as this:
Sub Application_OnStart
        Server.Execute "/db/config.asp"
End Sub

This would allow many different applications (like all of the individual columnists on ASPAlliance.com) to have their connection information stored in one place. ASPAlliance also used a COM object's property for this same purpose for a while. The question is, what's the best way to store this information in ASP.NET?

Well, there is no single right answer for this question, but depending on your situation, there is usually a method that is best. Before we consider the different possibilities, let's consider one option that should probably be eliminated right away: Application variables.

Don't Use Application Variables for Sensitive Information in ASP.NET
Although this worked wonderfully well in ASP, there is one thing new about ASP.NET that makes using the Application (or Session) object for storing database connection information unwise: Tracing. In a typical ASP application, it was fairly rare to find a page that would dump out the contents of the Application collection. If such a page existed, usually only the developer of the site knew about it. So having sensitive information in the Application object wasn't terribly insecure, as long as you were careful not to give the users any easy way to get a look at this collection of values. However, in ASP.NET, a trace dump will always show the contents of the Application collection. And anybody who knows anything about ASP.NET will know how to hit the Trace.axd file to see recent Trace dumps. Now, you say, "But you can configure tracing in the Web.config file, and I'll just turn it off there and be secure." Certainly, you should do that. However, don't forget that individual pages can have tracing enabled as well, and there is no global way to turn this off. And this is a GREAT debugging tool, so it's probably going to be used quite a bit by developers. That said, how long do you think it will be before a page slips by and is published with tracing still turned on? As soon as it does, your connection information will be publicized to the world if it is stored in your Application (or Session) object. I strongly believe that this will happen with relative frequency, and that for this reason alone it is a BAD IDEA to store sensitive data in the Application (or Session) object in ASP.NET.

Ok, so where DO I store this stuff?
Well, let's look at the most common situation -- the single web site. If you're building a single web site, and it has a single database supporting it, then your best bet is to follow the examples in place at IBuySpy.com and store your connection information in your Web.config file. It is then very easy to retrieve this information from any of your ASP.NET pages, using the following syntax:

        'VB
        Dim connstring as string
        connstring = ConfigurationSettings.AppSettings("ConnectionString")
       
        //C#
        string connstring = ConfigurationSettings.AppSettings["ConnectionString"];

To add support for this to your Web.Config, you need to add an AppSettings section, like this one:
<appSettings>
        <add key="ConnectionString" value="(your connection string)" />
</appSettings>

which should go right after the "configuration" section and before the "system.web" section. These settings can also be read from your custom data access components, as the IBuySpy examples also demonstrate. This is the recommended method for storing database connection information for small applications.

For larger appliations, such as the ASPAlliance.com website, which has dozens of different web applications all relying on the same database, there are several options. Since all of the columnist websites are subwebs of the root site, the easiest thing to do is to store the global config information in the root web's Web.config file. This is what I've done for ASPAlliance.com. For a server that has many different sites all using the same database (i.e. different IP addresses and domain names, not subwebs), the best solution if you want to keep all of the connection information in one place is to use the machine.config file for the server. There is an appSettings section already in the machine.config file that serves this purpose. Another option is to build a shared control, and have all of the sites use this, but this is less intuitive to use than the web.config method, and would require some instruction to new columnists whenever they needed to connect. Whenever possible, the web.config file should be used for storing sensitive application information, and remember that all sites inherit from the machine.config, and subwebs inherit from parent webs, so store your connection info as high up in the tree hierarchy as necessary to allow all the sites that need it to access it.