Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question
1  
Are you referring to simply encrypting the traffic or the data in your source is going to be encrypted? – Shawn Melton Oct 16 '12 at 19:56
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. – Slider345 Oct 16 '12 at 20:14
What level of encryption is needed for these files? – Shawn Melton Oct 16 '12 at 20:47
I don't have a specific requirement for encryption level, but naturally want to make it as secure as possible. – Slider345 Oct 16 '12 at 20:53

3 Answers

up vote 3 down vote

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.

share|improve this answer
up vote 0 down vote

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.

share|improve this answer
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. – Slider345 Oct 16 '12 at 20:01
Encrypt the entire DB – Eric Higgins Oct 16 '12 at 20:15
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. – Alf47 Oct 17 '12 at 3:01
up vote 0 down vote

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

share|improve this answer
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post. – RolandoMySQLDBA Jan 30 at 4:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.