SQL Database ConnectionString - use variables for Username, Password, IP address, Port etc

Help Activities
, , , , ,
1

Hello,

I was successfully able to made a connection to my Oracle database via the “DatabaseConnect” activity in Studio. The connection string that resulted was (ABCD inserted for privacy sake for each comapny variable):

“USER ID=ABCD;PASSWORD=ABCD;DATA SOURCE=”(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ABCD)(PORT = ABCD)))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = ABCD)))“;PERSIST SECURITY INFO=True”

This worked and I was able to run queries using this setup, however if I go into the Expression Editor for the ConnectionString with the “DatabaseConnect” activity the following error message is present: "Comma, ‘)’, or a valid expression continuation expected. Thus, I am unable to edit this connection string to put in variables into all the ABCD places like (username, password, IP address, Port #, Service Name, etc.) - the “OK” button is always grayed out because of the ERROR and I can only hit cancel - despite being able to run with this exact connection string.

I need to be able to put in the variables into that connection string as I will be moving between production and testing databases so I need those values to be flexible and reference assets in my Orchestrator.

Any suggestions as to how to tweak things or how to make my own “Connection String” that I might be able to pass into the “ConnectionString” field of the “DatabaseConnect” activity?

Thanks!

1 Like
2

@taxguy33

You can pass variable as below into the connection string.

"USER ID='"+ABCD+"'

ABCD variable should be of type String.

3

instead of connection string try to connect manually from connect activity

4

@lakshman @Veera_Raj

I was able to make to use an “Assign” connectionstring equal to the below text that worked and I could run queries successfully (the Password is hard coded, obviously I want this hard-coded password to be a variable that still works and not be hardcoded):

“USER ID='” + username +“';PASSWORD=Password1234#;DATA SOURCE=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = '”+hostIP +“’ )(PORT = '”+portnumber+“')))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = '”+servicename+“')))”+“;PERSIST SECURITY INFO=True”

When I made the change to include the “password” variable (see below string) in the “connectionstring” variable assign activity instead, I am now getting an error “ORA-01017: invalid username/password;logon denied”. Do you think this could be because there is a special character (#) in the password phrase? Seems wild that it would work when the # is hard coded in the PW, but when I put the password string variable in the “connectionstring” it says it is an “invalid username/password”.

“USER ID='” + username +“‘;PASSWORD=’”+password+“';DATA SOURCE=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = '”+hostIP +“’ )(PORT = '”+portnumber+“')))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = '”+servicename+“')))”+“;PERSIST SECURITY INFO=True”

Thoughts on what I can do to fix?

image

1 Like
5

@taxguy33 Refer below docs, it would help

6

Hey @taxguy33

In your original password you don’t have the quotes.

But when you changed it to the dynamic variable, you are adding a single quote to it which is why I guess the error occurs.

The working code which you posted

“USER ID=’” + username +"’;PASSWORD=Password1234#;DATA SOURCE=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ‘"+hostIP +"’ )(PORT = ‘"+portnumber+"’)))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = ‘"+servicename+"’)))"+";PERSIST SECURITY INFO=True"

The code which I modified as per above statement

“USER ID=’” + username +"’;PASSWORD=" + Password + ";DATA SOURCE=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ‘"+hostIP +"’ )(PORT = ‘"+portnumber+"’)))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = ‘"+servicename+"’)))"+";PERSIST SECURITY INFO=True"

Hope this helps.

Thanks
#nK

7

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.