SSIS Encrypt and Decrypt Connection String

              Sometimes it is needed to secure the connection string which we use in SSIS package. Even we store the connection string in sql server database as config, the users who has access to the config table, can read the connection string. In order to secure it, we should encrypt the connection string and store it in sql config table. In this way it will not be human readable form. It is secure as the person who encrypts the string may not have access to prod environment (in prod environment DBAs will do data update). We may need to consider that the .net framework version should same in the environments where the encryption is happening and where it will be decrypted (the SSIS package run environment).

Below are the steps to do for securing the connection string.

1. Create a reusable library (.dll). In this way the library file can be used with other SSIS package or .net code.

2. Register it.

3. Refer in SSIS package.

Create a reusable library

 Open a .net class library type project.
 Create a class

  public class EncryptDecrypt
  {
        public string StringToEncrypt { get; set; }
        public string SecretKey { get; set; }
        public string StringToDecrypt { get; set; }
 
        public string GetEncryptedString()
        {
            try
            {

                string strEncryptedString;

                byte[] clearBytes = System.Text.Encoding.Unicode.GetBytes(StringToEncrypt);
                PasswordDeriveBytes pdb = new PasswordDeriveBytes(SecretKey, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
                byte[] encryptedData = Encrypt(clearBytes, pdb.GetBytes(32), pdb.GetBytes(16));
                strEncryptedString = Convert.ToBase64String(encryptedData);

                return strEncryptedString;

            }
            catch
            {
                throw;
            }
        }

         public string GetDecryptedString()
        {
            try
            {
                string strDecryptedString;

                byte[] cipherBytes = Convert.FromBase64String(StringToDecrypt);
                PasswordDeriveBytes pdb = new PasswordDeriveBytes(SecretKey, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
                byte[] decryptedData = Decrypt(cipherBytes, pdb.GetBytes(32), pdb.GetBytes(16));
                strDecryptedString = System.Text.Encoding.Unicode.GetString(decryptedData);

                return strDecryptedString;
            }
            catch
            {
                throw;
            }
        }
       private static byte[] Encrypt(byte[] clearText, byte[] Key, byte[] IV)
       {
            MemoryStream ms = new MemoryStream();
            Rijndael alg = Rijndael.Create();
            alg.Key = Key;
            alg.IV = IV;
            CryptoStream cs = new CryptoStream(ms, alg.CreateEncryptor(), CryptoStreamMode.Write);
            cs.Write(clearText, 0, clearText.Length);
            cs.Close();
            byte[] encryptedData = ms.ToArray();
            return encryptedData;
        }

        private static byte[] Decrypt(byte[] cipherData, byte[] Key, byte[] IV)
        {
            MemoryStream ms = new MemoryStream();
            Rijndael alg = Rijndael.Create();
            alg.Key = Key;
            alg.IV = IV;
            CryptoStream cs = new CryptoStream(ms, alg.CreateDecryptor(), CryptoStreamMode.Write);
            cs.Write(cipherData, 0, cipherData.Length);
            cs.Close();
            byte[] decryptedData = ms.ToArray();
            return decryptedData;
        }
 }

Register the .dll file

Please follow the steps mentioned (https://microsoft-ssis.blogspot.com/2011/05/referencing-custom-assembly-inside.html) to sign the dll with strong name key.

Refer in SSIS package

1. Create a variable in SSIS package. This variable should be configured to get data from sql config table at runtime.
2. Don’t config (through properties -> expression) the connection manager to refer this variable for connection string. If we config this way, the connection manager will throw error at runtime as it will get the encrypted string.
3. Place a script task at the beginning of the Control Flow.
4. Once the dll is registered, we can refer that in the script task.
5. Before the main process start, add a script task and refer the .dll file.
6. Create an object of the class and assign the properties with appropriate values.
7. Below is the code to assign the decrypted value to the connection manager.

public void Main()
{
    // TODO: Add your code here

ConnectionManager oCon;
    string strEncryptedConnStr;
    string strDecryptedConnStr;

    EncryptDecrypt ed = new EncryptDecrypt();

    ed.SecretKey = "MySecretkey";

    strEncryptedConnStr = Dts.Variables["User::varTeradataConnStr_str"].Value.ToString();
    ed.StringToDecrypt = strEncryptedConnStr;
    strDecryptedConnStr = ed.GetDecryptedString();

    oCon = Dts.Connections["ADONET_CONN-Teradata"];
    oCon.ConnectionString = strDecryptedConnStr;

    Dts.TaskResult = (int)ScriptResults.Success;
}

In this way (one of the ways which I tried) the connection string can be secured and decrypted at the time of running the SSIS package.