Setting Passwords In Connections Using Script Task

Setting Passwords In Connections Using Script Task

• Isa novaiel

  I have to retrieve the userid and password for each connection that exists from an external table, then decrypt the userid and password. It is unknown how many connections exist, so I must itterate the connections in the package, do a lookup in the table for that connections name, build the connection string, and update the connection.

  I am looping the DTS.Connections, and retrieving the name of the existing connection.

  
  

• Gurjeet

  If you using a property expression on the connection managers' ConnectionString then there is no need to loop over anything.

  What collection are you looping over And why

  -Jamie

  
  
  

• bmilano

  I have 6 - 10 connections, some Sql Server, some Oracle, and one excel......The configurations are across many different packages, and I need to be able to loop through the configurations within the package and update the connection strings dynamically by enviroment.

  I know I could use the Configuration settings for this, but my client doesn't want passwords held in unencrypted tables, or files. Due to the clients requirments, it seemed like the best way to accomplish this was to store encrypted values in a table, and write the decrypted values to the connections at run time, based on what connections were needed. I am not sure how that would be accomplished by variables and expressions without having a VERY complicated initialization routine. A single script that looped the connections, decrypted the connectstring, and updated the connections at the start of the package looked like a do-able solution, but as I said, I don't seem to be able to save the password within the connection string. Everything else is saved, and updates, and if I use integrated security, it works fine. It's only when I attempt to include the username and password in the connection string that I have issues. The funny thing here is also that within my script, if I attempt to connect using the connect string I create, it works, it's only when I attempt to read the string back from the connection and use it that it doesn't. It looks like the connection string is not saving the password when it's updated, although the rest of the data is in fact changed.

  And I am using the encryptwithpassword option, so I know that's not what's causing the problem of it now saving.

  
  

• jason414

  Microsoft have designed the product so that if you want to store passwords unencrypted then you must explicitly do that yourself, the product will not do it for you. e.g. When storing a connection string in a configuration file Microsoft will not put the password into the file - you have to edit it yourself. I would imagine that that is what is preventing you doing what you are trying to do here.

  I'm sure your opinion will be that as you are setting the password in this manner you should be allowed to do t and I am inclined to agree with you. I don't know of a way around this. I don't think its a bug - more an anomoly of the product.

  It'd be handy if someone from Microsoft were reading this to confirm or deny the above but unfortunately they don't seem to frequent this forum as much as they used to.

  Is there a reason that you cannot store the passowrds in the package in an encypted format This functionality is built into the product.

  Sorry I can't be of more help but I suspect you are stuck.

  -Jamie

  
  
  

• Forrestsjs

  ah, I see......let me try that then....I'll add the variables ,loop the connections in my script, set the variables for each connection needed, and see what happens.....
  

• imrash

  OK....tried it.....Nope, it still strips out the password......persistent little cuss.....
  

• abohmza

  I am open to the suggestion, especially if it actually saved the changed password for the connection.

  I get the setting of the variables, although I would have to have a script task to decrypt the userid and password anyway, and the setting up of the connection string with an exception, but what task item would you use to read and loop the connections and check for existance, and update the connection strings

  
  

• maymay

  Here is maybe a more complete picture of the scenario:

  Package 1 uses connections 1, 5, and 6

  Package 2 Uses connections 2, 5, and 6

  Package 3 uses connections 1,2,3, and 6

  ConnectString Table has info for connections 1,2,3,4,5,6

  In package 1, since I don't know what connections are being used, I itterate the collection and find 1, 5, and 6. I decrypt the entries from the table for these, and update the connections 1, 5, and 6.

  In package 2, again I don't know what connections are being used, I itterate the collection and find 2, 5, and 6. I decrypt the entries from the table for these, and update the connections 2, 5, and 6.

  In package 3, again I don't know what connections are being used, I itterate the collection and find 1, 2, 3, and 6. I decrypt the entries from the table for these, and update the connections 1, 2, 3, and 6.

  The bottom line here, is still that I am not able to set the password, or find a reference to the password. If you look at the class, there is no property for it, and entering it in the connection string doesn't work. I can itterate the connections, get the correct data, unencrypt it, build the connection string, etc.....I just can't set the password. What am I missing There HAS to be a way to do it. An interesting note here, is that if I attempt a connection with the string I just built, it works fine. It's only when I update the connection managers connection and try to use that, that it fails with incorrect login for user such and such.

  
  

• SriDirectX

  You don't. You just need an expression on the connection string of the connection manager.

  You only have a max 10 connection managers - is it worth looping Why not just set each one seperately

  -Jamie

  
  
  

• vic0824

  Seems like every way is a dead end doesn't it Shame really.

  Encrypted config files are something I know Microsoft are looking at for the next version.

  In the meantime, how about using config files and restricting access to the storage folder

  -Jamie

  
  
  

• debug13

  Cathan wrote:

  I am not sure how that would be accomplished by variables and expressions without having a VERY complicated initialization routine.

  Would it be any more complicated than code in a script task Your expression would look something like the following I should think:

  "Server=myserver;UserName=;MyUsername;Password=" + @[User::VarContainingPassword] + ";"

  This may fall foul of the same problems you are having updating the connection string using the script task - but its worth trying anyway.

  -Jamie

  
  
  

• Wprogram

  Have you tried writing the value to a variable and then using an expression to set the ConnectionString property (containing the password)

  -Jamie

  
  
  

• govind_30s

  The passwords between the development enviroment and the stage enviroment and the production enviroments are all different. If I store it in the package itself, then we have basically 56 packages that have to be opened and reset for each enviroment, and each time the passwords change for any of the 10 servers involved forevermore.

  We attempted to go the route of the configurations, but encounter the same issue. When the configuration is stored in a table, it is not encrypted but clear text, and does not include the password. I can place the password in the connection string in the table and it reads it and updates the connection strings properly, but the userid and password are both plaintext. Anyone who has access to the table, gains access to up to 10 servers they should not have access to.

  Also, when using the configurations table, the packages complain and throw warnings for each connection that is not actually in the package. We are using the configurations table for constants in the packages, and have been going the route of making sure all packages have all constants to prevent it from being a problem with them, but that makes a lot of constants in every package that are not even used, and a lot of maint for each change. Basically, it's a pain I could live without.

  We also went the direction of integrated security, but the issue of Oracle came into things, making that not an option.

  
  
• Setting Passwords In Connections Using Script Task