How to add Credentials as variables in ODBC Connection String?

Help
1

Hi folks,

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:

“Driver={Microsoft ODBC for Oracle};SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hosthere)(PORT=porthere))(CONNECT_DATA=(SID=databasenamehere)));
uid=” + UsernameVariable + “;pwd=” + PasswordVariable

…but get a “String constants must end with a double quote” error.

I suspect I’m missing something simple, but any help would be appreciated.

2

I figured out what is the problem is.

You are assigning the string which is multi-line.
Make sure the string should be in a single line or proper ending of strings with double quotations.

Find the attached workflow for the same.Main.xaml (6.0 KB)

Regards,
Karthik Byggari

3

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.

Is there another step I’m missing?

4

Hi,

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?

Thanks in advance.

5

Hi Pathfinder

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 :slight_smile:

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,

4 Likes
6

Hi Yumwzsch,

Thanks for that, this is very useful as a workaround, apologies for the delay in replying!

7

Or you can use an asset don’t you ? get and set :slight_smile:

8

Hi Beesheep

Can you explain how?

Best regards Mikkel

1 Like
9

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.

2 Likes
10

@Yumwzsch

Sorry man I didnt see you message. Do you still need help?

11

nah, @Mikkel_Nielsen explained the issue perfectly :slight_smile:

12

Any update or new approach on this scenario? I am experiencing the constraints exactly as @Mikkel_Nielsen described them…

13

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.

Edit: Reading that other thread, it turns out that the database activities pack is open source and someone who knows what their doing could update it to support Secure String: Idea: Database Connector as Asset in Orchestrator - #3 by Scott_Taylor

14

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;

Thanks in advance,
Priya