Read custom encrypted connection string from Web.config

Question

I have an existing ASP.NET project where the connection string is encrypted in the Web.config file by using a custom encryption function in C#, and saving that encrypted string to the Web.config file as follows...

<add name="ConnectionString" connectionString="+tj/H0V/Wpa+UBWzHvOfuL4GPyoDssypPKfeRdUU1FnfHw+phOEBLpRne/ytv1v8gs7P0DoSC7rhN2aPWG3uZZvSis5f/Dqu53HgsRH8m44=" providerName="System.Data.SqlClient" />

I am now wanting to add a Page to this project with a control requiring a SQLDataSource, and I have specified the ConnectionString in the .aspx code behind, as a property of the SQLDataSource as follows...

<asp:SqlDataSource ID="PeriodsDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

This Obviously is now trying to read the connectionString, but gets the Encrypted String, and throws the error...

"Keyword not supported: '+tj/h0v/wpa+ubwzhvoful4gpyodssyppkferduu1fnfhw+phoeblprne/ytv1v8gs7p0dosc7rhn2apwg3uzzvsis5f/dqu53hgsrh8m44'."

How do I get to Pass the ConnectionString to my custom Encryption.Decrypt function for the SQLDataSource to use the Un-Encrypted string?

I have tried simply adding the following to the Page_Load event, which helps with the initial load, but after postback I still get the error mentioned above. (No, this is not within a !IsPostBack)

string connectionString = Encryption.Decrypt(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString, "XXXXXX");
        PeriodsDataSource.ConnectionString = connectionString;

Answer 1

This is the solution that I have implemented.

The %$ characters for the ConnectionString in asp:SqlDataSourcemean it is an expression and evaluated thus. And we can create our own custom Expression.

Create a class like this that extends ExpressionBuilder

namespace YourNameSpace
{
    [ExpressionPrefix("EncryptedExpressionStrings")]
    public class EncryptedConnectionStringExpressionBuilder : ExpressionBuilder
    {
        private static string DecryptConnectionString(string cipherText)
        {
            return Encryption.Decrypt(cipherText);
        }
        public static ConnectionStringSettings GetConnectionStringSettings(string connectionStringName)
        {           
            return ConfigurationManager.ConnectionStrings[connectionStringName];
        }
        public static string GetConnectionString(string connectionStringName)
        {
            string decryptedConnectionString = null;
            System.Web.Caching.Cache connectionStringCache = new System.Web.Caching.Cache();
            if (connectionStringCache["connectionString"] == null)
            {
                ConnectionStringSettings settings = GetConnectionStringSettings(connectionStringName);
                decryptedConnectionString = DecryptConnectionString(settings.ConnectionString);
                connectionStringCache.Insert("connectionString", decryptedConnectionString);
            }
           else
           {
               decryptedConnectionString = (string)connectionStringCache["connectionString"];
            }
            return decryptedConnectionString;
        }
        public static string GetConnectionStringProviderName(string connectionStringName)
        {
            ConnectionStringSettings settings = GetConnectionStringSettings(connectionStringName);          
            return settings.ProviderName;
        }
        public override CodeExpression GetCodeExpression(BoundPropertyEntry entry, object parsedData, ExpressionBuilderContext context)
        {
            Pair pair = DirectCast<Pair>(parsedData);
            string text = pair.First.ToString();
            if (Convert.ToBoolean(pair.Second))
            {
                return new CodeMethodInvokeExpression(new CodeTypeReferenceExpression(base.GetType()), "GetConnectionString", new CodeExpression[] { new CodePrimitiveExpression() { Value = text } });
            }
            else
            {
                return new CodeMethodInvokeExpression(new CodeTypeReferenceExpression(base.GetType()), "GetConnectionStringProviderName", new CodeExpression[] { new CodePrimitiveExpression() { Value = text } });
        }
        static T DirectCast<T>(object o) where T : class
        {
            T value = o as T;
            if (value == null && o != null)
            {
                throw new InvalidCastException();
            }
            return value;
        }
    }

And you have to declare the new custom ExpressionBuilder in the web.config

<compilation>
    <expressionBuilders>
        <add expressionPrefix="EncryptedExpressionStrings" type="YourNameSpace.EncryptedConnectionStringExpressionBuilder"/>
     </expressionBuilders>
</compilation>

And you declare your connectionString that is encrypted

<connectionStrings>
    <add name="EncryptedSqlDBConnectionString" connectionString="nWCfxsad8lkdyLWERODVxd3Ox..."
</connectionStrings>

And then you declare the name of the prefix in the web.config for your custom ExpressionBuilder and the name of your ConnectionString in the asp:SqlDataSource

<asp:SqlDataSource runat="server" ID="YourID" ConnectionString="<%$ EncryptedExpressionStrings:EncryptedSqlDBConnectionString %>" SelectCommand="SELECT * FROM YourDBTable" />

This means that you can encrypt the connection String in your web.config and still evaluate the asp.SqlDataSource connectionString.

Answer 2

It turned out to be a very simple solution of updating the SqlDataSource.ConnectionString in the SqlDataSource's Init event, as follows:

    protected void SqlDataSource_Init(object sender, EventArgs e)
    {
        string connectionString = Encryption.Decrypt(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString, "XXXXXX");
        SqlDataSource.ConnectionString = connectionString;
    }