Managing Connection Strings for Web Farms in ASP.NET 2.0

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.

 Collapse | Copy Code

<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 Labelcontrol named "Label1" and an ASP Button control named "Button1" on the page.

 Collapse | Copy Code

...
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:

 Collapse | Copy Code

<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 oldSystem.Configuration.AppSettings object has been deprecated in favor of the newSystem.Configuration.ConfigurationManager. In the case of connection strings, we access theConnectionStrings collection to obtain a ConnectionStringSettings object.

 Collapse | Copy Code

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:
    Collapse | Copy Code

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

2. Per MSDN instructions, type the following command:
    Collapse | Copy Code

   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:

 Collapse | Copy Code

<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 yourweb.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:

 Collapse | Copy Code

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:

 Collapse | Copy Code

<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 xmlnsattribute as below.

 Collapse | Copy Code

<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 calledconnectionTestKey.

 Collapse | Copy Code

> aspnet_regiis -pc "connectionTestKey" -exp

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

 Collapse | Copy Code

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

 Collapse | Copy Code

> 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 section of your web.config file

 Collapse | Copy Code

> 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

 Collapse | Copy Code

> 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:

 Collapse | Copy Code

<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

 Collapse | Copy Code

> 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:

 Collapse | Copy Code

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:

 Collapse | Copy Code

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 newSystem.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 theaspnet_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).

Read More »

Using COALESEC Function in SQL SERVER

COLAESEC() Function

When developing queries in SQL Server, dealing with nulls can often be challenging. The COALESCE function was included in SQL 2005 to help deal with these issues. First let’s take a look at what COALESCE can offer. Generally, COALESCE function receives a sequence of values and a default value to used when all of items in the sequence of values are null. From here the function returns the first not-null in the sequence of values list.

Scenario

There is a requirement of showing a user’s full name and how much money they get paid per week. This scenario will be divided up into two segments, displaying the user’s full name and then computing and showing their weekly earnings. Please feel free to use the attached database to follow along.

Implementation

In this first example, suppose there is a table of users that have the columns FirstName, MiddleName and LastName. The table holds the following values:

In many applications, the requirement of welcoming the user is often needed. So, to put the user’s full name on the screen, a stored procedure using the COALESC function properly format all three fields into one field. 

So the query using COALESCE looks like this:

SELECT (FirstName + ' ' + COALESCE(MiddleName,'') + ' ' +
COALESCE(LastName,'')) AS FullName
FROM Users

The results will look like this:

 

Thanks and happy coding!

Read More »

GLOBAL SEARCH IN SQL SERVER

--EXEC SearchAllTables 'A38010088'
--GO

Here is the complete stored procedure code:

CREATE PROC SearchAllTables
(
    @SearchStr nvarchar(100)
)
AS
BEGIN

    -- Copyright Ã�© 2002 Narayana Vyas Kondreddi. All rights reserved.
    -- Purpose: To search all columns of all tables for a given search string
    -- Written by: Narayana Vyas Kondreddi
    -- Site: http://vyaskn.tripod.com
    -- Tested on: SQL Server 7.0 and SQL Server 2000
    -- Date modified: 28th July 2002 22:50 GMT


    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL
    BEGIN
        SET @ColumnName = ''
        SET @TableName =
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
   
            IF @ColumnName IS NOT NULL
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END   
    END

    SELECT ColumnName, ColumnValue FROM #Results
END

Read More »

UPDATE QUERY USING CASE STATEMENT IN SQL SERVER

One of the keys to SQL Server database performance if keeping your transactions as short as possible. In this article we will look at a couple of tricks using the CASE statement to perform multiple updates on a table in a single operation. By doing this, some transactions can be shorted, and performance boosted.

--First Create a Table
CREATE TABLE [dbo].[UsingCaseInUpdateQuery](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NOT NULL,
[gender] [varchar](50) NOT NULL,
[address] [varchar](100) NULL,)

--Insert Data in to the Table
insert into [UsingCaseInUpdateQuery] (name,gender,address) values('yogesh','male','almora')
insert into [UsingCaseInUpdateQuery] (name,gender,address) values('yogesh1','male','almora')
insert into [UsingCaseInUpdateQuery] (name,gender,address) values('yogesh2','male','ranikhet')
insert into [UsingCaseInUpdateQuery] (name,gender,address) values('yogesh3','male','nainital')

insert into [UsingCaseInUpdateQuery] (name,gender,address) values('naina','female','almora')
insert into [UsingCaseInUpdateQuery] (name,gender,address) values('pooja','female','ranikhet')
insert into [UsingCaseInUpdateQuery] (name,gender,address) values('ritu','female','nainital')

--Once Check the data in the table
select * from [UsingCaseInUpdateQuery]
 

USING CASE STATEMENT IN A SQL UPDATE QUERY
--Now Update all the Male Gender with Female and Female with Male
Update Query Using Case for the fast execution of data
update [UsingCaseInUpdateQuery] set gender = case
when gender ='male' then 'female'
when gender = 'female' then 'male'
end

*Note that there is a definite “top-down” priority involved in the CASE statement.

Multiple Updates to a Single column:

If we code our update like the example below, then the table will only need to be read once. For large tables, this can save us a lot of disk I/O, especially if the query requires a table scan over a long table

Update [UsingCaseInUpdateQuery]
Set address =
Case
    When gender = 'female'
    Then 'Almora'
    When id = '5'
    Then 'Delhi'
End
Where id = '5' OR gender = 'female'

Continues…

Read More »