Encrypt SQL connectionstring

Question

I need to encrypt my connectionString - I tried to run a Command Prompt with

aspnet_regiis.exe -pef “connectionStrings” C:\Projects\DemoApplication

It didn't work it says

Cannot determine the location of .Net Framework 32 bit

  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source = |SQL/CE|"/>
  </connectionStrings>

And I can't find another way to do it.

Any ideas how can I encrypt my connectionString?

I am using VS 2012 and .NET 4.5

Answer 1

According to http://forums.asp.net/t/1826319.aspx?Encrypt+web+config+connectionstring

You can move the encryption into the codebehind:
http://weblogs.asp.net/sukumarraju/encrypt-and-decrypt-connectionstring-section-in-web-config

Of course, instead of using the Microsoft way, you can also roll your own encryption, and just put the encrypted password in the connection string.

Namespace MyOrg


    Public Class CryptStrings
        Protected Shared strKey As String = "lalalalala"
        Protected Shared TripleDESprovider As New System.Security.Cryptography.TripleDESCryptoServiceProvider
        Protected Shared MD5Hasher As New System.Security.Cryptography.MD5CryptoServiceProvider


        ' MyOrg.CryptStrings.DeCrypt("abc")
        Public Shared Function DeCrypt(ByVal strSourceText As String) As String
            Dim strReturnValue As String = Nothing

            Try
                TripleDESprovider.Key = MD5Hasher.ComputeHash(System.Text.ASCIIEncoding.ASCII.GetBytes(strKey))
                TripleDESprovider.Mode = System.Security.Cryptography.CipherMode.ECB
                If Not strSourceText = "" Then
                    Dim DESdecryptor As System.Security.Cryptography.ICryptoTransform = TripleDESprovider.CreateDecryptor()
                    Dim baBuffer() As Byte = Convert.FromBase64String(strSourceText)
                    TripleDESprovider.Clear()
                    'DESdecryptor.Dispose()
                    strReturnValue = System.Text.ASCIIEncoding.ASCII.GetString _
                    (DESdecryptor.TransformFinalBlock(baBuffer, 0, baBuffer.Length))

                    Array.Clear(baBuffer, 0, baBuffer.Length)
                Else
                    strReturnValue = ""
                End If

                Return strReturnValue
            Catch ex As Exception
                'Me.Cursor() = Cursors.Default
                Logging.WriteLogFile("FEHLER", ex.Message)
                Logging.WriteLogFile("FEHLER", "-----------------------------------------------------------------")
                Logging.WriteLogFile("FEHLER", ex.StackTrace.ToString())
                Console.WriteLine(ex.Message.ToString() & vbLf & vbLf & ex.StackTrace.ToString, MsgBoxStyle.Critical, "FEHLER ...")
                Logging.WriteLogFile("MELDUNG", "-----------------------------------------------------------------")
                Logging.WriteLogFile("ENDE", "COR_DWG_Verwaltung beendet.")
            End Try

            Return strReturnValue
        End Function


        ' MyOrg.CryptStrings.Crypt("abc")
        Public Shared Function Crypt(ByVal strSourceText As String) As String
            Dim strReturnValue As String = Nothing
            Try

                TripleDESprovider.Key = MD5Hasher.ComputeHash(System.Text.ASCIIEncoding.ASCII.GetBytes(strKey))
                TripleDESprovider.Mode = System.Security.Cryptography.CipherMode.ECB
                Dim DESencryptor As System.Security.Cryptography.ICryptoTransform = TripleDESprovider.CreateEncryptor()
                Dim baBuffer() As Byte = System.Text.ASCIIEncoding.ASCII.GetBytes(strSourceText)
                strReturnValue = Convert.ToBase64String _
                (DESencryptor.TransformFinalBlock(baBuffer, 0, baBuffer.Length))
                Array.Clear(baBuffer, 0, baBuffer.Length)
                Return strReturnValue
            Catch ex As Exception
                'Me.Cursor() = Cursors.Default
                Logging.WriteLogFile("FEHLER", ex.Message)
                Logging.WriteLogFile("FEHLER", "-----------------------------------------------------------------")
                Logging.WriteLogFile("FEHLER", ex.StackTrace.ToString())
                Console.WriteLine(ex.Message.ToString & vbLf & vbLf & ex.StackTrace.ToString, MsgBoxStyle.Critical, "FEHLER ...")
                Logging.WriteLogFile("MELDUNG", "-----------------------------------------------------------------")
                Logging.WriteLogFile("ENDE", "COR LDAP-Service beendet.")
            End Try

            Return strReturnValue
        End Function


        ' MyOrg.CryptStrings.GenerateHash("abc")
        Public Shared Function GenerateHash(ByVal strSourceText As String) As String

            Try
                Dim encUnicode As New System.Text.UnicodeEncoding
                Dim ByteSourceText() As Byte = encUnicode.GetBytes(strSourceText)
                Dim MD5_HashGenerator As New System.Security.Cryptography.MD5CryptoServiceProvider
                Dim ByteHash() As Byte = MD5_HashGenerator.ComputeHash(ByteSourceText)
                Return Convert.ToBase64String(ByteHash)
            Catch ex As Exception
                'Me.Cursor() = Cursors.Default
                Logging.WriteLogFile("FEHLER", ex.Message)
                Logging.WriteLogFile("FEHLER", "-----------------------------------------------------------------")
                Logging.WriteLogFile("FEHLER", ex.StackTrace.ToString)
                Console.WriteLine(ex.Message.ToString & vbLf & vbLf & ex.StackTrace.ToString, MsgBoxStyle.Critical, "FEHLER ...")
                Logging.WriteLogFile("MELDUNG", "-----------------------------------------------------------------")
                Logging.WriteLogFile("ENDE", "COR LDAP-Service beendet.")
            End Try

            Return Nothing
        End Function


    End Class 'CryptStrings


End Namespace ' MyOrg

Afterwards, you change your function GetConnectionString

into

public string GetConnectionString()
{
    string old = GetOldConnectionString();
    var csb = new System.Data.SqlClient.SqlConnectionStringBuilder(old);
    csb.Password = MyOrg.CryptStrings.DeCrypt(csb.Password);
    return csb.ConnectionString;
}

The C#-Version looks like this

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;


namespace DB.Abstraction.Tools.Cryptography
{


    public class AES
    {
        protected static string strKey = "1b55ec1d96f637aa7b73c31765a12c2c8fb8b9f6ae8b14396475a20ed1a83dac";
        protected static string strIV = "d4e3381cdd39ddb70f85e96d11b667e5";


        public static string GetKey()
        {
            return strKey;
        } // End Sub GetKey


        public static string GetIV()
        {
            return strIV;
        } // End Sub GetIV


        public static void SetKey(ref string strInputKey)
        {
            strKey = strInputKey;
        } // End Sub SetKey 


        public static void SetIV(ref string strInputIV)
        {
            strIV = strInputIV;
        } // End Sub SetIV


        public static string GenerateKey()
        {
            System.Security.Cryptography.RijndaelManaged objRijndael = new System.Security.Cryptography.RijndaelManaged();

            objRijndael.GenerateKey();
            objRijndael.GenerateIV();

            byte[] bIV = objRijndael.IV;
            byte[] bKey = objRijndael.Key;
            objRijndael.Clear();

            return "IV: " + ByteArrayToHexString(bIV) + Environment.NewLine + "Key: " + ByteArrayToHexString(bKey);
        } // End Function GenerateKey



        public static string Encrypt(string strPlainText)
        {
            //Dim roundtrip As String
            //Dim encASCII As New System.Text.ASCIIEncoding()
            System.Text.Encoding enc = System.Text.Encoding.UTF8;
            System.Security.Cryptography.RijndaelManaged objRijndael = new System.Security.Cryptography.RijndaelManaged();
            //Dim fromEncrypt() As Byte
            byte[] baCipherTextBuffer = null;
            byte[] baPlainTextBuffer = null;
            byte[] baEncryptionKey = null;
            byte[] baInitializationVector = null;

            //Create a new key and initialization vector.
            //objRijndael.GenerateKey()
            //objRijndael.GenerateIV()
            objRijndael.Key = HexStringToByteArray(strKey);
            objRijndael.IV = HexStringToByteArray(strIV);


            //Get the key and initialization vector.
            baEncryptionKey = objRijndael.Key;
            baInitializationVector = objRijndael.IV;
            //strKey = ByteArrayToHexString(baEncryptionKey)
            //strIV = ByteArrayToHexString(baInitializationVector)

            //Get an encryptor.
            System.Security.Cryptography.ICryptoTransform ifaceAESencryptor = objRijndael.CreateEncryptor(baEncryptionKey, baInitializationVector);

            //Encrypt the data.
            System.IO.MemoryStream msEncrypt = new System.IO.MemoryStream();
            System.Security.Cryptography.CryptoStream csEncrypt = new System.Security.Cryptography.CryptoStream(msEncrypt, ifaceAESencryptor, System.Security.Cryptography.CryptoStreamMode.Write);

            //Convert the data to a byte array.
            baPlainTextBuffer = enc.GetBytes(strPlainText);

            //Write all data to the crypto stream and flush it.
            csEncrypt.Write(baPlainTextBuffer, 0, baPlainTextBuffer.Length);
            csEncrypt.FlushFinalBlock();

            //Get encrypted array of bytes.
            baCipherTextBuffer = msEncrypt.ToArray();

            return ByteArrayToHexString(baCipherTextBuffer);
        } // End Function Encrypt


        public static string DeCrypt(string strEncryptedInput)
        {
            string strReturnValue = null;

            if (string.IsNullOrEmpty(strEncryptedInput)) 
            {
                throw new ArgumentNullException("strEncryptedInput", "strEncryptedInput may not be string.Empty or NULL, because these are invid values.");
            }

            // Dim encASCII As New System.Text.ASCIIEncoding()
            System.Text.Encoding enc = System.Text.Encoding.UTF8;

            System.Security.Cryptography.RijndaelManaged objRijndael = new System.Security.Cryptography.RijndaelManaged();

            byte[] baCipherTextBuffer = HexStringToByteArray(strEncryptedInput);


            byte[] baDecryptionKey = HexStringToByteArray(strKey);
            byte[] baInitializationVector = HexStringToByteArray(strIV);

            // This is where the message would be transmitted to a recipient
            // who already knows your secret key. Optionally, you can
            // also encrypt your secret key using a public key algorithm
            // and pass it to the mesage recipient along with the RijnDael
            // encrypted message.            
            //Get a decryptor that uses the same key and IV as the encryptor.
            System.Security.Cryptography.ICryptoTransform ifaceAESdecryptor = objRijndael.CreateDecryptor(baDecryptionKey, baInitializationVector);

            //Now decrypt the previously encrypted message using the decryptor
            // obtained in the above step.
            System.IO.MemoryStream msDecrypt = new System.IO.MemoryStream(baCipherTextBuffer);
            System.Security.Cryptography.CryptoStream csDecrypt = new System.Security.Cryptography.CryptoStream(msDecrypt, ifaceAESdecryptor, System.Security.Cryptography.CryptoStreamMode.Read);

            //Dim baPlainTextBuffer() As Byte
            //baPlainTextBuffer = New Byte(baCipherTextBuffer.Length) {}
            byte[] baPlainTextBuffer = new byte[baCipherTextBuffer.Length + 1];

            //Read the data out of the crypto stream.
            csDecrypt.Read(baPlainTextBuffer, 0, baPlainTextBuffer.Length);

            //Convert the byte array back into a string.
            strReturnValue = enc.GetString(baPlainTextBuffer);
            if(!string.IsNullOrEmpty(strReturnValue))
                strReturnValue = strReturnValue.Trim('\0');

            return strReturnValue;
        } // End Function DeCrypt


        // VB.NET to convert a byte array into a hex string
        public static string ByteArrayToHexString(byte[] arrInput)
        {
            System.Text.StringBuilder strOutput = new System.Text.StringBuilder(arrInput.Length);

            for (int i = 0; i <= arrInput.Length - 1; i++) 
            {
                strOutput.Append(arrInput[i].ToString("X2"));
            }

            return strOutput.ToString().ToLower();
        } // End Function ByteArrayToHexString


        public static byte[] HexStringToByteArray(string strHexString)
        {
            int iNumberOfChars = strHexString.Length;
            byte[] baBuffer = new byte[iNumberOfChars / 2];
            for (int i = 0; i <= iNumberOfChars - 1; i += 2) 
            {
                baBuffer[i / 2] = Convert.ToByte(strHexString.Substring(i, 2), 16);
            }
            return baBuffer;
        } // End Function HexStringToByteArray

    } // End Class AES 


    public class DES
    {


        protected static string strSymmetricKey = "lalalalala";
        //Protected Shared strSymmetricKey As String = "Als symmetrischer Key kann irgendein Text verwendet werden. äöü'"

        // http://www.codeproject.com/KB/aspnet/ASPNET_20_Webconfig.aspx
        // http://www.codeproject.com/KB/database/Connection_Strings.aspx
        public static string DeCrypt(string SourceText)
        {
            string strReturnValue = "";

            if (string.IsNullOrEmpty(SourceText)) 
            {
                return strReturnValue;
            } // End if (string.IsNullOrEmpty(SourceText)) 


            using (System.Security.Cryptography.TripleDESCryptoServiceProvider Des = new System.Security.Cryptography.TripleDESCryptoServiceProvider()) 
            {

                using (System.Security.Cryptography.MD5CryptoServiceProvider HashMD5 = new System.Security.Cryptography.MD5CryptoServiceProvider()) 
                {
                    Des.Key = HashMD5.ComputeHash(System.Text.Encoding.UTF8.GetBytes(strSymmetricKey));
                    Des.Mode = System.Security.Cryptography.CipherMode.ECB;

                    System.Security.Cryptography.ICryptoTransform desdencrypt = Des.CreateDecryptor();
                    byte[] buff = System.Convert.FromBase64String(SourceText);
                    strReturnValue = System.Text.Encoding.UTF8.GetString(desdencrypt.TransformFinalBlock(buff, 0, buff.Length));
                } // End Using HashMD5

            } // End Using Des

            return strReturnValue;
        } // End Function DeCrypt


