DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 335

Points: 3391

Post Posted: Wed Apr 25, 2018 12:06 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Additional info: Database connection failing
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



Group memberships:
Premium Members

Joined: 25 Nov 2008
Posts: 686
Location: Malvern, PA
Points: 6492

Post Posted: Wed Apr 25, 2018 1:18 pm Reply with quote    Back to top    

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: http://www.dsxchange.com/viewtopic.php?t=143596 Using CFF FAQ: http://www.dsxchange.com/viewtopic.php?t=157872
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 335

Points: 3391

Post Posted: Wed Apr 25, 2018 2:54 pm Reply with quote    Back to top    

FranklinE wrote:
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.


Thank you for responding, but I’m not following you. I have a sequence with a user variables activity and a job activity. The user variables activity uses a routine to read environment variable values (different ones depending on the inputs to the sequence) into variables. These variables are then passed as parameters on the job activity. How are you suggesting I change this?
Rate this response:  
Not yet rated
FranklinE



Group memberships:
Premium Members

Joined: 25 Nov 2008
Posts: 686
Location: Malvern, PA
Points: 6492

Post Posted: Thu Apr 26, 2018 7:51 am Reply with quote    Back to top    

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

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: http://www.dsxchange.com/viewtopic.php?t=143596 Using CFF FAQ: http://www.dsxchange.com/viewtopic.php?t=157872
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 335

Points: 3391

Post Posted: Thu Apr 26, 2018 9:38 am Reply with quote    Back to top    

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.
Rate this response:  
FranklinE



Group memberships:
Premium Members

Joined: 25 Nov 2008
Posts: 686
Location: Malvern, PA
Points: 6492

Post Posted: Thu Apr 26, 2018 9:46 am Reply with quote    Back to top    

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: http://www.dsxchange.com/viewtopic.php?t=143596 Using CFF FAQ: http://www.dsxchange.com/viewtopic.php?t=157872
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54283
Location: Sydney, Australia
Points: 294429

Post Posted: Thu Apr 26, 2018 4:48 pm Reply with quote    Back to top    

Try using values files in a Parameter Set, and use logic to choose which values file to implement.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
FranklinE



Group memberships:
Premium Members

Joined: 25 Nov 2008
Posts: 686
Location: Malvern, PA
Points: 6492

Post Posted: Fri Apr 27, 2018 7:07 am Reply with quote    Back to top    

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: http://www.dsxchange.com/viewtopic.php?t=143596 Using CFF FAQ: http://www.dsxchange.com/viewtopic.php?t=157872
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42663
Location: Denver, CO
Points: 219713

Post Posted: Fri Apr 27, 2018 7:42 am Reply with quote    Back to top    

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

And I'm hovering like a fly, waiting for the windshield on the freeway...
Rate this response:  
Not yet rated
FranklinE



Group memberships:
Premium Members

Joined: 25 Nov 2008
Posts: 686
Location: Malvern, PA
Points: 6492

Post Posted: Fri Apr 27, 2018 7:55 am Reply with quote    Back to top    

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. Laughing

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: http://www.dsxchange.com/viewtopic.php?t=143596 Using CFF FAQ: http://www.dsxchange.com/viewtopic.php?t=157872
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 335

Points: 3391

Post Posted: Fri Apr 27, 2018 8:14 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
qt_ky



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2796
Location: USA
Points: 21137

Post Posted: Mon Apr 30, 2018 6:00 am Reply with quote    Back to top    

The DataStage version 8.x (or 9.x) and older "encrypt" type of parameters and environment variables used more of a simple encoding algorithm that was rather easily decoded, as posted elsewhere on the forum.

In DataStage 11.x and newer they moved to actual encryption and also made a command line utility available that you can use on credentials or any value you want to.

Here is a quote from:
https://www.ibm.com/support/knowledgecenter/SSZJPZ_11.7.0/com.ibm.swg.im.iis.found.admin.common.doc/topics/encrypt_ovw.html

Quote:
The encrypt command provides a method to encrypt user credentials. The encrypted strings can be stored in a credentials file or used on the command line with many IBM® InfoSphere® Information Server tools.

The command uses Advanced Encryption Standard (AES) 128-bit encryption as the default provider, which meets US export regulation requirements. You might also choose to provide your own password encryption algorithm.

_________________
Choose a job you love, and you will never have to work a day in your life. - Confucius
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 335

Points: 3391

Post Posted: Tue May 01, 2018 10:22 am Reply with quote    Back to top    

In case someone else is wondering, I found a way to pull this off, albeit a little less elegantly than I was hoping. We have a routine that will run a DataStage job by name with parameters passed in a delimited name/value pair string (e.g. “parm1=value1|parm2=value2”). So instead of using a Job activity, I will call this routine to run the job in a User Variables activity. I have to add the password environment variables for each possible database as a parameter to the sequence (uggh!). But the other connection parameters I can read as before. The logic for determining the password environment variable and getting its value will be incorporated into the building of the parameter string.
Rate this response:  
Not yet rated
qt_ky



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2796
Location: USA
Points: 21137

Post Posted: Fri May 04, 2018 10:04 am Reply with quote    Back to top    

This may also be of interest as related to encryption:

How to strongly encrypt passwords defined as project Environment Variables in InfoSphere Information Server DataStage

_________________
Choose a job you love, and you will never have to work a day in your life. - Confucius
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours