Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Change deployed package connection password?

Author  Topic 

thebrenda
Starting Member

22 Posts

Posted - 2013-08-01 : 09:36:47
FYI - There is a long story and I am just picking out a small peice of it.

We have deployed packages at multiple client sites. One of the connections in the packages are to an ISeries host and the connectionstring has a userid and password. The packages were deployed with protectionlevel 5 - ServerStorage. there is no configuration file. The are deployed in MSDB.

Is there a way to change the password on the packages without redeploying? Will dtutil or dtexec (or some other utility) allow passsword changes of an already deployed package in MSDB?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-01 : 12:50:58
Nope...Not inside connections within packages. Ideally you should identify them as configuration items within packages and then you will be able to change values from outside through configurations either using XML file or SQL table updation depending on the config type chosen.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

thebrenda
Starting Member

22 Posts

Posted - 2013-08-01 : 13:15:51
I tried configurations but they did not work for me. The password was visible in plain text in the XML file and in the SQL Server SSIS Connections table. Yes you can use roles and permissions to limit their access, but plain text passwords will not fly in my company.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-01 : 13:26:15
quote:
Originally posted by thebrenda

I tried configurations but they did not work for me. The password was visible in plain text in the XML file and in the SQL Server SSIS Connections table. Yes you can use roles and permissions to limit their access, but plain text passwords will not fly in my company.


why not keep it in SQL Server table then and encrypt it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

thebrenda
Starting Member

22 Posts

Posted - 2013-08-01 : 16:07:35
Are you talking about a Package Configuration type of SQL Server? How would you encrypt the password? During a package execute (Agent Job) it woud automatically unencrypt it? How would you change the password value if it was encrypted? The whole idea is to make it easy to change the password and userid.

We would have multiple configuration fields: server, initial library, userid, password.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-02 : 01:49:17
quote:
Originally posted by thebrenda

Are you talking about a Package Configuration type of SQL Server? How would you encrypt the password? During a package execute (Agent Job) it woud automatically unencrypt it? How would you change the password value if it was encrypted? The whole idea is to make it easy to change the password and userid.

We would have multiple configuration fields: server, initial library, userid, password.


yes..I'm speaking of SQL Server package config

see how to encrypt column data here

http://www.mssqltips.com/sqlservertip/2431/sql-server-column-level-encryption-example-using-symmetric-keys/

you might have to write a view to retrieve the properties and values from within the table which will include the logic to decrypt column information too. Then this can be used in package in package config to assign values to properties.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

thebrenda
Starting Member

22 Posts

Posted - 2013-08-02 : 09:45:44
There are scheduled jobs that dtexec package to run. How would that work?
Go to Top of Page

thebrenda
Starting Member

22 Posts

Posted - 2013-08-02 : 09:47:56
I am assuming that with configuration file that when the package executes it reads the data from the configuration file to get the values? I played with XML and SQL Server stored configurations but am not an expert. I did make some changes to the XML file and it did not seem to change the execution of the MSDB stored package when it ran.

Guess my basic question is that with configuration files, when you make a change to the file you can immediately run the package and it should pick up the change?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-03 : 00:22:37
quote:
Originally posted by thebrenda

I am assuming that with configuration file that when the package executes it reads the data from the configuration file to get the values? I played with XML and SQL Server stored configurations but am not an expert. I did make some changes to the XML file and it did not seem to change the execution of the MSDB stored package when it ran.

Guess my basic question is that with configuration files, when you make a change to the file you can immediately run the package and it should pick up the change?


Yep..it will read values from configuration at runtime and assign it to various properties based on mapping.
When you make change to file you need to put latest version in the path where package reads it from ( check the path set inside SSIS package). Then the package will automatically read the changes from file and will map values accordingly.
If you use SQL Server based config, then you just need to update values in table and it will take new values during execution.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -