Parameterized password in Stored Procedure stage

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
ajithaselvan
Participant
Posts: 75
Joined: Mon Jul 12, 2010 4:11 am
Location: Chennai

Parameterized password in Stored Procedure stage

Post by ajithaselvan »

Hi,

I'm using Stored Procedure stage to call Oracle SP. In the data connection,
I'm able to pass the parameter for Data source and Username. However, I'm unable to pass the parameter for password as it accepts encrypted format.

Can you suggest the way to accept the parameter?
Ajitha S
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Not really following. Passwords as encrypted parameters can be passed to anything, they are unencrypted during the process. Can you provide more details about your issue, please?
-craig

"You can never have too many knives" -- Logan Nine Fingers
ajithaselvan
Participant
Posts: 75
Joined: Mon Jul 12, 2010 4:11 am
Location: Chennai

Post by ajithaselvan »

I'm not suppose to hardcode the password inside the STP stage. It should be passed from sequence job. So I used the corresponding parameter name in the place of password, which is not getting accepted by this stage.
Ajitha S
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

You'll need to let us know what "not getting accepted" means. If you are getting errors, please post them.
-craig

"You can never have too many knives" -- Logan Nine Fingers
ajithaselvan
Participant
Posts: 75
Joined: Mon Jul 12, 2010 4:11 am
Location: Chennai

Post by ajithaselvan »

Error: ORA-01017: invalid username/password; logon denied
is the error message I received
Ajitha S
FranklinE
Premium Member
Premium Member
Posts: 739
Joined: Tue Nov 25, 2008 2:19 pm
Location: Malvern, PA

Post by FranklinE »

It could be as simple as a syntax error.

How are you typing in the Data source and user name? Do you just name the parameter -- parmDataSource -- or are you using the escape # -- #parmDataSource#?

I know that in other stages, you must use #, and in other stages you must not. Usually the difference is if you have an edit button for the text box.
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson

Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
ajithaselvan
Participant
Posts: 75
Joined: Mon Jul 12, 2010 4:11 am
Location: Chennai

Post by ajithaselvan »

Hi Frank,
Im using like below
#parmDataSource#
#parmUser#
#parmPassword#
Ajitha S
FranklinE
Premium Member
Premium Member
Posts: 739
Joined: Tue Nov 25, 2008 2:19 pm
Location: Malvern, PA

Post by FranklinE »

I believe it's that the password is encrypted, and not being converted back to text by the runtime engine. I defer to others who use the SP stage to comment further, because I've hit the extent of my experience with this.

Good luck.
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson

Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

this could be TOTALLY unrelated and off the wall, but let me describe a recent issue I was having...

I was having my password constantly rejected for a teradata system. My password was rather lengthy and about 50% special characters (standard USA keyboard punctuation, math, and similar stuff like !@#$%^&*() etc). What seems to have been happening is that I stumbled across some password that, when encrypted, generates datastage control characters, maybe #junk#, or something, I do not know exactly what, but it just would not work.

I changed my password on that database and the exact same code and such worked perfectly since.

So, all that to say, maybe change your password, if you think you are doing everything correctly?
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

I've experienced that as well. The software restricts a lot of special characters. There are documented here:

Naming restrictions for user IDs and passwords
Choose a job you love, and you will never have to work a day in your life. - Confucius
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

NICE, I looked but did not find this list.
Looks like the one I was using had at least 5 offending characters in it..

Also seems like login to the tool does not matter what you have, but inside jobs it will cause troubles.
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

We have also worked cases where the password that contains prohibited special characters works just fine when you click "View Data" on a Connector stage but the same password aborts the same job at run time. The password-handling code behind the "View Data" service provided by the services tier is different code than the run-time code in the engine tier. I have not worked with the Stored Procedure stage much but would, perhaps riskily, assume it would behave in a similar way.
Choose a job you love, and you will never have to work a day in your life. - Confucius
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Post by jackson.eyton »

Just wanted to say this was a great eye opener to read, not current issues with this but perhaps just avoided any in the future. Thanks everyone!
-Me
Post Reply