Help with SqlQuery activity-Connection string with parameter

Permanently deleted user

October 02, 2013 23:27

I am trying to create a work flow that will allow an authorized(who has access to XYZ database in oracle) user to create work request on an identified asset and have work request data stored back to database XYZ. In the work flow, the user will provide logon and password that he uses to logon to Oracle database XYZ. 

I am wodering if it is possible to connect to the database with parameters in SqlQuery activity's connection string ?

Inside the Connection String, I have:

Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST='databaseserverIPaddress')(PORT=1521)))(CONNECT_DATA=(SID=XYZ)(SERVER=DEDICATED)));User Id=:userid;Password=:password

 

then inside SqlQuery activity's parameters: I have:

/customer/servlet/servlet.FileDownload?file=00P6000000e88WoEAI

Inside the command, I have "select * from users where uname = :user;"

When I run the workflow, I got the following error:

ORA-01017: invalid username/password; logon denied

I know for sure that the user name and password that I put in is correct, what could be missing?

Could someone please offer some help?

 

Thank you and regards,

Jin

0

• 

  Kevin Penner

  October 04, 2013 06:03

  Hi Jin,

  I do not think it is possible to dynamically change the connection string (prevent injection/reading them in payload). Try connecting with a string which makes use of a static username and password, such as: 

  Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST='databaseserverIPaddress')(PORT=1521)))(CONNECT_DATA=(SID=XYZ)(SERVER=DEDICATED)));User Id=ID_CHECKER_READ_ONLY ;Password=multiPass

  You can then test the connection right from the non-query activities design panel to see if it works.

  It would be possible to check user/pass validity by hitting a stored procedure or have some sort of table to look up.

   

  Best of luck,

  Kevin

  Edit: I should remind you that the usernames and passwords will be sent in a workflow payload and will be unencrypted. If someone is listening to the traffic between the server and your client, they might be able to see which user/passwords are being checked for validity. Since those are also passwords to your database I'd be very careful. Try using Fiddler to inspect these payloads so you can get a good idea of what I'm talking about.

   

  0

Please sign in to leave a comment.