Encrypt/Decrypt output & input for SSIS task

Question

I use SSIS packages to output the data from one system as Comma Separated Values, and then import that same data into a second system. I have a new requirement to encrypt the CSV text files that are generated by the SSIS package, which means I will also need to decrypt those text files when importing into the other system.

This encrypting/decrypting could be done using some sort of batch processing outside of SSIS, but it seems like it would be more natural to do this all within SSIS. What's the best way to do this?

• user507: Are you referring to simply encrypting the traffic or the data in your source is going to be encrypted?
• Slider345: I need to encrypt the text files the SSIS package creates from my database. Then at a later point I'll have to decrypt the text files and import them using another SSIS package.
• user507: What level of encryption is needed for these files?
• Slider345: I don't have a specific requirement for encryption level, but naturally want to make it as secure as possible.

Answer 1

Encrypting files through SSIS is possible, but there is no real easy native solution available with SSIS. There are a wealth of third party productions out there than can assist with this. Time is money, so it all depends on how much you want to spend on trying to come up with a customized solution or just go buy something that can help you do it quicker.

There is a good write up on this blog post that walks thorugh doing it with some custom .NET code and a ScriptTask. He also list out some of the third party products that offer a solution as well.

Answer 2

If you are using SQL Server 2005+ on both ends, you can use Database Encryption.

This article by Pinal Dave gives a good overview.

You will basically have to setup a server master key, a database master key, and a symmetric key on both sides and use those to encrypt / decrypt the data going in and out using the ENCRYPTBYKEY() and DECRYPTBYKEY() functions. The trick is that you want to make sure once your keys are created DMK and SYMKEY that you restore them to your destination so the data can be decrypted.

That's a very high level, but it should get you started in the right direction.

• Slider345: ENCRYPTBYKEY() and DECRYPTBYKEY() seem to operate on columns individually. This would result in alot of work to encrypt all the columns one by one, which I suppose I could do if necessary. But I'm really looking for a way to encrypt the entire output file, not just a column.
• Eric Higgins: Encrypt the entire DB
• Alf47: You can use TDE if you are using SQL Enterprise edition which would keep the database encrypted but unfortunately the data coming out would not be encrypted.

Answer 3

You may want to look into PGP/GPG. While it is an "outside the DB server" solution, it can be made secure.