Managing Connection Strings for Web Farms in ASP.NET 2.0

• Download source - 3.18 Kb

Introduction

What do I do with my connection strings? How do I encrypt them to protect my logins? Where should I store them? How can I share them across servers? These are questions that have plagued developers for ages. With .NET 1.0/1.1, a consensus has formed on storing this information in the web.config file. Encryption is fairly simple using the framework encryption libraries and letting Windows internally manage the encryption key.

But Web Farms are more problematic. The encryption algorithm is often shared with other encryption needs (e.g., user passwords), so all machines must share a common encryption key. Key management then becomes a problem, because if someone finds your key, there are only a handful of algorithms to test before your data is compromised. Microsoft recommended storing the key or the connection string in a protected registry key, but in practice I found this to be cumbersome because it required execution of your ASP.NET application as a specially privileged user. Storing the key in the web.config file is easier, but then you had the encrypted data and the decryption key living in the same file - bad idea. Machine config is a better choice, but occasionally causes problems with other applications on the same box, like SharePoint. So there are solutions but no perfect answers.

Simple Example: Unencrypted Connection Strings in Web.Config

We'll start with some simple test code to check our connection. It is the computer classic, Hello World, using the database to say hello.

Create a new website using IIS to host:

1. File > New > Website >
2. In dialog, choose the following settings:
   • Template = ASP.NET Web Site
   • Location dropdown = HTTP
   • URL = http://localhost/connectionTest

Next, add the code listed below. The connection string assumes SQL Server 2005 to be installed as a named instance on myserver. For SQLExpress, the server will look like "localhost\SQLExpress".

Test Code Listings

Here is some simple code written as it would be for .NET 1.1. We'll use this as a starting point.

Web.config

Here, we define our connection string. You can see I added a SQL user for AdventureWorks named test with the password test.

<configuration>
    <appSettings>
        <add key="myconnection" 
           value="server=myserver\sql2005;initial cata
                  log=AdventureWorks;user id=test;pwd=test;"/>
    </appSettings>
...
</configuration>

Default.aspx.cs

This is the snippet of code that opens a connection and has the database say hello. There is an ASP Label control named "Label1" and an ASP Button control named "Button1" on the page.

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


protected void Button1_Click(object sender, EventArgs e)
{
    string constr = ConfigurationSettings.AppSettings["myconnection"];
    SqlConnection sqlConn = new SqlConnection(constr);
    sqlConn.Open();
    SqlCommand cmd = sqlConn.CreateCommand();
    cmd.CommandText = "SELECT 'Hello World'";
    try
    {
        SqlDataReader reader = cmd.ExecuteReader();
        if (reader.Read())
        {
            this.Label1.Text = reader.GetString(0);
        }
        else
        {
            this.Label1.Text = "No Result";
        }
    }
    catch (Exception ex)
    {
        this.Label1.Text = "Error: " + ex.Message;
    }
    finally
    {
        if(sqlConn.State != ConnectionState.Closed)
            sqlConn.Close();
    }
}

Test Code Updated to ASP.NET 2.0

Web.config

What happened to my <appSettings> config section? It's still there and still usable, but Microsoft has decided to promote connection strings to their own section. The updated code looks like this:

<configuration>
    <appSettings/>
    <connectionStrings>
        <add name="myconnection" 
          connectionString="server=myserver\sql2005;initial 
                            catalog=AdventureWorks;
                            user id=test;pwd=test;"/>
    </connectionStrings>
</configuration>

Default.aspx.cs

The way configuration settings are accessed has changed slightly as well. The old System.Configuration.AppSettings object has been deprecated in favor of the new System.Configuration.ConfigurationManager. In the case of connection strings, we access the ConnectionStrings collection to obtain a ConnectionStringSettings object.

protected void Button1_Click(object sender, EventArgs e)
{
    ConnectionStringSettings conn = 
      System.Configuration.ConfigurationManager.
             ConnectionStrings["myconnection"];
    string constr = conn.ConnectionString;
    ...
}

So that's the simple, unencrypted way to do connection strings. It would run just fine for web farms as is, but you have exposed SQL credentials which is not particularly safe. What about encrypting for web farms?

Help! My code isn't working!

If you get the following error:

System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings, SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

you may have to modify your SQL Server (2005) to allow external connections via the SQL Server Surface Area Configuration tool (under Start > Programs > Microsoft SQL Server 2005 > Configuration Tools). Either that or your connection string is incorrect.

Encrypting Connection Strings

Encryption of the connectionStrings section of the web.config is easy and fun! Just follow the few easy steps outlined in MSDN (click on the "Configuration" link). You utilize the aspnet_regiis utility to encrypt a section in the application's web.config (hence we use IIS to host our development site instead of the VS2005 built-in web server).

1. Open a command prompt in the .NET 2.0 framework directory. On most computers, this will be:

   C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

2. Per MSDN instructions, type the following command:

   aspnet_regiis.exe -pe "connectionStrings" -app "/connectionTest"

This uses the default RSAProtectedConfigurationProvider to encrypt the data. This is also the recommended provider to use when setting up a web farm. After this task has been performed, the <connectionStrings> section in your web.config will be rewritten to look something like this:

 <connectionStrings 
     configProtectionProvider="RsaProtectedConfigurationProvider">
  <EncryptedData Type="http://www.w3.org/2001/04/xmlenc#Element"
   xmlns="http://www.w3.org/2001/04/xmlenc#">
   <EncryptionMethod 
     Algorithm="http://www.w3.org/2001/04/xmlenc#tripledes-cbc" />
   <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
    <EncryptedKey xmlns="http://www.w3.org/2001/04/xmlenc#">
     <EncryptionMethod 
        Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-1_5" />
     <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
      <KeyName>Rsa Key</KeyName>
     </KeyInfo>
     <CipherData>
      <CipherValue>S9M1K/Dx9nX4nYJO6E7kz+ttLKFo+uvO+VsNwUFyQSEJnkEY
                   mjUUMr9JvjHoOaiFZv/fKz61BBP6kY6HcGDTxClXlCFGdWP5ElVlZ02
                   PfKrB2tIeQuFGieNGztBtiPHh7DxS4W2fnr0cyRoXi0WLT
                   6GHOfskptcV03SY3LrSVSk=</CipherValue>
     </CipherData>
    </EncryptedKey>
   </KeyInfo>
   <CipherData>
    <CipherValue>Wo48uaYxUROulI587vC4vgOk//6cJD
                 h70f3t8Izn53zQKJAilC/4HHHQREjLRFaClgj
                 SdjALGiCh/gb6ioAHdMYNP5NMaNARvtLGjLEbbm6
                 u09YSbvTFlSyucZJca/HZquib5FuT+slXUw+PijnlX
                 BHU/CtuMZ+90N32NxO5bSDjeN6yZQfa2H7wMXtb6uaBx
                 6pXl/v5OoF2pTiJjGMTMZwCm9+zRPMU6Y8ix+kntp
                 zHmb5SRKGDoQ==</CipherValue>
   </CipherData>
  </EncryptedData>
 </connectionStrings>

Now, everything is encrypted and protected and safe. Try to execute your page to prove it.

• Parser Error Message: Failed to decrypt using provider 'RsaProtectedConfigurationProvider'. Error message from the provider: The RSA key container could not be opened.

Whoops! Seems the MSDN article left out an important step. The application user needs to have access granted to the key container. What access? What key container? Well, let's find out.

Decrypting the Web.Config

First, let's get back to the point where we started. While it is a good idea to maintain backups of your web.config file before doing this encryption, it is reversible. Open your command prompt and go to the .NET Framework installation as before. Issue the following command to decrypt and restore your web.config to its previous state:

aspnet_regiis.exe -pd "connectionStrings" -app "/connectionTest"

The -pd is the decryption flag for the utility. Your config file should be back to its previous state.

Re-Encrypting your Web.Config Correctly for Web Farms

For encryption to work, the key must be accessible both during encryption and during decryption. Our prior example failed because it was not available during decryption. As it turns out, the simple examples out there of how to encrypt your connectionStrings are misleading at best. It is a three step process.

1. Add a <configProtectedData> section to your web.config to identify the encryption provider and your key container.
2. Create your own key container on the server.
3. Grant access to this key container to all interested accounts.
4. Encrypt the <connectionStrings> section of web.config.
5. Export the key container for use on other servers in the farm.

So let's do it, step by step.

Step 1: Modify web.config to identify the key container

Edit your web.config file to contain a provider definition that identifies which key container to use for encryption/decryption. Under the <configuration> section, add the following:

<configProtectedData >
    <providers>
        <add name="ConnectionTestProvider"
          type="System.Configuration.RsaProtectedConfigurationProvider, 
                System.Configuration, Version=2.0.0.0,
                Culture=neutral, processorArchitecture=MSIL"
                keyContainerName="connectionTestKey"/>
    </providers>

</configProtectedData>

You may also wish to modify the configuration tag to identify the proper XML namespace, to avoid XML validation errors. It's not necessary for compilation and execution, but it is annoying to have the dev environment tell you the attributes required are not valid. Modify the <configuration> tag to add the xmlns attribute as below.

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

Step 2: Create the key container on the server

Use aspnet_regiis at the command prompt to generate a new RSA cryptographic key container called connectionTestKey.

