encrypting and decrypting passwords in SSIS

Question

  Below is the high level steps for encrypting and decrypting passwords in SSIS,

1. Store the Password in encrypted format in data setup table
2. Define a SSIS Variable called “EncryptionKey” in package level
3. Use Execute SQL Task , Get the encrypted value from database and store the value in this “EncryptionKey”.
4. Use Script component to decrypt the password from the SSIS Variable “EncryptionKey” and pass this value (Dynamically) to the connection string property of connection manager.

Please help me how to accomplish the above tasks.

Answer 1

Decrypt is impossible, it's hashed. But there is a way to reuse your saved password

...

If you choose "EncryptSensitiveWithPassword", you can pass your new package password as a parameter to the dtexec program. Check out this link, http://msdn.microsoft.com/en-us/library/ms138023.aspx and look at the /decrypt option.

 

here is Jamie's blog post from a while back that might help you and your team make a decision:

http://blogs.conchango.com/jamiethomson/archive/2007/04/26/SSIS_3A00_-Storing-passwords.aspx

...

---

Regards, Nighting Liu

Answer 2

Hi,

I couldn't find system encrypt function in sql server 2005. Please see the below link for encrypt and decrypt functions.

http://www.sql-server-helper.com/functions/string-encryption.aspx

Sri krishna

---

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you