February 8, 2019 at 1:19 pm
Greetings,
I've started working on a new set of SSIS packages that require the use of credentials (username/password) to access other datasources - namely Oracle and Teradata - as part of our ETL/ingest processes.
Another part of the requirement is implementing a round-robin approach to which user the package attempts to use (the passwords are all the same), which means that I can't statically set the username in the package or supply it via a package parameter - the work flow has to pull it from a control table at the start of the job.
Here's my question: is there a way to encrypt the password column in the control table so that it's not visible to anyone, but the service account executing the package is able to decrypt it at runtime? The idea being that the service account the SSIS package runs under would pull the username (round-robin) and password from the control table, and be able to get the decrypted password to pass along to the connection manager.
Long ago and far away I thought I read something about doing this with certificates, but I can't seem to find that anymore - either because it doesn't really exist, or my google-fu is failing me. Perhaps both?
Has anyone else had this situation, and if so, how did you resolve it?
February 8, 2019 at 1:58 pm
Are you storing the packages in the SSIS catalog? If so, set up a project or package parameter for the password, and then use an SSIS catalog environment to store the password. Marking is as Sensitive will encrypt it in the SSIS catalog table.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply