Connection String encryption (connectionString element) in ASP.NET

From what I've been able to determine, setting up an ODBC connection in Windows and using that for ASP.NET generally seems to be frowned upon. Instead, ASP.NET uses a Web.config file to store a number of settings, including all the connection strings you'll be using (whether you're using Windows authentication or user names and passwords).

For example:

<?xml version="1.0"?>
<configuration>
    <appSettings/>
    <connectionStrings>
        <add name="TestDatabase001Reader" connectionString="Data Source=192.168.56.102,1433;Initial Catalog=TestingDatabase001;User Id=DataReader;Password=DataReader" providerName="System.Data.SqlClient"/>
    </connectionStrings>
    <system.web>
        ...
    </system.web>
</configuration>

However, it's also possible to store the connection strings in a completely different file by tweaking the connectionString in Web.config like so:

<connectionStrings configSource="ConnectionStrings.config">
</connectionStrings>

Then ConnectionStrings.config would be created and consist of the following:

<?xml version="1.0"?>
<connectionStrings>
    <add name="TestDatabase001Reader" connectionString="Data Source=192.168.56.102,1433;Initial Catalog=TestingDatabase001;User Id=DataReader;Password=DataReader" providerName="System.Data.SqlClient"/>
</connectionStrings>

The problem arises when the Web.config file is compromised, either by pushing it without a .config extension, sending it to someone else, or someone having browse access your Web site's directory. How do you encrypt these connection strings, and can you do so if you decide to store them in a separate file?

The following is how to verify that either way works just fine.

SqlConnectionEncryption.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SqlConnectionEncryption.aspx.cs" Inherits="WebApplication1.SqlConnectionEncryption" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>SQL Connection Encryption example</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
        <div id="Options" runat="server"></div>
    </div>
    </form>
</body>
</html>

SqlConnectionEncryption.aspx.cs

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Web.Configuration;

namespace WebApplication1 {
    public partial class SqlConnectionEncryption : System.Web.UI.Page {
        protected void Page_Load(object sender, EventArgs e) {

            string action = Request.QueryString["action"];

            Options.InnerHtml = "";

            DataTable results = new DataTable();

            using (SqlConnection connection = new SqlConnection()) {
                connection.ConnectionString = ConfigurationManager.ConnectionStrings["TestDatabase001Reader"].ToString();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "SELECT * FROM NameTable";
                command.CommandType = CommandType.Text;

                connection.Open();
                results.Load(command.ExecuteReader());
                connection.Close();
            }

            if (results.Rows.Count > 0) {
                GridView1.DataSource = results;
                GridView1.DataBind();
            }

            Configuration config = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath);
            ConfigurationSection configSection = config.GetSection("connectionStrings");

            if (!configSection.SectionInformation.IsProtected) {
                configSection.SectionInformation.ProtectSection("RsaProtectedConfigurationProvider");
                config.Save();
                Options.InnerHtml += "Configuration encrypted.<br />";
            } else {
                //configSection.SectionInformation.UnprotectSection();
                //config.Save();
                //Options.InnerHtml += "Configuration unencrypted.<br />";
                Options.InnerHtml += "Configuration already encrypted.<br />";
            }

            Options.InnerHtml += "Web configuration path: " + config.FilePath + "<br />";
            Options.InnerHtml += "Section path: " + configSection.ElementInformation.Source + "<br />";
            Options.InnerHtml += "<br />";
        }
    }
}

Depending upon your Web.config setup, you'll either see Web.config for both, or Web.config for one and ConnectionStrings.config for the other.