0

I'm trying to run an SSIS 2005 package from a SQL Server Agent job on my local PC. This package is attempting to connect to a SQL Server 2005 database on a remote server, but it gets the error message, Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. What is the problem and how can I solve it?

Laurel
  • 5,965
  • 14
  • 31
  • 57
user317178
  • 1
  • 1

3 Answers3

1

you can create a SQL job to run the start_execution command and then the process will run under the user running the job.

the approach is explained here.

user3140982
  • 135
  • 1
  • 3
  • 8
Pavel Gr.
  • 141
  • 2
  • 9
  • Note that [link-only answers are discouraged](http://meta.stackoverflow.com/tags/link-only-answers/info), SO answers should be the end-point of a search for a solution (vs. yet another stopover of references, which tend to get stale over time). Please consider adding a stand-alone synopsis here, keeping the link as a reference. – kleopatra Jan 13 '14 at 10:15
0

The problem is that the SSIS package is being executed under the NT credentials of the account running the SQL Server Agent service (likely to be either your local PC's "local system" or "network service" account), and is attempting to connect to the remote SQL instance with windows integrated security. This fails because the remote SQL server cannot authenticate the service account as permitted to connect.

When you developed it, the package was connecting to the remote SQL server using your NT credentials (a domain account which the remote SQL server could authenticate).

The quickest fix will be to change the service account under which your SQL Server Agent service runs to a domain account with rights to connect to the remote SQL server. To prove that this is the issue you could use your own domain account for this, although this isn't really a long term solution.

Ed Harper
  • 21,127
  • 4
  • 54
  • 80
  • Thank you, Ed. This is exaclty what the DBAs just told me to do. What would be the long term solution? – user317178 Apr 15 '10 at 16:09
  • @user317178 - longer-term, the solution would be to get an NT account created specially for the SQL Agent service to run under, which can be granted rights to only those resources actually needed to run the job. You may not need to worry about this unless the job goes onto a production system. – Ed Harper Apr 15 '10 at 17:32
-1

Another thing to consider if above fails is "hop count". If I try to run a SP from a local machine that is connected to remote DB that is trying to connect to another remote DB. I will get the error above. However, if I remote connect to the primary server first, then run the SP with the remote connection, it works fine.

Robert F.
  • 1
  • "hop count" is usually talked about regarding networking, so it's incorrectly used here. Instead, we're effectively talking about the need for pass-through authentication. The problem can be resolved by setting up the authentication on both source and target services using explicit credentials. The failure occurs because the source and target computers do not inherently trust each other - nor should they. Explicit credentials will solve your problem. – Lizz Nov 04 '12 at 20:30