Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Encrypt a file from SSIS using the Advanced Encryption Standard (AES)

By Stan Kulp,

The Advanced Encryption Standard (AES) cryptography code used in this project was adapted from the C# code that Ira Lukhezo has kindly provided on his blog at: http://lukhezo.com/2011/11/06/encrypting-files-in-net-using-the-advanced-encryption-standard-aes/

Overview

Creating an SSIS package that encrypts a file with a CLR assembly and generates an MD5 checksum file entails the following steps:

(Steps 1 through 6 can be omitted if you prefer by using the attached AES256.dll file rather than generating your own.)

  1. Create a C# class library project
  2. Paste C# cryptographic code into the class file
  3. Change the target framework of the class library to .NET 3.5
  4. Create a strong name key file
  5. Configure the "Signing" parameter of the compiler to sign the class library with the key
  6. Compile the C# class library to an assembly
  7. Copy the resource file 'gacutil.exe' to the folder C:\AES256\
  8. Browse to the location of the compiled AES256.dll assembly and copy it to the C:\AES256\ folder
  9. Add the AES256.dll assembly to GAC with the gacutil utility
  10. Confirm the addition of the AES256 assembly to the Global Assembly Cache (GAC)
  11. Create an SSIS project in Business Intelligence Development Studio
  12. Add a C# script task to the SSIS package
  13. Add "password" and "salt" variables to the SSIS package
  14. Add a reference to the AES256 assembly in the script task
  15. Add a "using" reference to the AesApp.Rijndael namespace of the AES256 assembly in the script task code
  16. Paste C# code into the script task
  17. Change the script task target framework to .NET 3.5
  18. Copy the test file to the C:\AES256\ folder
  19. Execute the SSIS package
  20. Confirm that files named test.txt.enc, test.tst.enc.dec and md5sum.txt have been generated
  21. Open text.txt.enc to confirm that it is a binary file
  22. Open test.txt.enc.dec to demonstrate that the text.txt.enc file has been decrypted
  23. Open the md5sum.txt file to confirm that the checksum string has been generated

Step 1: Create a C# class library project

Create a new C# class library project named 'AES256' in MS Visual C# 2010 Express (downloadable at http://go.microsoft.com/?linkid=9709939), MS Visual Studio 2012 Express (downloadable at http://www.microsoft.com/en-us/download/confirmation.aspx?id=34673), SharpDevlop 4.4 (Downloadable from http://www.icsharpcode.net/OpenSource/SD/Download/GetFile.aspx?What=Setup&Release=Mirador), or any other appropriate programming environment.

A template class file is generated.

Step 2: Paste C# cryptographic code into the class file

Paste the following code...

using System;
using System.IO;
using System.Security.Cryptography;
using System.Text;

namespace AesApp.Rijndael
{
    public class Cryptology
    {
        public const int SizeOfBuffer = 1024 * 8;

        internal static byte[] EncryptStringToBytes(string plainText, byte[] key, byte[] iv)
        {
            // Check arguments.
            if (plainText == null || plainText.Length <= 0)
            {
                throw new ArgumentNullException("plainText");
            }
            if (key == null || key.Length <= 0)
            {
                throw new ArgumentNullException("key");
            }
            if (iv == null || iv.Length <= 0)
            {
                throw new ArgumentNullException("key");
            }

            byte[] encrypted;
            // Create an RijndaelManaged object
            // with the specified key and IV.
            using (var rijAlg = new RijndaelManaged())
            {
                rijAlg.Key = key;
                rijAlg.IV = iv;

                // Create a decrytor to perform the stream transform.
                ICryptoTransform encryptor = rijAlg.CreateEncryptor(rijAlg.Key, rijAlg.IV);

                // Create the streams used for encryption.
                using (var msEncrypt = new MemoryStream())
                {
                    using (var csEncrypt = new CryptoStream(msEncrypt, encryptor, CryptoStreamMode.Write))
                    {
                        using (var swEncrypt = new StreamWriter(csEncrypt))
                        {
                            //Write all data to the stream.
                            swEncrypt.Write(plainText);
                        }
                        encrypted = msEncrypt.ToArray();
                    }
                }
            }


            // Return the encrypted bytes from the memory stream.
            return encrypted;

        }

