In a DatabaseConnect activity I’ve an ODBC ConnectionString for connecting to an Oracle database. The string is in this format:
“Driver={Microsoft ODBC for Oracle};SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hosthere)(PORT=porthere))(CONNECT_DATA=(SID=databasenamehere)));
uid=usernamehere;pwd=passwordhere”
And the ProviderName = “System.Data.ODBC”
The connection works fine and I’m able to query the database successfully in a subsequent Execute Query activity.
However at the moment, the username and password are hard coded into this connection string: I’d like to use variables in the string so that I can retrieve the credentials from Orchestrator at run time, and then pass them into the connection string when connecting to the database. I can get the credentials, but I can’t find the correct syntax for including these in the connection string. I had tried:
Thanks for replying KarthikByggari, that has helped. As per your example I’ve now reformatted the connection string into a single line and assigned it to a variable.
However, the one remaining issue I’ve got, is that the password is a secure string, and when I include it in the connection string (using tostring), I get an error, invalid username/password. If I show the variable that holds the connection string in a message box, then it shows the text System.Security.SecureString where the password should be.
Just wondering if anyone else had any ideas for this one? How to pass in a password (secure string) into an ODBC connection string to an Oracle database?
Im having the same issue and Im currently looking into decrypting the securestring password (I get from a credential in Orchestrator), but it feels like a bad solution
To elaborate you can use the following command to get the value inside of a SecureString:
System.Runtime.InteropServices.Marshal.PtrToStringUni(System.Runtime.InteropServices.Marshal.SecureStringToGlobalAllocUnicode(PASSWORD)).ToString
And then you can put it into your connection string and still keep it “hidden” from in the orchestrator,
No, get-asset only gets assets from Orchestrator, in this case it will get a username which is a string variable, and a password which is a securestring variable.
Set-asset, updates said values of an orchestrator asset.
The problem of the OP is still very valid, as you cannot pass the securestring password in to a database connection string.
If you choose to decrypt the password, as someone suggested, you are having bad security in your robot, as it will be sent in clear text over the network once the robot runs, and as such anyone with WireShark can find your credentials.
To mitigate this, one can use the “GetPassword” activity to encrypt(actually obfuscate) the decrypted password, only problem is that to my knowledge you cannot pass the decrypted (or encrypted) password to the GetPassword as an input parameter. So you have to store the password in the GetPassword activity manually, which is a no go, governance wise, since everytime the password needs updating, you would have to rebuild the project.
Hope it makes sense.
I just want to +1 this post - from an enterprise security perspective, database activities really need to support retrieving a credential from Orchestrator. I did see another post where some suggested making database connections a new asset type in and of themselves; that would likely be an even better approach.
Hi,
I’m facing the same scenario as I couldn’t integrate the asset credentials as variables in db2 connection string is there any updated solution on this issue. Could you please help me out with any solutions for the same, and the connection string I have used is,
“Driver={Microsoft ODBC For Oracle};SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hosthere)(PORT=porthere))(CONNECT_DATA=(SID=databasenamehere))); " +"uid=” + UsernameVariable + “;pwd=” + PasswordVariable;