Job Scheduling with DataStage Director

EJRoufs · Post by **EJRoufs** » Thu Jun 16, 2005 8:10 am

We are using DataStage v 7.1 on 2 Windows servers.

We don't use the DataStage Job Scheduler a real lot here, but let me give you some background first. Apparently, you have to have Admin rights to the server to use the Scheduler. Only a few of us have that. I am one of those. Unfortunately, most people do not have Admin rights, so they can not schedule their own jobs. Is there a way around that? I would think it wouldn't be a big deal to have people scheduling their own jobs, but there's no way that everyone will be grated Admin rights to the server.

Problem #2. I have my DataStage jobs scheduled, and they run just fine... some of the jobs running against DB2 tables. When I try to schedule a job for someone else, using SQL Server database with an ODBC connection that uses Windows Authentification, it errors out, saying it can't connect to the SQL Server with userid (null). How do I tell the job to run with a certain UserID in the scheduler, if that can even be done?

Thanks for any help. :>

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Jun 16, 2005 8:21 am

1.) You can schedule a sequencer and include your jobs in the sequencer
2.) You need to define the schedule user-id and pass in the DS Admin.

EJRoufs · Post by **EJRoufs** » Thu Jun 16, 2005 8:39 am

Sainath.Srinivasan wrote:1.) You can schedule a sequencer and include your jobs in the sequencer
2.) You need to define the schedule user-id and pass in the DS Admin.

Thanks! :>

So, #1... I'm thinking we'll still have the problem with most of the users not being able to schedule a Sequencer, either.

#2... cool beans. Can you tell I've had absolutely ZERO Admin training? :> I see it now, and have entered it in. We are doing a test here in just a moment...........

ds_developer · Post by **ds_developer** » Thu Jun 16, 2005 10:56 am

DataStage uses the underlying (OS) scheduler, which on Windows is called 'AT'. Windows is enforcing the rule that you have to be an administrator to use AT (DataStage has nothing to do with it).

So, you are right, it won't matter if it is a series of jobs or a sequence.

John

gsbrown · Post by **gsbrown** » Mon Feb 20, 2006 3:27 pm

Sorry to dig this up from the grave, but this issue relates to a current issue I'm having and I'm looking for answers.

We currently have many jobs running via Windows Task Scheduler and we've designated a specific user id to run these scheduled jobs via DataStage Administrator.

Now we've hit a roadblock where we need to run a job that updates a MSSQL database where the scheduler user/pass that we've designated doesn't have access to this system. Giving this user access to the system is not an option and we don't want to change the scheduler user/pass to the appropriate MSSQL user/pass because that will cause our existing scheduled jobs to bomb.

The MSSQL job doesn't seem to want to use the user/pass job parameters that I specify when I add the job to the schedule. The database uses WindowsNT authentication and it won't use any credentials other than the user/pass supplied in DSAdmin.

Even more troublesome, you can't even "view data" in the ODBC stage within the job using another supplied MSSQL user/pass. It ignores anything you've specified in the user/pass fields of the ODBC stage and strictly uses whoever is signed into the DataStage client.

Any ideas or suggestions to get around this?

Thanks,
greg

kwwilliams · Post by **kwwilliams** » Mon Feb 20, 2006 5:09 pm

Have you thought about switching to Unix? :D

Could you set up a generic account that is used to log into DataStage to perform this type of work? Grant permissions in the database to this generic account and you should be set.

ray.wurlod · Post by **ray.wurlod** » Mon Feb 20, 2006 7:40 pm

Job parameters for DSN, user ID and password for SQL Server?

kwwilliams · Post by **kwwilliams** » Tue Feb 21, 2006 6:41 am

Ray,

I think that the way they have their SQL Server setup it does not recognize the parameters being passed. Using Windows Authentication it just allows any users to log in to a database without ever signing in because it is using their OS credentials to actually log them in to the database.

gsbrown · Post by **gsbrown** » Tue Feb 21, 2006 8:37 am

That's correct. The SQL Server is setup with WindowsNT Authentication and it from my testing the ODBC stage in my job uses the DataStage login or login designated for scheduled tasks to connect to the database.

UserA = DataStage Login
UserB = SQL Server Login
We would like UserA to run this job using UserB to connect to the SQL Database.

In the ODBC stage, it ignores the user/pass fields. I even left them blank and it still let me view the data or run the job.

We might just be stuck, but I was hoping someone knew a workaround. The DataStage signon that we use to run our production jobs is widely known among developers and the owner of this SQL Server data is not wanting to give authority to that user. The data owner wants to use a brand new domain user where only a select few know the password. Then, the problem is we don't know how we are going to get this user to run this job automated.

I'm NOT an expert on security policies, but I wanted to get some info and suggestions on this issue so I could approach our I.T. security and lay out the options.

Thanks for the quick responses!
Greg

kwwilliams · Post by **kwwilliams** » Tue Feb 21, 2006 9:48 am

I took an IBM class last week and the instructor mentioned something about a SQL Server Stage taht was available for clients running on a Windows Platform (we're on Unix so I didn't really listen intently to parts that did not pertain to me), I wonder if it would have functionality that you could use to work around this security issue.

ray.wurlod · Post by **ray.wurlod** » Tue Feb 21, 2006 5:31 pm

Security is not meant to be worked around! That's why I suggested not using the "trusted identity" and explicitly providing user ID and password (as job parameter values).

kwwilliams · Post by **kwwilliams** » Tue Feb 21, 2006 10:22 pm

Not trying to argue, but what he is saying is that NT authentication is overriding any parameters he tries to pass to the job. When he runs the job it is logging into the Sql Server database using the name that was used to sonnect to DataStage and not the parameters that he is trying to pass. Unless he works with his DBA's to find a way to pass a log in to the database, I don't think he really has many options.

crouse · Post by **crouse** » Tue Feb 21, 2006 11:05 pm

Just came from this type of environment and the only way for this to work is to have the DBA create a local SQL Server account for you. You then create a new DSN that attaches to the database with the local SQL Server account, not the domain account.

And as Ray mentions, make the DSN, ID and Password job parameters so it can be run with etiher of the DSN's, dependent on who runs it.

The passwords can be encrypted in DataStage so they aren't viewable by the wrong eyes.

The trusted domain accounts in Windows and SQL Server are very effective, but it isn't "one size fits all".

-Craig

jmorgan · Post by **jmorgan** » Tue Feb 21, 2006 11:58 pm

We use this exact method on the site here where i work.

crouse wrote:Just came from this type of environment and the only way for this to work is to have the DBA create a local SQL Server account for you. You then create a new DSN that attaches to the database with the local SQL Server account, not the domain account.

And as Ray mentions, make the DSN, ID and Password job parameters so it can be run with etiher of the DSN's, dependent on who runs it.

The passwords can be encrypted in DataStage so they aren't viewable by the wrong eyes.

The trusted domain accounts in Windows and SQL Server are very effective, but it isn't "one size fits all".

-Craig

gsbrown · Post by **gsbrown** » Wed Feb 22, 2006 7:58 am

Thank you very much for that idea. I orginally suggested that to the SQL Server owner, but she insisted that the database is setup with Windows authentication and wouldn't allow SQL Server authentication anymore.

I will have to confirm this with her. From what you are saying we should be able to use use either Windows or SQL Server authentication. If that's the case, this new DSN solution is exactly what we need to do.

Thanks!
Greg