Sue_W
asked on
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_
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?
Thanks!
Executed as user: MyComputer\SYSTEM. ...un: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_
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?
Thanks!
MNelson831
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.
ASKER
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.
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.
Alternatively
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.
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.
Alternatively
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.
ASKER
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.
Questions:
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.
Thanks
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.
Questions:
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.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.