DTS failing as a job
-
I've seen this thread posted other various forms already in this forum so pardon me on seeming redundant, but its different this time (really).
I have a DTS that I'm running with the T-SQL command
exec master..xp_cmdshell 'dtsrun /S"MYSERVER01" /E /N"dts package name"'
-- to use Windows authentication
When I run it as a job, it says it runs successfully, but it takes less than a second and nothing happens.
When I run the above code in Query Analyzer (as the login used in the job's owner), I get the following error:
Error string: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Error source: Microsoft OLE DB Provider for SQL Server
Any assistance is appreciated.
-
Check the connections in the DTS package. How are they connecting to SQL Server? Are they using the same credentials as the owner of the job?
--------------------
Colt 45 - the original point and click interface
-
We're using windows authentication on all server connections. Must I use a SQL Server authentication?
-
As shown, this will be using, not the windows login of the job (or you), but a login chosen as follows:
When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running.
When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account.
So, check the permissions of these logins related to what you are attempting to do.
-
I was using SA to run the jobs, or sometimes run the job under my login. The DTS runs fine when I execute it from Enterprise Manager.
-
What account do the SQL Server and SQL Agent services use?
--------------------
Colt 45 - the original point and click interface
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply