I want to encrypt a column with sensitive data. How do I do that in SSIS?
Solutions
A) If your source is a SQL Server table, you could use T-SQL to encrypt your data.
B) An other option is the Script Component with some .Net coding to encrypt your sensitive data.
Note: see this post for encrypting a whole file instead of certain columns.
Let's elaborate that Script Component solution. Let's assume you have an user table that you want to export to a csv file, but you don't want to export the password (or salary) column in plaintext.
Usertable in SQL Server |
1) Variable
Create a string variable named EncryptionKey to store your encryption key in and give it a value.
Rightclick in the Control Flow |
2) Data Flow Task
Create a new Data Flow Task with a source connection to your table. This example has two columns: Name and Password (see screenshot above step 1).
Data Flow Task with source |
3) Script Component
Add a Script Component after the source and choose Transformation. Connect it to the source and give it a suitable name.
Script Component Type Transformation |
4) Select Variable
Edit the Script Component and select the variable EncryptionKey from step 1 as a ReadOnlyVariable.
ReadOnlyVariables |
5) Input Column
Go to the Input Columns section and select the column that you want to encrypt as a ReadOnly input column.
Readonly input column |
6) Output Column
Go to the Inputs and Outputs section and create a new output column to store the excrypted password in. The encypted string will be larger than the original string so make sure it will fit.
New output column named EncryptedPassword |
7) The Script
Hit the Edit Script button and remove the PreExecute and PostExecute methods because we don't need them. Copy the code below. I have created two new static methods (Encrypt and Decrypt) that will do the job. They are simplified versions of these examples at CodeProject. If you want to use these scripts commercially, make sure you read the documentation and test it thoroughly! I don't take any responsibility whatsoever.
// C# code using System; using System.Data; using System.IO; // Added using System.Security.Cryptography; // Added using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper; using System.Windows.Forms; [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { public override void Input0_ProcessInputRow(Input0Buffer Row) { // Fill the new column with an encrypted version of the password column. Row.EncryptedPassword = Encrypt(Row.Password.ToString(), this.Variables.EncryptionKey.ToString()); } // Encrypt text with Rijndael encryption public static string Encrypt(string clearText, string Password) { // Convert password string into byte array byte[] clearBytes = System.Text.Encoding.Unicode.GetBytes(clearText); // Create Key and IV from the password with salt technique PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 }); // Create a symmetric algorithm with Rijndael Rijndael alg = Rijndael.Create(); // Set Key and IV alg.Key = pdb.GetBytes(32); alg.IV = pdb.GetBytes(16); // Create a MemoryStream MemoryStream ms = new MemoryStream(); // Create a CryptoStream CryptoStream cs = new CryptoStream(ms, alg.CreateEncryptor(), CryptoStreamMode.Write); // Write the data and make it do the encryption cs.Write(clearBytes, 0, clearBytes.Length); // Close CryptoStream cs.Close(); // Get Encypted data from MemoryStream byte[] encryptedData = ms.ToArray(); // return the Encypted data as a String return Convert.ToBase64String(encryptedData); } // Decrypt text with Rijndael encryption public static string Decrypt(string cipherText, string Password) { // Convert password string into byte array byte[] cipherBytes = Convert.FromBase64String(cipherText); // Create Key and IV from the password with salt technique PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 }); // Create a symmetric algorithm with Rijndael Rijndael alg = Rijndael.Create(); // Set Key and IV alg.Key = pdb.GetBytes(32); alg.IV = pdb.GetBytes(16); // Create a MemoryStream MemoryStream ms = new MemoryStream(); // Create a CryptoStream CryptoStream cs = new CryptoStream(ms, alg.CreateDecryptor(), CryptoStreamMode.Write); // Write the data and make it do the decryption cs.Write(cipherBytes, 0, cipherBytes.Length); // Close CryptoStream cs.Close(); // Get Decypted data from MemoryStream byte[] decryptedData = ms.ToArray(); // return the Decypted data as a String return System.Text.Encoding.Unicode.GetString(decryptedData); } }
Or VB.Net
' VB.Net code Imports System Imports System.Data Imports System.IO ' Added Imports System.Security.Cryptography ' Added Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper <Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _ <CLSCompliant(False)> _ Public Class ScriptMain Inherits UserComponent Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) ' Fill the new column with an encrypted version of the password column. Row.EncryptedPassword = Encrypt(Row.Password.ToString(), Me.Variables.EncryptionKey.ToString()) End Sub ' Encrypt text with Rijndael encryption Public Shared Function Encrypt(ByVal clearText As String, ByVal Password As String) As String ' Convert password string into byte array Dim clearBytes As Byte() = System.Text.Encoding.Unicode.GetBytes(clearText) ' Create Key and IV from the password with salt technique Dim pdb As New PasswordDeriveBytes(Password, New Byte() {&H49, &H76, &H61, &H6E, &H20, &H4D, &H65, &H64, &H76, &H65, &H64, &H65, &H76}) ' Create a symmetric algorithm with Rijndael Dim alg As Rijndael = Rijndael.Create() ' Set Key and IV alg.Key = pdb.GetBytes(32) alg.IV = pdb.GetBytes(16) ' Create a MemoryStream Dim ms As MemoryStream = New MemoryStream() ' Create a CryptoStream Dim cs As CryptoStream = New CryptoStream(ms, alg.CreateEncryptor(), CryptoStreamMode.Write) ' Write the data and make it do the encryption cs.Write(clearBytes, 0, clearBytes.Length) ' Close CryptoStream cs.Close() ' Get Encypted data from MemoryStream Dim encryptedData As Byte() = ms.ToArray() ' return the Encypted data as a String Encrypt = Convert.ToBase64String(encryptedData) End Function ' Decrypt text with Rijndael encryption Public Shared Function Decrypt(ByVal cipherText As String, ByVal Password As String) As String ' Convert password string into byte array Dim cipherBytes As Byte() = Convert.FromBase64String(cipherText) ' Create Key and IV from the password with salt technique Dim pdb As New PasswordDeriveBytes(Password, New Byte() {&H49, &H76, &H61, &H6E, &H20, &H4D, &H65, &H64, &H76, &H65, &H64, &H65, &H76}) ' Create a symmetric algorithm with Rijndael Dim alg As Rijndael = Rijndael.Create() ' Set Key and IV alg.Key = pdb.GetBytes(32) alg.IV = pdb.GetBytes(16) ' Create a MemoryStream Dim ms As MemoryStream = New MemoryStream() ' Create a CryptoStream Dim cs As CryptoStream = New CryptoStream(ms, alg.CreateDecryptor(), CryptoStreamMode.Write) ' Write the data and make it do the decryption cs.Write(cipherBytes, 0, cipherBytes.Length) ' Close CryptoStream cs.Close() ' Get Decypted data from MemoryStream Dim decryptedData As Byte() = ms.ToArray() ' return the Decypted data as a String Decrypt = System.Text.Encoding.Unicode.GetString(decryptedData) End Function End Class
8) The result
Now finish the Data Flow by adding a destination. I added a Data Viewer to see the result.
The result: encrypted data |
9) Reverse
By reversing the Data Flow we can decrypt the data.
The result: decrypted data |
// C# code public override void Input0_ProcessInputRow(Input0Buffer Row) { // Fill the new column with the decrypted version of the encrypted password column. Row.DecryptedPassword = Decrypt(Row.Password.ToString(), this.Variables.EncryptionKey.ToString()); }
Or VB.Net
' VB.Net code Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) ' Fill the new column with the decrypted version of the encrypted password column. Row.DecryptedPassword = Decrypt(Row.Password.ToString(), Me.Variables.EncryptionKey.ToString()) End Sub
Note: This example uses Rijndael, but there are more methods available in .Net
I'm getting this error while decrypting the password using VB script
ReplyDeletePadding is invalid and cannot be removed.
at System.Security.Cryptography.RijndaelManagedTransform.DecryptData(Byte[] inputBuffer, Int32 inputOffset, Int32 inputCount, Byte[]& outputBuffer, Int32 outputOffset, PaddingMode paddingMode, Boolean fLast)
at System.Security.Cryptography.RijndaelManagedTransform.TransformFinalBlock(Byte[] inputBuffer, Int32 inputOffset, Int32 inputCount)
at System.Security.Cryptography.CryptoStream.FlushFinalBlock()
at System.Security.Cryptography.CryptoStream.Dispose(Boolean disposing)
at System.IO.Stream.Close()
at SC_33f43246bfb44a03a096ca265c35b8ed.vbproj.ScriptMain.Decrypt(String cipherText, String Password)
at SC_33f43246bfb44a03a096ca265c35b8ed.vbproj.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)
at SC_33f43246bfb44a03a096ca265c35b8ed.vbproj.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)
at SC_33f43246bfb44a03a096ca265c35b8ed.vbproj.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
@Anonymous: check out this forum link. Let me know if it worked.
ReplyDeleteCan somepne provide the code for RSA encryption?
ReplyDeleteThanks
@Anonymous: here
ReplyDeleteI am getting this error message when I use this script for my tutorial.
ReplyDelete"The value is too large to fit in the column data area of the buffer"
at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)
at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32 columnIndex, Object value)
at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32 ColumnIndex, Object value)
at ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)
at UserComponent.Input0_ProcessInput(Input0Buffer Buffer)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
Please advice
With the encrypt script in the Script Component you create a new column that is significantly larger then the original column so you can't replace the original column. It won't fit and will give you an error (Except when your source datatype is already very large, but the actual values are very small).
DeleteHello,
ReplyDeleteWhen I use the Script component to encryt the Data using SSIS. How i can use SQL query in the SQl server 2012 Database, to get the real (decrypted) Data.
Check the link above on tsql encryption. If you want to use SSIS to encrypt with Rijndael abd TSQL to decrypt then you could check out this link: http://www.codeproject.com/Articles/15296/SQL-Encryption-and-Decryption-Library-Using-Rijnda.
DeleteHi,
ReplyDeleteMy doubt here is every time i run the code, does it Encrypt the value to same value ?
suppose if first time encrypted value is 268BMM for first row , will it be same if i run the package second time.
Yes, otherwise the decrypt wont work... just try it by running it a couple of times
Deleteand what if i have null values??
ReplyDeleteYou can't encrypt 'nothing', so you either don't encrypt null values or you first give it a default value.
Deleteits possible decrypt directly in database? because my source is an excel and my target SQL server, and i dont want decrypt data in ssis.
ReplyDeleteYes, depending on the version.
Delete