December 16, 2009 at 7:27 am
Server A running SQL2008 runs SQL Agent Job to pull info from table on Server B running SQL2000 using SSIS. Job was working fine until last Friday when I changed SA password on Server B. Job fails with error SQL Server Scheduled Job 'Employee_Transfer_TA' (0x2E8F8EB32E4ADD4690A8E1FB7AF4605E) - Status: Failed - Invoked on: 2009-12-16 09:15:19 - Message: The job failed. The Job was invoked by User domain\admin. The last step to run was step 1 (Employee_Transfer_TA). Both SQL Server services use same network admin account as logon.
If I execute the package on Server A in BIDS it runs fine, but when I run it in SQL Server Agent it fails. I run a seperate job on Server A that truncates the table on Server A then the SSIS job is supposed to pull the data from a table on Server B and load that data into empty table on Server A. When I execute the package in BIDS new data is loaded into table on Server A. When job is run in SQL Server Agent on Server A it fails. I've tried to change the password on the connection in BIDS but it does not help.
December 16, 2009 at 7:35 am
When you execute a package in BIDS, it executes under your security context, and runs fine (since you have enough permissions). But when schedules, it runs under the context of account which is given in the Job step Run AS. Check that account has appropriate permissions.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 16, 2009 at 8:10 am
Bru,
That's what I thought. It's a SSIS package so the job is running under the SQL Server Agent Service Account. I looked in SQL Server Configuration Manager and SQL Server Agent uses our network admin account as a logon. This is the same account I logged into the server with to run BIDS. The password for this account has not changed. The only password that was changed was the SA password on server B.
Also other jobs on Server A work fine, only one failing is one that connects to Server B.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply