DTS Package pulling data from AS400 Fails when run by sql server agent.

I have read to my eyes are blurry but I can't seem to find any one post that addresses my issue. I have a dts package that I can successfully execute manually that pulls a table from the AS400 to a sql server table: however, if I schedule a job to run the same package and start it then the job always fails. I get the following error message:

Executed as user: MyComputer\SYSTEM. ...un:  Executing...   DTSRun OnStart:  DTSStep_DTSExecuteSQLTask_2   DTSRun OnFinish:  DTSStep_DTSExecuteSQLTask_2   DTSRun OnStart:  DTSStep_DTSExecuteSQLTask_1   DTSRun OnFinish:  DTSStep_DTSExecuteSQLTask_1   DTSRun OnStart:  DTSStep_DTSDataPumpTask_1   DTSRun OnError:  DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)      Error string:  CWBSY0002 - Password for user X on server AS400 is not correct       Error source:  IBMDA400 Session      Help file:        Help context:  0      Error Detail Records:      Error:  -2147467259 (80004005); Provider Error:  8002 (1F42)      Error string:  CWBSY0002 - Password for user X on server AS400 is not correct       Error source:  IBMDA400 Session      Help file:        Help context:  0      DTSRun OnFinish:  DTSStep_DTSDataPumpTask_1   DTSRun OnStart:  DTSStep_DTSExecuteSQLTask_3   DTSRun OnFinish:  DTSStep_DTSExecuteSQLTask_3   DTSRun:  Package execution complete.  Process Exit Code 1.  The step failed.

When I looked at my one connection to the AS400 in the DTS package, I see the following checked below my userid  - blank password and save password. I uncheck blank password and re-save the package after entering my password again. I execute the package successfully and save the changes then I go to sql server agent and run the job. I get the same error message as above. I have done this numerous times. Next, I tried setting up a DSN connection on my computer and using it in my DTS package instead of the iSeries Access UDB2 provider. It errors out as well. It gives me an unspecified sql error and will not even pull my table from the AS400 in the package.

I set up a linked server to my AS400 and was able to pull the table data into query analyzer and view it.

I am stumped. I know there is a bug in DTS import wizard. See KB: http://support.microsoft.com/kb/315994
It's the error message I received when tried to use the import wizard.

However, another sql developer in the building has used DTS on his machine successfully to run AS400 packages through a DSN connection.

Has anybody else run into this issue?


Who is Participating?
Unfortunately its all a bit clouded because we have an iron fisted paranoid IT manager which has only allowed me limited testing.

The conclusions I have come to are:

1. The user id/pwd entered into the AS400 connection in DTS is largely ignored, especially if run as a job.

2. If no user id is provided to the AS400 driver (Which is the case as the login in the AS400 DTS connection is ignored) then it uses the default user id as previously provided by CWBCFG.EXE and/or client access properties

3. The light dawned on me when I opened this 'client access properties' applet. If you log in as yourself you will see one load of settings (on the second tab). If you log in as the SQLAgent service account, you will see a different bunch of settings. When a SQLAgent job runs, it runs as the SQLAgent account and therefore will use the login in this applet - therefore it can possibly be using a different AS400 login

The way we actually do it here is like this:

1. Create a DTS
2. The first step is a execute process task which calls that script above to set the default login and password
3. Second step actually runs another AS400 command line thing called RTOPCB. This extracts data into a text file
4. Fourth step is to import the text file into a staging table

These are workflowed to ensure they don't run in parallel

Regarding step 3, you need to

1. Run ~another~ AS400 tool called 'Data Transfer From AS400'
2. Define your data extract in this tool
3. Save your 'Data Extract' definition (as a DTF file)
4. Create a script which runs this data extract like so:
   RToPCB <Path to DTF file>

Note that this DTF is a text file that can be altered manually or automtically if need be.

So in actual fact we don't data pump directly from the AS400, we use DTF to export to a text file then load that in. In actual fact if you have a large amount of data you could BULK LOAD the text file in very quickly.

I know it sounds long winded but we have found every other method to be unreliable.... (though we do have an older unsupported AS400 version)

>> Do I have to run the 'CWBLOGON.EXE utility to attach a default password to that login right before you try to use the connection' every time I want to use sql server agent to run a job for the AS400? How would I do that automatically?

Theoretically once you have set the default user for the SQLAgent login/AS400 host combination the first time, then it should save it and you should never have to do it again.

However it only takes someone to log in as the Agent and run a different data extract, or some other unknown AS400 process to change that default user to something else. Then your default login is changed and your data extract hangs.

Also you may need different logins to extract different pieces of data. You'll need to set these beforehand because you never know what the last one was.

Therefore it doesn't hurt to run this script every time you extract the data. It isonly cahcing a user id for the client libraries to use. Nothing hits the AS400 till you extract the data.

Of course the major downfall of this method is that the login and password are sitting in clear text. But this is the price you pay for non-
interoperability (did I just make that word up?)

..... The BEST method would be to get windows integrated logins working but I have not had the opportunity to get that working here. In this case you tell the AS400 that the SQLAgent windows account is a 'trusted' account and no logins or passwords have to be written anywhere.

If you can get THAT working I would like to know :)

I know this is all quite confusing so feel free to ask more questions.
When you schedule a job in SQL the job executes as the user that is set up for the SQL Server Agent Service.  Open enterprise manager, double click management, and then right click on SQL Server Agent and select properties.  Then verify that the Service Start up account has the neccessary privelages to access the AS400.
Sue_WAuthor Commented:
Okay, I know that my sql server agent runs off my local system account: however, the user that the above error message is referring to is my AS400 account name not my sql server agent account.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anthony PerkinsCommented:
As MNelson831 has alluded to (no points please) the local system account does not have access to the DSN, hence the error.
Are you using IBM AS400 Client Access Express?

We do and we had this problem.

What is happening is that there is a default user set up for every windows login (you can set it in the 'Client Access Properties' applet). At the moment this is set for ~your~ login but not for the SQLAgent account.

You need to log in as the SQL Agent account then set a default user in 'Client Access Properties'.

Then you need to use the CWBLOGON.EXE utility to attach a default password to that login right before you try to use the connection.

Then when you do use the connection, the login and password are predefined.


1. Paste the below lines into a CMD file
2. In your Job, run this .CMD file before trying to connect to the AS400.

REM Set the default user ID for this login for a given AS400 host
"C:\Program Files\IBM\Client Access\CWBCFG.EXE" /host <AS400hostname> /uid <username> /r

REM Attach Password to login/server
"C:\Program Files\IBM\Client Access\CWBLOGON.EXE" <AS400hostname> /u <username> /p <password>

If you don't use IBMAS400 Client Access then disregard this post.
Oh, also to run CWBCFG.EXE, the SQLAgent needs to be a local administrator.
Sue_WAuthor Commented:
Okay, I've been trying to get with the AS400 guys all day to discuss the executables mentioned above. I do have a couple of questions about Client Access logging in etc.

We use iSeries for Windows Client Access and as I stated I can successfully execute my package manually and I use my AS400 userid when I set up the iSeries Access 32bit client provider in my DTS package.

I am a little confused about logging in as the sql server agent. I am assuming that I only need to be logged in as the sql server agent to set the default userid once. Correct? Would I have to change the provider information in my dts package to match the new default userid for AS400? I would assume (probably not correctly) that it would be okay to leave my current userid in the package.

Do I have to run the 'CWBLOGON.EXE utility to attach a default password to that login right before you try to use the connection' every time I want to use sql server agent to run a job for the AS400? How would I do that automatically?

Sorry, I am being such a slow top.


Sue_WAuthor Commented:
Thanks for all of the help. I was able to figure out how I was going to get around the issue.

Here's what I did:
I set up a local windows user account for my sql server agent with Admin priv. I set the sql agent service to use that account. I logged on to the computer using the sql agent account then I opened up the iSeries Navigator and set up my connection. One of my options was to use the windows account which was already populated with the logged in account name. I accepted this choice. Opened sql server and ran my job successfully. I logged off of that account and logged back into the machine with my domain login and was able to still run the job successfully.

My only unknown is if the AS400 will prompt sql server agent for it's password, which I would not know about and if it does how long the password is active after you enter it. I have to wait to test it more fully to find out that answer but I am a long way towards solving my issue thanks to your help.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.