June 22, 2011 at 8:06 am
Both my Google and memory skills are failing me so I come to you guys:
I suspect there's a security context difference between running a SQL Agent job via the scheduler vs. doing right click | start job as step in SSMS. But I'm not finding the documentation to confirm this.
I ask because I have an agent job that apparently out of the blue (no changes I am aware of and I'm the only one that would touch this server / process) has failed for two nights in a row with an obscure Windows error (see below). The Agent job is an ETL process which invokes an SSIS package which does some processing then invokes a third party application which runs its own SSIS packages. The job is failing somewhere between the apparent end of their SSIS package run and the app returning control to our SSIS package.
SSIS log for both our SSIS package and theirs shows the same operator regardless of which way invoked. We use Windows / AD for security.
This is in our integration/test region so I can play with this and rerun the job as much as needed, fortunately. Next step I'm thinking is ti schedule the job on a one-off and see what happens. (I have contacted the vendor and they are working on it from their end as well.)
here's the event log error entry:
Event Type:Error
Event Source:.NET Runtime 2.0 Error Reporting
Event Category:None
Event ID:5000
Date:6/22/2011
Time:4:12:02 AM
User:N/A
Computer:SQLINT
Description:
EventType clr20r3, P1 theapp.exe, P2 1.2.6.0, P3 4bb379db, P4 system.data, P5 2.0.0.0, P6 4889deaf, P7 2490, P8 2c, P9 system.data.sqlclient.sql, P10 NIL.
June 22, 2011 at 9:17 am
Pam Brisjar (6/22/2011)
Both my Google and memory skills are failing me so I come to you guys:I suspect there's a security context difference between running a SQL Agent job via the scheduler vs. doing right click | start job as step in SSMS. But I'm not finding the documentation to confirm this.
My understanding of Agent says it should not matter how you kick off the job. Each job step is configured to run as a specific user, either explicitly or implicitly. If you find documentation that implies there is a security context difference based on the start method I would love to see it.
My guess is that something else changed in the environment. Could the error be data-related? Have you applied any software or OS patches lately? Have any security changes been made to resources your app is trying to access?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 22, 2011 at 9:26 am
In my Googling I found a few saying it was different, but for SQL 2000. Anyhow, even when invoked from a one-off schedule it still ran to completion just fine.
Only thing I can figure at this point is that IT/Ops is doing something around the 4:00 - 4:11 time frame when this job runs and fails. I did check with them and they say there's nothing unusual happening during that time but you never know. I've adjusted the schedule for tonight's run to a later time so we'll see what happens.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply