| 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
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. |
|
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
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. |
|
|
|
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 configsee how to encrypt column data herehttp://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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
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? |
|
|
|
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? |
|
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
Change deployed package connection password?