none
Editing SSIS package and replacing Password - can it be encrypted?

    Question

  • We have an SSIS package that has a connecton string to to an iSeries DB2 and it needs an entered password. We ship this package (*.dstx in XML) file out to clients with a vb.net utility to enter their iSeries IP, Userid and password information. The vb.net program then edits the dstx file with this information and then starts the Deployment Wizard (might change to DTUTIL, but not sure). But the password is in plain text in the dstx file now. Yes I can remove it after the Wizard is done. But wondering is there a way to encrypt the password in the dstx file, from within vb.net - or really from anywhere? obviously it needs to be encrypted so that either DTUTIL or Deployment Wizard (start the Manifest file) can recognize it.
    Friday, July 26, 2013 12:19 PM

All replies

  • What do you mean by "password is in plain text"? What is the package protection level? By default, sensitive information, password is encrypted with the userid of the person who developed the package unless the package protection level is changed.

    Nitesh Rai- Please mark the post as answered if it answers your question

    Friday, July 26, 2013 12:27 PM
  • Unfortunately, I am not able to connect to the system with BIDS and the vb.net application right now. At the client site, the password for the connection string is edited by the vb.net program. It inserts the site specific password into the dstx file. I believe the vb.net program does change the package protection from 5->2 or maybe 2->5. The vb.net program needs to be able to change the userid password for the connection string, but i would like for it to be able to have it encrypted in the dstx file. Is that possible?

    Friday, July 26, 2013 12:58 PM
  • You can encrypt the iSeries DB2 password with a password by setting the package protection level to "Encrypt Sensitive with Password".

    But, as you said that the user id and password is changed for the connection manager, why you want to save the password in the package?

    Once you have designed and verified/tested the package, set the protection level to "Don't Save Sensitive" and send it to client.


    Nitesh Rai- Please mark the post as answered if it answers your question

    Friday, July 26, 2013 1:07 PM
  • I must not be describing the process correctly.

    1) dstx file, manigest, and vb.net program are sent to the client

    2) client executes vb.net program which produces a form where the client can enter as400 ip, as400 userid, and as400 password.

    3) vb.net program takes these three peices of information and edits the dstx file. so after this editting the password is in the dstx file in plain text. i want the vb.net program to insert the password into the dstx file encrypted.

    4) vb.net program starts the manifest file which executes the Package Installation Wizard. I might change the vb.net to execute DTUTIL instead, but not sure about that yet.

    5) at the very end, the vb.net program removes the password from the dstx file. but my issue is still that for a moment in time, the password is in plain text in the dstx file when it is sent to the Package Installation Wizard to deploy.

    Friday, July 26, 2013 1:35 PM
  • Why not use an external package configuration?

    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, July 26, 2013 2:15 PM
  • sorry, i do not know what "external package configuration" means. We have many clients and do not know what are their servers, userids, passwords etc. we are trying to deploy packages to 2005, 2008 and 2012 sql servers.
    Friday, July 26, 2013 3:25 PM
  • and WE do not know what are their servers, userids, passwords etc
    Friday, July 26, 2013 4:12 PM
  • You don't need to store the connection details inside the package.  You can use an external file, a database table, job step configuration in SQL Agent, or the SSIS catalog (new in SQL 2012).

    See: Best Practices for Integration Services Configurations

    David


    David http://blogs.msdn.com/b/dbrowne/


    Friday, July 26, 2013 4:13 PM
  • I have read that but obviously my knowledge is limited and I stumble at how does the configurations/parameters get applied to the package. What is the process that a client would have to do? BTW - All the packages are run on a SQL Agent Job schedule.

    Ok, so if the package is deployed without the connection string informatino (userid, password, IP) etc

    1) external file. How would the client set the configuration/external file pathj associated with the package. Again, the client would have to designate the path because we do not know it.

    2) database table - at this time we would not want to setup a new table at each of the client sites.

    3) step job configuations - i looked at the package properties and did not see a place that you could put in configuration/parameters. How is this done?

    4) our solution must accomodate 2012, 2008, 2005

    thanks for your patience.

    Friday, July 26, 2013 6:26 PM
  • In that case I would look at setting the values in the Agent job step configuration. How are you creating the Agent job step to run the package today?

    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, July 26, 2013 8:39 PM
  • Good point. The Agent job is setup by our team. It is a manual process. But the real issue is the changing of server IPs and changing of passwords. Passwords use to be non-expiring but now they must be routinely changed to keep up with new security initiatives. So looking for a way for the clients to easily (even though they host the SQL Servers, many of them are not very technical) change the connection and passord information without engaging our team. Would also love for them to be able to test the connection after changing, but now I am just getting silly.

    Set up a BIDS with configurations and double clicked on the manifest file and the Package Installation Wizard allowed me to enter the configurations. So the package would have to be redeployed to change the configurations, but this seems like an option. It is easy to have a dtsx, configuration and manifest together at one time in the deployment folder. But it is difficult to depend on the configuration to always be in a specific folder and they move things around.  

    I still don't understand you comment about having the configuration in the Agent job step. Where would the client enter the new IP or password that would be retained? 

    Friday, July 26, 2013 9:22 PM