i'm having trouble with a sql job calling a dts package. the dts package runs fine, but does not run when executed from a job (my domain account is set as owner of the job, and i'm a system admin as well - but the sql server agent is a local account not a domain account)
ordinarily that would be fine - i'm the owner of the job so when the agent executes the job it should use the security context of my account. however i believe there is an additional twist to that, related to DTS packages. here's the catch . . .
if the job is owned by a Windows NT domain account and if the package is stored in the SQL Server or SQL Server repository (not as a file), you must start the SQL Server service by using an account from the same domain or an account from a trusted domain. For example, if the SQL Agent job is owned by an account from the xyz domain, then the account used to start the SQL Server service must be either from the xyz domain or a domain trusted by the xyz domain. If the SQL Server is started using a local account, the package fails to run. this stems from the fact the dts is launched by a separate executable, and inherits the sql server agent user, not the one you set under the job properties.
backgrounnd info on the security context for dts packages :
from http://support.microsoft.com/?kbid=269074
Where is the DTS Package Running?
One problem that is frequently reported about DTS packages is the difference in behavior when a package is run from the SQL Enterprise Manager versus when the DTS package is scheduled as a job. When you run the package from the DTS Designer in SQL Enterprise Manager (SEM), the package is running on the computer where you are seated. If you are at the server (either physically, or through remote access software), the package is run on the server. If you are seated at a workstation and you have registered the SQL Server server in Enterprise Manager, then the package is run on the workstation. The security context of the package is that of the Windows NT account you used to log in to that computer. When the package is run as a scheduled job, the package is always run on the server.
Frequently, a developer creates and tests the DTS package interactively on their workstation through the DTS Designer in Enterprise Manager. After the DTS package is debugged, the package is then scheduled as a job. This changes the location of the package from the developer's workstation to the server. If the package was loading text data into SQL Server, the package fails unless the text file and the path to the file exist on the server. If the package was connecting to another server, the package fails if the security context of the job does not support the connection.
How is the DTS Package Launched?
If you manually run a package by using the DTSRun.exe command line utility, the security context is that of the Windows account you used to log in to the computer. If you run the package by using DTSrun.exe through the xp_cmdshell extended stored procedure, the package is run in the context of the account used to start the SQL Server service, provided that the user that executed xp_cmdshell is a member of the Sysadmin role. If the user that executed xp_cmdshell is not an account in the Sysadmin role, then DTSRun.exe runs in the context of the SQLAgentCmdExec account.