Reading encrypted password into user variable

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Reading encrypted password into user variable

Post by UCDI »

We store database connection info (DSN, user, password) in environment variables. I would like to develop a database-agnostic sequence that would dynamically read database connection info (through a routine that reads DSPARAMS) for a connection identified by parameters and then execute SQL against that database. When I read the connection info into user variables and then pass them into the parallel job that executes the SQL, the SQL execution fails with an invalid user/password error. The environment variable holding the password and the parallel job parameter for the password are encrypted. My assumption is that the issue is related to the value being encrypted as it is using the correct values for DSN and user. If the parallel job is run passing the environment variables directly, it succeeds. But when read into a user variable and running the job passing the user variables, it fails.
FranklinE
Premium Member
Premium Member
Posts: 739
Joined: Tue Nov 25, 2008 2:19 pm
Location: Malvern, PA

Post by FranklinE »

My experience with this, using environment variables for passwords, involves how the variables are listed and used in the job.

My implementation:
Define the environment variable in the job properties parameter tab, and set the default value to $PROJDEF.

Name the variable in the stage for the password.

It depends on the stage, of course. If the password attribute prompts for two entries for the password, one types in the variable name.

Look for this in your alternate method of providing the password. I suspect you'll need to use a user defined variable, set to the encrypted attribute, then follow what I followed.
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
FranklinE
Premium Member
Premium Member
Posts: 739
Joined: Tue Nov 25, 2008 2:19 pm
Location: Malvern, PA

Post by FranklinE »

You're not following me because I'm likely not understanding your situation. :oops:

First likely bad assumption: you're storing the variables in the project repository as User defined environment variables. If that's not true, my suggestion is not appropriate. I thought that was true when you mentioned DSPARAMS.

The breakage must be as you found in your alternative testing. DS is not using the user variable in compliance with the encryption format.

My local example avoids this by using the repository, and User defined environment variables. If you considered that and decided it wasn't a good solution, any alternative will have to comply with how that works. That's an under-the-covers thing with which I'm not familiar enough to offer accurate comments.
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 »

FranklinE wrote:First likely bad assumption: you're storing the variables in the project repository as User defined environment variables. If that's not true, my suggestion is not appropriate. I thought that was true when you mentioned DSPARAMS.
I am using user-defined environment variables defined in the Administrator Client tool. What I am needing and what you might be missing is to be able to dynamically choose which environment variables are used from one execution to the next based on parameters. For example, in one execution, the input parameters may dictate that I should use environment variables A1, A2, and A3. On the next execution, the input parameters may dictate that I should use B1, B2, and B3. So I can't directly pass the environment variables because the ones used can change from one run to the next. I don't know a clean way to do this without using user variables and it seems that user variables don't handle the encryption properly on the password. Thanks for your responses. I may have to go with a "dirtier" solution.
FranklinE
Premium Member
Premium Member
Posts: 739
Joined: Tue Nov 25, 2008 2:19 pm
Location: Malvern, PA

Post by FranklinE »

Have you considered "dirty" logic branches in a job sequence? It means having redundant stages, their only difference being the environment variables used.

You are aiming for an efficient solution. I'd do the same, but sometimes it's better to surrender to the obstacle and change it to something else. :wink:
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
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Try using values files in a Parameter Set, and use logic to choose which values file to implement.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
FranklinE
Premium Member
Premium Member
Posts: 739
Joined: Tue Nov 25, 2008 2:19 pm
Location: Malvern, PA

Post by FranklinE »

Ray,

That would never fly here, having passwords (even encrypted) in a permanent file. Much too hackable.
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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Interesting. Even encrypted, eh? We're doing that very thing here with a different ETL tool, leveraging encrypted passwords from a file and it passed a security audit. Do you specifically mean DataStage encrypted passwords? Those are seemingly not all that strong or hacker proof as seen by the tools posted by others here to decrypt them.
-craig

"You can never have too many knives" -- Logan Nine Fingers
FranklinE
Premium Member
Premium Member
Posts: 739
Joined: Tue Nov 25, 2008 2:19 pm
Location: Malvern, PA

Post by FranklinE »

Craig,

I'm a designated access person in the financial industry. My fingerprints are on file at the SEC. I work in the most regulated industry in human history (well, arguable by some, but they are pikers, really), and I will happily stipulate that my shop is as closed-down as it is possible to be and still get a little bit of work done. :lol:

Seriously, though, the key risk points are easy to see. Environment variables, if visible to the OS, will be visible to hackers. There are, of course, equally easy ways to prevent that, but not when the password is permanently stored in a file. Hackers find ways to get around paltry permissions and such (ahem), but if a password is exposed only during a running process, unless they know exactly which one and can see it while it's running, they will not get the password.

I might be describing that poorly. I'm an adequate Unix guy, capable of repeating what I've heard without necessarily knowing the details behind it. I do know, from Unix gurus, that SSHv2 with key authorization and such is far better than storing passwords in files, and it still isn't as secure as some (like my shop) want it to be.

BTW, our implementation is ID Vault.
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 »

ray.wurlod wrote:Try using values files in a Parameter Set, and use logic to choose which values file to implement.
Thanks for the suggestion. I considered that option, but the issue is that I need both the ability to choose which database connection to use as well as the ability for that connection to have different values across environments (Dev, Test, Prod), which are DataStage projects. If I use a parameter set with different values files, the values files would give me the first, but not the second. Technically, they could give me both, but the parameter set would have to have different values files in each environment and to keep migrations easy, we try to make environment variables the only things that change across environments. If I use a parameter set with environment variables, it would give me the second, but not the first.
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Choose a job you love, and you will never have to work a day in your life. - Confucius
Post Reply