January 12, 2009 at 11:09 pm
I have an SSIS configuration issue question. I appreciate if someone can address it. Let me explain it by presenting an example scenario –
A developer in a remote location who does not have access to the servers in my environment develops and send a package to me by email. The package simply copies a table from a source SQL server called MySource to a destination SQL server called MyDestination. MySource and MyDestination SQL servers are in different windows domains so the package has to use connections using SQL authentication with a userid and password.
Since developer has no access to MySource and MyDestination, he creates the package at his location using connection to his local version of source and destination servers.
The objective is to deploy the package to your SSIS server without opening the package in designer and altering the connections by entering the necessary credentials for MySource and MyDestination. I could use configuration files but I do not want the password to be saved in plain text. The package will be run as a SQL job.
Using a Package password to encrypt the sensitive information still requires the package password in plain text when run it from sql job. I can rely on sql server storage for encryption too but the bottom line is password cannot be in plain text. In old DTS you could use the disconnected edit option and re enter the credentials and package use to save the credentials in encrypted format. Users who have admin access to the server can reset the connections but they can never see the password. I find this functionality missing in SSIS.
Again I really appreciate reading my post and thanks
January 15, 2009 at 2:45 am
What you are trying to achieve is possible via SSIS configurations. That said the best way I have found to getting it working is to store the config info in a SQL server and encrypt the table, this will encrypt all values of all configurations but a securable view can then be created on top of the table that will decrypt and display the values if you have the appropriate permissions.
A good starting place can be found here:
http://curionorg.blogspot.com/2007/05/encrypted-sql-server-ssis.html
hope it helps
Chris
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply