encrypting column data while doing data transfer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

encrypting column data while doing data transfer

Author

Topic

ujjaval
Posting Yak Master

108 Posts

Posted - 2007-05-08 : 02:17:41

Hi,

I have a SSIS project where I am transferring data from DB2 table to SQL Server table. There is a column called REC_ID which I need to encrypt before we store it in SQL Server. Now, SQL Server has buildin encryption functionality and we need to use that as there are views that will decrypt this column and give data to authenticated users.

So, the question is, is there anyway that I can encrypt the column data in my SSIS package using my target SQL server database key and using SQL server encryptbykey function while transferring?

Thanks,
Ujjaval

prpball
Starting Member

19 Posts

Posted - 2007-05-08 : 11:46:25

If I understand you right you want to decrypt a column of data in sql by using an SSIS package? If this is your question then yes is the answer.

You have to do a few things first before you can use the encryption sql has. The column you want to encrypt must be a nvarchar and the length must be set to 128 or higher. This is because the encryption algorithm is 128 bit or higher encryption with numbers and letters, hince the nvarchar.

Create a new query on the target database

Create a master key by using the following syntax:
Create Master Key nameTheKey By Password = 'password'

Then you have to create a certificate:
Create Certificate certName with subject = 'whatever'

Then you have to choose which encryption you are going to use:
AES, DES ?
Then create your symmetric key
Create Symmetric Key keyName with Algorithm = AES_256 or DES
Encryption by Certificate certName

Now you can encrypt in the SSIS package
I like to use Execute T-Sql Statement Task because I am used to DTS

The syntax for encrypting is
Open Symmetric Key keyName Decryption by Certificate certName

Now run an update command to encrypt the column data
Update tableName Set REC_ID = EncryptByKey(Key_GUID('keyName'), columnName or parameterName)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources