Search This Blog

Monday, March 1, 2010

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!


8 comments:

Geir said...

Thanks!! Exactly what I have been looking for, but I am getting a runtime error:
["Parser Error Message: Unrecognized attribute 'name'"]
.. where 'name' is always the first attribute name that appears in the add node that the decrypt method returns.

Decrypt is returning:



Is that incorrect...? Any obvious idea on what I am doing wrong? (I am working with .Net 3.5 MVC)

Geir said...

As the code in my comment went blank above ... Decrypt is returning:
&ltadd name="blux" connectionString="..." Provider="..."/&gt

Donald Frederick said...
This comment has been removed by the author.
Donald Frederick said...

Geir,
It seems my post had the same issue as your first comment.

I bet, if you compare your encrypted connection strings block to mine above, you are missing the nested EncryptedData element inside of the connectionStrings.

At the end of the Decrypt method, there is a statement:
doc.LoadXml("" + node.InnerXml +"");
It's incorrect. Inside of the first pair of quotes, their should be and opening xml tag with the text [EncryptedData] with the lessthan, greaterthan tags surrounding it. Likewise, the second pair of quotes should have a closing EncryptedData tag.

I fixed the text above, please scroll up if i'm not being clear enough.

Thank you for pointing out the issue. As you can tell, I'm new at the blogging and I expected my text to be escaped, but apparently this blog software doesn't do so.

Geir said...

Hi Donald, thanks for your reply. I am getting back to this issue, and still stuck on the return value from the Decrypt method.

Should the return value include the "EncryptedData" node as a parent, or some other parent ("DecryptedData"?) or just a list of "add" nodes? I'm not sure if the output.Append(""); is intentional in the Decrypt method?

Could you possibly list the xml structure you are returning from the Decrypt method? I realize the blog-system challenge :)

Donald Frederick said...

Geir,

Sorry about the confusion, you are correct, the blog software struck again.
The first output.Append("") in the Decrypt() method should have an opening "connectionStrings" tag and a corresponding closing "connectionsStrings" tag at the end.

I fixed it above as well.

In the future I will have to make a contentious effort to replace < and > with & l t ; and & g t ; before posting so this doesn't happen anymore.

Anonymous said...

I am getting this error
Could not load file or assembly 'ProtectedConfiguration' or one of its dependencies.
Can ypu please tell me what i am missing.

Donald Frederick said...

@Anonymous,
It sounds like you forgot to add a reference for ProtectedConfiguration to your project.

For IIS (or some other host) to use this provider, you need to either add a reference to the ProtectedConfiguration project, the ProtectedConfiguration dll, or add the dll to the GAC.