> aspnet_regiis -pc "connectionTestKey" -exp

This will create a new machine level key container in the following location:

C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys

You are highly advised to not manually manipulate these files (delete, change permissions, rename, etc.). I hosed my IIS installation doing this, and had to do a ground-up reinstall of IIS and both the 1.1 and 2.0 .NET Frameworks. My VS2005 installation is still not quite right as a result of this.

Step 3: Grant access to the key container

> aspnet_regiis -pa "connectionTestKey" "ASPNET"

This grants read access on the crypto key file to the indicated user; in this case, the ASPNET built-in account is granted access. Be warned: you may also have to grant read access on the directory C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys to the ASPNET account. I had endless problems with this and it is an undocumented "feature" of the security model.

Step 4: Encrypt the <connectionStrings> section of your web.config file

> aspnet_regiis -pe "connectionStrings" -app "/connectionTest"

This will encrypt and rewrite your web.config file. You are now good to go (on this server). If you execute the code, your "Hello World" app should work fine.

Step 5: Export the key container for later use on farm servers

> aspnet_regiis -px "connectionTestKey" \temp\mykeyfile.xml

This writes the key to an XML file for use on other machines in your farm. The contents of the key file will look something like this:

<RSAKeyValue>
   <Modulus>4E/ykYJHOR/kkLuxeG9pV68eelZo9xkxhX
         MgaUZN4LCqw8iCq8N7B1vYiackmagdvF4STN8OihNPSi1562/ZGNn/CDxkf
         PkpzIax0sbtxl8fcF030qpRhUrZ6IfZPsFW/czNd1Xpm0bw+pu6YowBTsE
         iLYGRe2IdKqgT1RTxBHE=</Modulus>
   <Exponent>AQAB</Exponent>
</RSAKeyValue>

Step 6: Import the key container on farm servers

> aspnet_regiis -pi "connectionTestKey" mykeyfile.xml

When you deploy your application, you will need to import the key as part of the installation process. Place the key file in an accessible location on the server and run the command above to import the key. You are now good to go!

Pitfalls to avoid

Most problems, predictably, revolve around proper configuration of access privileges to the key container. Make sure you add access rights for the account your application is running under, to both the key container and the folder holding it.

If problems occur, do not delete your key containers willy-nilly; use the aspnet_regiis utility. I did this (manually deleted the container) to remove a key container I had mugged up by removing everyone's privilege to. In the process, I deleted some other key containers that were important. Upon restarting IIS, I discovered I could not restart IIS. Instead, I got the following error for the IISADMIN service:

System error 8 - Not enough storage is available to process this command

This required me to uninstall and reinstall IIS to fix the problem. And both the .NET frameworks too. And it's still not fixed. I continue to have the following error:

Safe handle has been closed

when I try to do anything other than create keys. Comments/clues are appreciated. I just hope it doesn't require a full OS install to fix. I'm guessing that the IIS metabase also gets touched when doing things with crypto keys, but I'm still investigating the matter.

Conclusion

To summarize, utilize the new <connectionStrings> section in the web.config along with the new System.Configuration.ConfigurationManager.ConnectionStrings collection to obtain your connection string from the configuration file. Create a key container using the aspnet_regiis utility, and identify the proper container in you web.config by adding a <configProtectedData> section. Encrypt with the aspnet_regiis utility, and export your key for use on other servers in the web farm.

I'm sorry, why is this so complex again? While I laud Microsoft's recognition of the need for security and consistency when handling connection strings, this multi-step, thinly documented process makes me wonder a little about their sanity. In some ways, I appreciate the justification for people like me to continue to make the big bucks. In other ways, I wonder why a simple add-in or installer option wasn't written for VS2005 to handle web farm connection strings. It's almost like they hired some UNIX geek or a Java developer to make this crypto utility stuff work. I smell an opportunity for some intrepid developer.

Also, why was aspnet_regiis appropriated for so many non-ASP.NET-framework-registration-with-IIS functions? Instead of making a security key configuration utility, aspnet_regiis became a dumping ground for this functionality. A poorly thought out utility decision in an otherwise impressively designed framework and SDK (IMnsHO).

Resources Used in Writing this Article

• Creating and Exporting an RSA Key Container.
• How to use the ASP.NET utility to encrypt credentials and session state connection strings.
• Security Practices: ASP.NET 2.0 Security Practices at a Glance.
• Safeguard Database Connection Strings and Other Sensitive Settings in Your Code.
• Importing and Exporting Protected Configuration RSA Key Containers.
• ASP.NET Connection Strings.
• ASP.NET IIS Registration Tool (Aspnet_regiis.exe).