Encrypting Data Through Script Components

So far, most of this chapter has focused on the Script Task, which is in the control flow of your package. In the data flow, you use a Script Component, which morphs into a Script Source, Transform, or Destination. The code that’s stubbed out for each of the components varies based on what role you want the Script Component to play. More information on this can be seen in the Professional SQL Server 2005 Integration Services book. This section shows you how to use the power of the data flow to transform data into an encrypted form.

As the world of technology matures, the unfortunate reality is that new methods of hacking mature as well. In earlier versions of SQL Server, there was nothing that handled encryption. Within SQL Server 2005, however, there is built-in functionality that handles encryption and decryption of data. Obviously, certain types of data should not be left in plain text within a database (data such as credit card numbers and Social Security numbers, for example). Wouldn’t it be nice to have a component that handles encryption as well?

SSIS does not have a component that handles encryption and decryption out-of-the-box. However, the Script Component can be extended to use a custom library that encapsulates the encryption/decryption for you. The component can then be reused within other packages.

When using encryption, you should understand how it works, as well as what types of encryption are available. There are two types of encryption: symmetric and asymmetric.

Symmetric encryption means that there is one key that is shared by two different parties for encrypting and decrypting data. For example, if Party A needs to send encrypted data to Party B, then both parties must know a private key that can be used to encrypt and decrypt any data that is sent.

Asymmetric encryption uses a key pair. One key is private and one key is public. Party A sends a message request along with a public key to Party B. Party B can then encrypt a message over the wire with the public key supplied, so it cannot be compromised. After the message has been encrypted with the public key, it is then sent to Party A. The message can now be decrypted by Party A using the private key. The important part is that a public key can be re-created each time, thus changing the private key in case it is ever compromised.

The following table describes some well-known encryption algorithms.

Open table as spreadsheet

Encryption Algorithm	Description	Key Size
Data Encryption Standard (DES)	Created by IBM, National Security Agency (NSA), and others as a standard back in the 1970s. In 2000, it was broken because of the growth in technology.	56 bits
Triple DES (3DES)	Superseded DES by implementing the DES algorithm three times over, therefore providing the name 3DES. Encryption experts feel that its days are numbered. However, it has not been broken.	156 bits
Rijndael (AES)	Advance Encryption Standard (AES) was created by Joan Daemon and Vincent Rijmen, and is currently the standard of the United States government. The algorithm used is called Rijndael.	126,192, and 256 bits (Standard)

Listing 2-3 is a static class that uses Rijndael’s encryption algorithm. Since this is another example of custom code, it has been written with C# to show that it does not matter what language is used, as long as custom assemblies are written with .NET code. Listing 2-3 contains two public properties: Key (the symmetric key) and IV (the Initialization Vector). These properties can be generated by calling the CreateSymmetricKey() method. The properties can also be passed in using the two methods, Encrypt() and Decrypt(), along with the data to be encrypted or decrypted.

