0

How can I make a scheduled task work? I want to schedule a task that runs a query nightly. The query parses correctly and essentially grabs data from the SQL Server 2005 database on server2 and inserts the data into a table in a database on server1. The query and the task run on server1.

I am able to execute the query in Microsoft SQL Server Management Studio on server1. Using SQL Server Agent, I am able to create and schedule a job to execute the query but the job fails with the following error: Executed as user NT AUTHORITY\SYSTEM. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' [SQLSTATE 28000] [Error 18456]. IT at my company speculates that I need to be added to the interactive logon list for server2, which is part of the policy.

I have Admin privileges on both server1 and server2, though the latter should not be necessary I think. Both server1 and server2 use Windows authentication, which I gather is set up upon installation of SQL Server.

Here is what I did. First I added server2 to sys.sysservers on server1 with the following command:
EXEC sp_addlinkedserver @server='server2'

Then I created a mapping between logins on server1 and server2 with: EXEC sp_addlinkedsrvlogin @rmtsrvname = 'server2', @useself = 'true'

That allowed me to to execute the query in Microsoft SQL Server Management Studio on server1. It worked. What I found to be curious was that the following didn’t work although "Command(s) completed successfully":

EXEC sp_addlinkedsrvlogin @rmtsrvname = 'server2', @useself = 'false', @locallogin = 'MYDOMAIN\myname', @rmtuser = 'myname', @rmtpassword = '*****'

(Note that real password is unencrypted because of Windows authentication.).

I got the error Msg 18456, Level 14, State 1, Line 1 Login failed for user 'myname'.

A value of @useself = TRUE specifies that logins use their own credentials to connect to rmtsrvname, with the rmtuser and rmtpassword arguments being ignored. FALSE specifies that the rmtuser and rmtpassword arguments are used to connect to rmtsrvname for the specified locallogin. I would have expected both to work since what I specified for rmtuser and rmtpassword are exactly what I use to log in to the remote server directly.

user2093192
  • 77
  • 1
  • 7

1 Answers1

0

The job always executes as the user specified in the Sql Server Agent service. If your service is running as LocalSystem, then you need to change your add linked server login to

EXEC sp_addlinkedsrvlogin @rmtsrvname = 'server2', @useself = 'false', @locallogin = 'NT AUTHORITY\SYSTEM', @rmtuser = 'myname', @rmtpassword = '*****'

If not localsystem, then specify whatever login used in the agent

Look at this discussion for more info http://www.sqlservercentral.com/Forums/Topic1003064-146-1.aspx#bm1003182

Mani Sundaram
  • 11
  • 1