none
Linked Server

    Question

  • I have two servers(one with SQL 2005 and another SQL 2008).

    I am trying to get one table data from SQL 2008 to Sql 2005 through linked servers in a stored procedure and was succesful when executed from query window.

    I have created job for the same, but the job is getting fail and giving the following error

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    Pls. guide me to rectifying the above

    Thursday, March 01, 2012 10:26 AM

Answers

  • Hi Janos,

    Thanks..problem solved..

    I have selected "be made using security context" and entered sa credentials of remote server in linked server properties security on local machine.

    then it worked.

    Rajesh.

    • Marked as answer by nrajeshkumars Friday, March 02, 2012 10:19 AM
    Friday, March 02, 2012 10:19 AM

All replies

  • Hi,

    When you create an agent job, it is running behalf of a user - eg Agent Sevice account. If your agent service account is not a domain user you may get this error. I used to see this error on sql servers when the service account is local service or local system.

    Try to change the service account to a domain account which has proper permissions and try again.

    I hope it helps.

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog


    • Edited by Janos Berke Thursday, March 01, 2012 11:23 AM
    Thursday, March 01, 2012 11:22 AM
  • Hi Janos,

    Thanks for the reply..

    server1 is  microsoft windows server 2003 r2.. (having sql 2005)

    server2 is  microsoft windows server 2008 r2.. (having sql 2008)

    I have added my login in SQLServer2005SQLAgentUser$AIPL-ERP02$MSSQLSERVER

    which is at mycomputer rightclick --> Manage--> local users and groups.

    still the job getting failed and showing the same error Executed as user: NT AUTHORITY\SYSTEM. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [SQLSTATE 28000] (Error 18456).  The step failed.

    can you guide me to mark this NT AUTHORITY\SYSTEM as domain user.

    Thanks.

    Thursday, March 01, 2012 12:44 PM
  • Hi,

    You cannot. NT AUTHORITY\Local System is a local built in account. You may create proxy for agent or change the SQL Server Agent Service account to a domain user account. You can change it with Sql Sever Configuration Manager.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    Thursday, March 01, 2012 5:07 PM
  • Hi ,

    Can you please provide me the steps to fix this error.

    I have added my user in Run as user at job step advanced option, it is giving the below error.

    The same user is added in remote system and could get the data in normal query, but job is getting failed..

    Executed as user: AIPL\rnarra. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274).  The step failed.

    Thanks,

    Rajesh.

    Friday, March 02, 2012 9:39 AM
  • Hi,

    Please check this article.

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    Friday, March 02, 2012 9:43 AM
  • Hi Janos,

    Thanks..problem solved..

    I have selected "be made using security context" and entered sa credentials of remote server in linked server properties security on local machine.

    then it worked.

    Rajesh.

    • Marked as answer by nrajeshkumars Friday, March 02, 2012 10:19 AM
    Friday, March 02, 2012 10:19 AM