Listing 2-3: C# Code Using the Rijndael Encryption Algorithm

  using System; using System.Collections.Generic; using System.Text; using System.Security; using System.Security.Cryptography; using System.IO; namespace SSIS.Utilities {     static public class SecuringData     {         static private byte[] m_key;         static private byte[] m_iv;         /// <summary>         /// Holds the Symmetric Key         /// </summary>         static public string Key         {             get             {                 return Convert.ToBase64String(m_key);             }             set             {                 m_key = Convert.FromBase64String(value);             }         }         /// <summary>         /// Holds the Init Vector         /// </summary>         static public string IV         {             get             {                 return Convert.ToBase64String(m_iv);             }             set             {                 m_iv = Convert.FromBase64String(value);             }         }         /// <summary>         /// Sets a key and iv for encryption         /// </summary>         /// <param name="key"></param>         /// <param name="iv"></param>         static public void CreateSymmetricKey(string key, string iv)         {             Key = key;             IV = iv;         }         /// <summary>         /// Creates a key and IV for encryption         /// </summary>         static public void CreateSymmetricKey()         {             Rijndael RhineDal = new RijndaelManaged();                          try             {                 RhineDal.GenerateKey();                 RhineDal.GenerateIV();                 m_key = RhineDal.Key;                 m_iv = RhineDal.IV;             }             catch (CryptographicException)             {                 throw;             }             finally             {                 RhineDal.Clear();             }         }         static public void Encrypt(ref string Data,                                 string key,                                 string iv)         {             Rijndael RhineDal = new RijndaelManaged();             CryptoStream cs = null;             MemoryStream ms = null;             Byte[] byt;             try             {                 if(key!=null)                     Key = key;                 if(iv!=null)                     IV = iv;                 KeySupplied();                 RhineDal.Key = m_key;                 RhineDal.IV = m_iv;                 ICryptoTransform RndlEncrypt = RhineDal.CreateEncryptor();                 byt = Encoding.UTF8.GetBytes(Data);                 ms = new MemoryStream();                 cs = new CryptoStream(ms, RndlEncrypt, CryptoStreamMode.Write);                 cs.Write(byt, 0, byt.Length);                 cs.FlushFinalBlock();                 cs.Close();                 Data = Convert.ToBase64String(ms.ToArray());             }             catch (ApplicationException)             {                 throw;             }             catch (CryptographicException)             {                 throw;             }             catch (Exception)             {                 throw;             }             finally             {                 RhineDal.Clear();             }         }         static public void Decrypt(ref string EncryptedData,                                string key, string iv)         {             Rijndael RhineDal = new RijndaelManaged();             CryptoStream cs = null;             MemoryStream ms = null;             Byte[] byt;             try             {                 if (key != null)                     Key = key;                 if (iv != null)                     IV = iv;                 KeySupplied();                 RhineDal.Key = m_key;                 RhineDal.IV = m_iv;                 ICryptoTransform RndlEncrypt = RhineDal.CreateDecryptor();                 byt = Convert.FromBase64String(EncryptedData);                 ms = new MemoryStream();                 cs = new CryptoStream(ms, RndlEncrypt, CryptoStreamMode.Write);                 cs.Write(byt, 0, byt.Length);                 cs.FlushFinalBlock();                 cs.Close();                 EncryptedData = Encoding.UTF8.GetString(ms.ToArray());             }             catch (ApplicationException)             {                 throw;             }             catch (CryptographicException)             {                 throw;             }             catch (Exception)             {                 throw;             }             finally             {                 RhineDal.Clear();             }        }        static private void KeySupplied()        {            if (m_iv == null || m_key == null)                throw new ApplicationException("A Key and IV must be supplied");        }     } } 

After the SecuringData class is compiled (using a new C# class library project) and added to the GAC, it is ready to be referenced just as you did earlier in this chapter. Create or open an existing SSIS package within Visual Studio and add a new Data Flow Task. Create a new ADO.NET Connection Manager that connects to the AdventureWorks database. In the data flow, add a new Data Reader Source. Double-click the Data Reader Task to configure it. Figure 2-13 shows how the Data Reader Source should be configured to reference the AdventureWorks Connection Manager.

Figure 2-13: Creating a new connection manager that points to the AdventureWorks database

The Data Reader Source is the source for all records from the Person.Address table, where the person lives in the city of Bothell, and will initiate the data flow.

Prior to opening the Data Reader Source, create two new string variables, one called Key and the other called IV (which stands for Initialization Vector). These two variables will be used later to store the key that SSIS will use to encrypt the data. The values of each variable can be random bytes of data. To get started, use THk5QMKX1HGVzSo8Pq7X6y1q6IjnVqR/inuZsaagVpY= as Key and bjsLiMoy6/mUsvuMdjMWhA== as IV, as shown in Figure 2-14.

Figure 2-14: Creating the Key and IV variables

With the plumbing now complete, open the Data Reader Source, and in the Component Properties tab, set the SqlCommand property for the Data Reader Source to the following SELECT statement:

  SELECT * FROM         Person.Address WHERE     (City = 'Bothell') 

The Script Component can now be added (select Transformation as the script type after you drag it onto the design surface) and wired to the Data Reader Source component by using the green arrow. Double-click on the Script Component, and in the Input Columns page, check AddressLine1 to pass it into the script and select ReadOnly for the usage type. Any columns you select here can be accessed inside the script and their values can be used in calculations or whatever the script needs them for. Setting their usage to ReadOnly means the column values cannot be changed in the script; setting them to ReadWrite allows the script to both read the current values of the column and then update them, too. Lastly, any columns you don’t check here just pass right through the transform untouched.

There is an Add Output button that can be pressed to add new output columns and paths from the transform (additional green arrows). In this case, use the existing Output 0 output. When you select the Output Columns folder, the Add Column button can be clicked to add new columns to be outputted from the transform. When creating a new column, be sure to set both the DataType property, which is the type of data the column will hold (Boolean, integer, string, and so on), and the Name property, which will be used to identify the output. For this example, create a new output column called EncAddressLine1, which is a 250-character string (see Figure 2-15). More space will be needed to store the AddressLine1 column after it’s encrypted, and the amount of space will vary based on the encryption type you use.

Figure 2-15: Output columns in the Script Transformation Editor

Note that, in this case, we have created what is called a synchronous transformation, meaning that as each row comes into the script, it is updated immediately with any new values (using the script code) and the new row is passed through to the output without copying any data. You could also have created an asynchronous transformation, which, in a nutshell, would mean that inside the script you actually copy each row from the input into the output. This copying would mean the operations would be slower, but the benefit is that the set of output columns could be quite different from the set of input columns in terms of number, type, and size.

To add the script that follows, select the Script page in the left pane of the Script Transformation Editor. The Key and Initialization Vector (IV) variables you created earlier must be passed into the custom assembly from the component. The property ReadOnlyVariables should be set to Key,IV (without spaces between the two variables) so that the script recognizes them as variables.

Also note that the custom assembly SecuringData has a CreateSymmetricKey() method. This can be called as well to create these parameters. However, just as with testing the assemblies within a Windows project, the SecuringData object could be embedded within the Windows app, which could then call the CreateSymmetricKey() method to display the generated key and IV on the form.

The script can now be added by clicking the Design Script button. When the Visual Studio Script Editor launches, paste the following code (available at www.wrox.com). After adding the code, add a reference to the code by right-clicking on References Add Reference and selecting the name of the file created from Listing 2-3 that was added to the GAC earlier.

  Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports SSIS.Utilities Public Class ScriptMain     Inherits UserComponent     Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)         'Encrypt AddressLine1 and save the result to EncAddressLine1         Row.EncAddressLine1 = EncryptValue(Row.AddressLine1)         'Then blank out the original (unencrypted) value from AddressLine1         Row.AddressLine1_IsNull = True     End Sub     Private Function EncryptValue(ByVal RowVal As String) As String         SecuringData.Encrypt(RowVal, Me.Variables.Key, Me.Variables.IV)         Return RowVal     End Function End Class 

Finally, add a Flat File Destination component and wire it up to the Script Component by dragging the green arrow and connecting it (see Figure 2-16). This component will accept the transformed or encrypted data, and write it to a specified file. Double-click the component to set the properties of what columns to write, as well as the type of file and where the file will be written (of course, there is no need to write the AddressLine1 column to the file since every one of these values has been ensured to be null inside the script-but you do want to write out the EncAddressLine1 columns). The package is now ready to be executed.

Figure 2-16: Adding a Flat File Destination component

If you open the text file where you saved the encrypted data, you should see that all of the EncAddressLine1 values are now encrypted, as shown in Figure 2-17.

Figure 2-17: Viewing all EncAddressLine1 values encrypted

Similar code can be used to decrypt the data. To see how to decrypt the file in the pipeline, you could use the same steps, but this time, make the flat file the source. The code then for the Script Component would look like the following:

  Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports SSIS.Utilities Public Class ScriptMain     Inherits UserComponent     Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)         'Decrypt EncAddressLine1 and save the result to AddressLine1         Row.AddressLine1 = DecryptValue(Row.EncAddressLine1)         'Then blank out the original (encrypted) value from EncAddressLine1         Row.EncAddressLine1_IsNull = True     End Sub     Private Function DecryptValue(ByVal RowVal As String) As String         SecuringData.Decrypt(RowVal, Me.Variables.Key, Me.Variables.IV)         Return RowVal     End Function End Class