        public static string Crypt(string SourceText)
        {
            string strReturnValue = "";

            using (System.Security.Cryptography.TripleDESCryptoServiceProvider Des = new System.Security.Cryptography.TripleDESCryptoServiceProvider()) 
            {

                using (System.Security.Cryptography.MD5CryptoServiceProvider HashMD5 = new System.Security.Cryptography.MD5CryptoServiceProvider()) 
                {
                    Des.Key = HashMD5.ComputeHash(System.Text.Encoding.UTF8.GetBytes(strSymmetricKey));
                    Des.Mode = System.Security.Cryptography.CipherMode.ECB;
                    System.Security.Cryptography.ICryptoTransform desdencrypt = Des.CreateEncryptor();
                    byte[] buff = System.Text.Encoding.UTF8.GetBytes(SourceText);

                    strReturnValue = System.Convert.ToBase64String(desdencrypt.TransformFinalBlock(buff, 0, buff.Length));
                } // End Using HashMD5

            } // End UsingDes

            return strReturnValue;
        } // End Function Crypt


        public static string GenerateKey()
        {
            System.Security.Cryptography.TripleDESCryptoServiceProvider objDESprovider = new System.Security.Cryptography.TripleDESCryptoServiceProvider();

            objDESprovider.GenerateKey();
            objDESprovider.GenerateIV();
            byte[] bIV = objDESprovider.IV;
            byte[] bKey = objDESprovider.Key;

            return "IV: " + AES.ByteArrayToHexString(bIV) + Environment.NewLine + "Key: " + AES.ByteArrayToHexString(bKey);
        } // End Function GenerateKey


        public static string GenerateHash(string SourceText)
        {
            string strReturnValue = "";
            byte[] ByteSourceText = System.Text.Encoding.UTF8.GetBytes(SourceText);

            using (System.Security.Cryptography.MD5CryptoServiceProvider Md5 = new System.Security.Cryptography.MD5CryptoServiceProvider()) 
            {
                byte[] ByteHash = Md5.ComputeHash(ByteSourceText);
                strReturnValue = System.Convert.ToBase64String(ByteHash);
                ByteHash = null;
            } // End Using Md5

            return strReturnValue;
        } // End Function GenerateHash


    } // End Class DES


} // End Namespace 

This can be further speed-optimized:

public static string constr = null;

public string GetConnectionString()
{
    if(constr != null)
        return constr;

    string old = GetOldConnectionString();
    var csb = new System.Data.SqlClient.SqlConnectionStringBuilder(old);
    csb.Password = MyOrg.CryptStrings.DeCrypt(csb.Password);
    constr = csb.ConnectionString;
    return constr;
}

Answer 2

I currently use this custom class

using System.Configuration; // Requires a reference to assembly System.Configuration

public static class ConfigurationEncryptor {
    [Flags]
    public enum ConfigurationSectionType {
        ConnectionStrings = 1,
        ApplicationSettings = 2
    }

    public static bool Encrypt(ConfigurationSectionType section) {
        bool result = false;

        Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
        if (config == null)
            throw new Exception("Cannot open the configuration file.");

        if (section.HasFlag(ConfigurationSectionType.ConnectionStrings)) {
            result = result || EncryptSection(config, "connectionStrings");
        }

        if (section.HasFlag(ConfigurationSectionType.ApplicationSettings)) {
            result = result || EncryptSection(config, "appSettings");
        }

        return result;
    }

    private static bool EncryptSection(Configuration config, string section) {
        ConfigurationSection currentSection = config.GetSection(section);
        if (currentSection == null)
            throw new Exception("Cannot find " + section + " section in configuration file.");
        if (!currentSection.SectionInformation.IsProtected) {
            currentSection.SectionInformation.ProtectSection("DataProtectionConfigurationProvider");
            config.Save();

            // Refresh configuration
            ConfigurationManager.RefreshSection(section);

            return true;
        }
        return false;
    }
}

And use it:

ConfigurationEncryptor.Encrypt(
    ConfigurationEncryptor.ConfigurationSectionType.ApplicationSettings |
    ConfigurationEncryptor.ConfigurationSectionType.ConnectionStrings
    // Or just the app settings, or just the connection strings
);