        internal static string DecryptStringFromBytes(byte[] cipherText, byte[] key, byte[] iv)
        {
            // Check arguments.
            if (cipherText == null || cipherText.Length <= 0)
                throw new ArgumentNullException("cipherText");
            if (key == null || key.Length <= 0)
                throw new ArgumentNullException("key");
            if (iv == null || iv.Length <= 0)
                throw new ArgumentNullException("key");

            // Declare the string used to hold
            // the decrypted text.
            string plaintext;

            // Create an RijndaelManaged object
            // with the specified key and IV.
            using (var rijAlg = new RijndaelManaged())
            {
                rijAlg.Key = key;
                rijAlg.IV = iv;

                // Create a decrytor to perform the stream transform.
                ICryptoTransform decryptor = rijAlg.CreateDecryptor(rijAlg.Key, rijAlg.IV);

                // Create the streams used for decryption.
                using (var msDecrypt = new MemoryStream(cipherText))
                {
                    using (var csDecrypt = new CryptoStream(msDecrypt, decryptor, CryptoStreamMode.Read))
                    {
                        using (var srDecrypt = new StreamReader(csDecrypt))
                        {
                            // Read the decrypted bytes from the decrypting stream
                            // and place them in a string.
                            plaintext = srDecrypt.ReadToEnd();
                        }
                    }
                }

            }
            return plaintext;
        }

        public static void EncryptFile(string inputPath, string outputPath, string password, string salt)
        {
            var input = new FileStream(inputPath, FileMode.Open, FileAccess.Read);
            var output = new FileStream(outputPath, FileMode.OpenOrCreate, FileAccess.Write);

            // Essentially, if you want to use RijndaelManaged as AES you need to make sure that:
            // 1.The block size is set to 128 bits
            // 2.You are not using CFB mode, or if you are the feedback size is also 128 bits

            var algorithm = new RijndaelManaged { KeySize = 256, BlockSize = 128 };
            var key = new Rfc2898DeriveBytes(password, Encoding.ASCII.GetBytes(salt));

            algorithm.Key = key.GetBytes(algorithm.KeySize / 8);
            algorithm.IV = key.GetBytes(algorithm.BlockSize / 8);

            using (var encryptedStream = new CryptoStream(output, algorithm.CreateEncryptor(), CryptoStreamMode.Write))
            {
                CopyStream(input, encryptedStream);
            }
        }

        public static void DecryptFile(string inputPath, string outputPath, string password, string salt)
        {
            var input = new FileStream(inputPath, FileMode.Open, FileAccess.Read);
            var output = new FileStream(outputPath, FileMode.OpenOrCreate, FileAccess.Write);

            // Essentially, if you want to use RijndaelManaged as AES you need to make sure that:
            // 1.The block size is set to 128 bits
            // 2.You are not using CFB mode, or if you are the feedback size is also 128 bits
            var algorithm = new RijndaelManaged { KeySize = 256, BlockSize = 128 };
            var key = new Rfc2898DeriveBytes(password, Encoding.ASCII.GetBytes(salt));

            algorithm.Key = key.GetBytes(algorithm.KeySize / 8);
            algorithm.IV = key.GetBytes(algorithm.BlockSize / 8);

            try
            {
                using (var decryptedStream = new CryptoStream(output, algorithm.CreateDecryptor(), CryptoStreamMode.Write))
                {
                    CopyStream(input, decryptedStream);
                }
            }
            catch (CryptographicException)
            {
                throw new InvalidDataException("Please supply correct password and salt values");
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static void CopyStream(Stream input, Stream output)
        {
            using (output)
            using (input)
            {
                byte[] buffer = new byte[SizeOfBuffer];
                int read;
                while ((read = input.Read(buffer, 0, buffer.Length)) > 0)
                {
                    output.Write(buffer, 0, read);
                }
            }
        }
    }
}

...over the template code and save the project.

Step 3: Change the target framework of the class library to .NET 3.5

Select "AES256 Properties" from the "Project" menu.

 Browse to and select the ".NET Framework 3.5" target framework in the "Application" tab of the properties configuration panel.

 Click the "OK" button in the "Target Framework Change" dialog box.

Step 4: Create a strong name key file

Open a command console window, create a directory named "C:\AES256," move to the "C:\AES256" directory, then create a strong name key file with the command "sn -k keyfile.snk."

Browse to the "C:\AES256"  directory in Windows Explorer to confirm the existence of the key file.

Step 5: Configure the "Signing" parameter of the compiler to sign the class library with the key

Select "AES256 Properties" from the "Project" menu.

 Click on the "Signing" tab of the properties configuration panel, then browse to the "C:\AES256" folder and select the "keyfile.snk" key file.

Step 6: Compile the C# class library to an assembly

 Select "Build Solution" from the "Debug" menu.

 "Build succeeded" will be displayed in the status bar when the project has been compiled.

Step 7: Copy the resource file 'gacutil.exe' to the folder C:\AES256\

 Download the attached resource file "gacutil.zip" and extract the file "gacutil.exe" to...

...the "C:\AES256" folder. 

8. Browse to the location of the compiled AES256.dll assembly and copy it to the C:\AES256\ folder

 Brows to the "\bin\Debug\" folder of the C# project and copy the AES256.dll file...

 ...to the "C:\AES256\" folder.

9. Add the AES256.dll assembly to GAC with the gacutil utility

 Open a command console, move to the "C:\AES256\" folder, then execute the command "gacutil.exe -i AES256.dll."

 The message "Assembly successfully added to the cache" should appear.

10. Confirm the addition of AES256.dll to the Global Assembly Cache (GAC)

Open Windows Explorer and browse to "C:\WINDOWS\assembly\" to confirm the addition of the AES256 assembly to the Global Assembly Cache (GAC)

11. Create an SSIS project in Business Intelligence Development Studio

 A default SSIS package named Package.dtsx is automatically generated when you create a new SSIS project.

12. Add a C# script task to the SSIS package

Drag-and-drop a script task onto the control flow panel of the default SSIS package.

13. Add "password" and "salt" variables to the SSIS package

Add password and salt variables to the SSIS package.

Go to https://www.grc.com/passwords.htm and generate your own 64-character strings for use as password and salt values, or use these two values:

password: E2DC3D431AC56AD73DC3711F554EB287691E645AAA2A8D41AFD48A5986769E4E

salt: 79949D576FB7F4B8AAEB48671E8C9C99B5289A434300ED1A7B3100DE402E3E28

Double-click on the script task component to bring up the Script Task Editor. Add the read-only variables "User::password" and "User::salt" and then click on the Edit Script button...

 ...to bring up the default C# code in the text editor. 

14. Add a reference to the AES256 assembly in the script task

Select "Add Reference" from the "Project" menu...

...to bring up the Add Reference dialog box.

Browse to and select the "C:\AES256\AES256.dll" assembly and click the OK button.

15. Add a "using" reference to the AesApp.Rijndael namespace of the AES256 assembly in the script task

Add the "using AesApp.Rijndael;" namespace reference and delete all the default commented code.

16. Paste C# code into the script task

Replace the Main() function code in the script task with the following code:

public void Main()
{
    string password = Dts.Variables["password"].Value.ToString();
    string salt = Dts.Variables["salt"].Value.ToString();

    Cryptology.EncryptFile(@"C:\AES256\test.txt", @"C:\AES256\test.txt.enc", password, salt);

    string md5sum;

    using (var md5 = System.Security.Cryptography.MD5.Create())
    {
        using (var stream = System.IO.File.OpenRead(@"C:\AES256\test.txt.enc"))
        {
            md5sum = BitConverter.ToString(md5.ComputeHash(stream));
        }
    }

    string[] line = { md5sum };
    System.IO.File.WriteAllLines(@"C:\AES256\md5sum.txt", line);

    Cryptology.DecryptFile(@"C:\AES256\test.txt.enc", @"C:\AES256\test.txt.enc.dec", password, salt);

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

17. Change the script task target framework to .NET 3.5.

 Select "Properties" from the "Project" menu of the script task text editor.

Select ".NET Framework 3.5" from the Target Framework drop-down menu and click "Yes" in the Target Framework Change popup message, then close the script task text editor.

18. Copy the test file to the C:\AES256\ folder

Download the attached "test.txt" resource file to the C:\AES256\ folder...

 ...and open the file to inspect its contents.

19. Execute the SSIS package

Click the "Start Debugging" button... 

...to execute the SSIS package.

20. Confirm that files named test.txt.enc, test.tst.enc.dec and md5sum.txt have been generated

Browse to the C:\AES256\ folder to confirm that the encrypted file, the file containing the md5sum of the encrypted file, and the decrypted file have been created.

21. Open test.txt.enc to confirm that it is a binary file

When you open test.txt.enc file all you see are unprintable characters, indicating that it is a binary file.

22. Open test.txt.enc.dec to demonstrate that the text.txt.enc file has been decrypted

The decrypted version of the encrypted file looks identical to the original file.

23. Open the md5sum.txt file to confirm that the checksum string has been generated

The md5sum.txt file contains a checksum.

Summary

This article has demonstrated how to compile C# encryption code to a CLR assembly and how to use that encryption code in an SSIS package.

Resources:

gacutil.zip | AES256.dll | test.txt
Total article views: 2988 | Views in the last 30 days: 66
 
Related Articles
FORUM

Creating assembly

I can no longer create assemblies on the sql server

FORUM

Create GAC Assembly

How To Create Assembly from GAC and how to Deal with referenced assembly

FORUM

Errors on creating assembly from global dlls

Errors on creating assembly from global dlls

FORUM

Cannot create assembly referencing system.core v3.5.0.0

Fail to create assembly using Linq

FORUM

CLR Assemblies in Replication

64-bit CLR Assembly in Publication, what happens during initialization?

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones