Encrypting Passwords in a Connection String

One of the topics that has come up repeatedly is encrypting connection strings.

Many times, a team doesn't want to encrypt the whole connection string.  It makes it easier for support, especially if it's a larger team that's on a support rotation.  If you have multiple environments, it's easier to determine which database your app is connected to.  If you have granular DB security, it's easier to trouble shoot what roles are missing from which account if you know what the account is.

Some people argue that you shouldn't have a password in a connection string, that you should be using integrated security.  They seem to forget that there are situations where that's not possible.  For example, if your web servers are in a DMZ, they typically don't have access to Active Directory.

Do a Google search, and you'll see how many people just want to protect the password, but are told it's just not possible.  That's not true, and I'm about to show you how you can encrypt just a password in a connection string.


For more on this, look here:Donald Frederick's Blog: More Configuration Protected Data Encryption



Typically, what you see is this:

<connectionStrings >

            <add name="PortalConnectionString" connectionString="Server=portalserver\sql2005;Database=PortalDb;User ID=PortalUser;Password=portalPass" />

<connectionStrings>

But in my case, I wanted to see this:

<connectionStrings configProtectionProvider="ConnectionStringProtectedConfigurationProvider">

            <EncryptedData>

                        <add name="PortalConnectionString" connectionString="Data Source=portalServer\sql2005;Initial Catalog=PortalDb;User ID=PortalUser;Password="AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAA9UDF9oj2/0ePz/g7oTJgQAAAACAAAAAAADZgAAqAAAABAAAADAto3n3xCpElzVztkGiUfHAAAAAASAAACgAAAAEAAAACCXh2gjmKZfsnS9MRdoBqwIAAAAAb6Uu4W7Vk4UAAAACJlUP0qazHy6TieOBZXD4EfhfzA="" />

            <EncryptedData>

<connectionStrings>

Here's a good primer to get you familiar with the DPAPI and encrypting configuration sections found on MSDN.

I'm not going to explain everything here, I'm going to assume you have a working knowledge of encryption, C#, and .NET.  I also would like to warn you against blindly copying and pasting this.  It'll work, but only for SqlConnectionStrings, and you won't be able to copy and paste encrypted information from machine to machine.

Here's my provider class:

using System;

using System.Data.SqlClient;

using System.Security.Permissions;

using System.Xml;

using System.Security.Cryptography;

using System.Text;

using System.Collections.Specialized;

using System.Configuration;

namespace ProtectedConfiguration

{

    [PermissionSetAttribute(SecurityAction.Demand, Name = "FullTrust")]

    public sealed class ConnectionStringProtectedConfigurationProvider : ProtectedConfigurationProvider

    {

        // Create Entropy To salt the process aka 'Magic Salt'

        readonly byte[] entropy = Encoding.Unicode.GetBytes("magicsalt");

        private string providerName;

        #region Overrides of ProviderBase

        //

        // ProviderBase.Name

        //

        public override string Name

        {

            get { return providerName; }

        }

      

        #endregion

        #region Overrides of ProtectedConfigurationProvider

        //

        // ProviderBase.Initialize

        //

        public override void Initialize(string name, NameValueCollection config)

        {

            providerName = name;

            base.Initialize(name, config);

        }

        ///

        /// Encrypts the passed object from a configuration file.

        ///

        ///

        /// The object containing encrypted data.

        ///

        /// The object to encrypt.

        public override XmlNode Encrypt(XmlNode node)

        {

            XmlDocument doc = new XmlDocument();

            doc.PreserveWhitespace = true;

           

            XmlNodeList nodeList = node.SelectNodes("/connectionStrings/add");

            if (nodeList != null)

            {

                for (int i = 0; i < nodeList.Count; i++)

                {

                    XmlAttribute attribute = nodeList[i].Attributes["connectionString"];

                    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(attribute.Value);

                    if (!string.IsNullOrEmpty(builder.Password))

                    {

                        builder.Password = EncryptData(builder.Password);

                        attribute.Value = builder.ToString();

                    }

                }

            }

            doc.LoadXml("<EncryptedData>" + node.InnerXml +"</EncryptedData>"); 

            return doc.DocumentElement;

           

        }

        ///

        /// Decrypts the passed object from a configuration file.

        ///

        ///

        /// The object containing decrypted data.

        ///

        /// The object to decrypt.

        public override XmlNode Decrypt(XmlNode encryptedNode)

        {

            //get each in the connection string block

            XmlNodeList nodeList = encryptedNode.SelectNodes("/EncryptedData/add");

           

            //start building the output xml string

            StringBuilder output = new StringBuilder();

            output.Append("<connectionStrings>");

            if (nodeList != null)

            {

                for (int i = 0; i < nodeList.Count; i++)

                {

                    //get the text for the connection string

                    XmlAttribute attribute = nodeList[i].Attributes["connectionString"];

                    //use sqlconnectionstringbuilder to parse

                    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(attribute.Value);

                    if (!string.IsNullOrEmpty(builder.Password))

                    {

                        //decrypt it

                        builder.Password = DecryptData(builder.Password);

                        attribute.Value = builder.ToString();

                    }

                    //add the connection string with decrypted password to output

                    output.Append(nodeList[i].OuterXml);

                }

            }

            //add closing tag

            output.Append("</connectionStrings>");

            XmlDocument xmlDoc = new XmlDocument();

            xmlDoc.PreserveWhitespace = true;

           

            xmlDoc.LoadXml(output.ToString());

            return xmlDoc.DocumentElement;

           

        }

        private string EncryptData(string data)

        {

            //convert to byte array

            byte[] valBytes = Encoding.ASCII.GetBytes(data);

            // Use DPAPI to Encrypt

            byte[] encryptedData = ProtectedData.Protect

                (valBytes,

                 entropy, DataProtectionScope.LocalMachine);

            //convert to base64 and wrap with xml tags

            return  Convert.ToBase64String(encryptedData) ;

        }

        private string DecryptData(string encryptedData)

        {

            //convert to encrypted byte array

            byte[] valBytes = Convert.FromBase64String(encryptedData);

            // Decrypt using DPAPI

            byte[] decryptedData = ProtectedData.Unprotect

                (valBytes, entropy, DataProtectionScope.LocalMachine);

            var encoding = new ASCIIEncoding();

            //convert to ascii

            return encoding.GetString(decryptedData);

        }

     

        #endregion

    }

}

OK, got that?

The main thing is we extended ProtectedConfigurationProvider.  We overrode the initialize to store the provider name and we overrode the Encrypt and Decrypt methods so we could parse the xml in the   section.  If you inspect the EncryptData method, you will see we're just using the ProtectedData class to do the encryption.

To use it you need to add it to your web.config as a child of

<configProtectedData>

            <providers>

                  <add name="ConnectionStringProtectedConfigurationProvider" type="ProtectedConfiguration.ConnectionStringProtectedConfigurationProvider,ProtectedConfiguration"/>

            <providers>

<configProtectedData>

To actually do the encryption, you've got a couple options.  

• You could encrypt it when you deploy using reg_iis.exe, to do that you have to give the assembly a strong name and put it into the GAC. 
• Or you could call it programatically when your application starts

I decided to go with the second option, so I had to add a little more code.  In the Global.asax, I added this:

protected void Application_Start(object sender, EventArgs e)

        {

            /**************************************

             * Verify connection string is encrypted

             * ***********************************/

            //get the application path so we can open the config file for writing

            string path = HttpContext.Current.Request.ApplicationPath;

            //open the config file

            Configuration config = WebConfigurationManager.OpenWebConfiguration(path);

            //get the connection string section

            ConfigurationSection configurationSection = config.GetSection("connectionStrings");

            //If it's not encrypted, encrypt it.

            if(configurationSection != null && !configurationSection.SectionInformation.IsProtected)

            {

                configurationSection.SectionInformation.ProtectSection("ConnectionStringProtectedConfigurationProvider");

                configurationSection.SectionInformation.ForceSave = true;

                config.Save(ConfigurationSaveMode.Full);

            }

           

        }

Again, I caution you against copying and pasting.  This is just meant to show the concepts, it's not a robust solution.  It will encrypt on application startup, but if you change the web.config on the fly, it won't.  I suggest a slightly more robust implementation, but that's outside the scope of this discussion. 

I hope you found this useful.  More to come in the future!