SQL Server Agent Job - SSIS Package - Access Information required
Hi Team,
Good Afternoon. We need some help in understanding the way the SQL Server Jobs work. Below mentioned is an incident that we have encountered recently.
Incident: SQL Server Jobs remains in the Execution mode for a very long time.
Description: As a standard all the services are setup to execute with the domain account by default. In this process, the SQL Server Agent Services was set up to execute with the domain account.
Also as a standard, a proxy account is created with the required credentials to execute SSIS package and OS commands.
New Job is created with the owner as [sa] to execute a SSIS package. This SSIS package is present on the file system (on disk). When this job is executed from the job, the SQL Server job doesn’t complete. It remains in execution mode forever. This happens very frequently across many servers. Initially the jobs execute without any issue but after some time we run into the problems that were mentioned.
When the package is executed from the command prompt with the same proxy account that was used in the SQL Server DB, SSIS package executes successfully without any issues.
One more analysis that we did is that change the service account from domain to local system account. When this is changed and when the package is executed, the job did complete from SQL Server Agent without issues.
Can anybody explain the reason for this strange behaviour? Also if in case if there are some other standards that we need to follow, please let us know.
Please let me know if I need to provide more information on this.
Many Thanks in advance.
Regards,
Siva- Moved byDuane Douglas Wednesday, November 25, 2009 6:50 AM (From:SQL Server Integration Services)
All Replies
- this seems to be more of a sql server security issue than an ssis issue.
i'm referring you to the sql server security forum for further assistance.
Duane Douglas, MCAD, MCITP | http://www.ssisbi.com | Please mark the post(s) that answered your question.
