locked
Linked server failure RRS feed

  • Question

  • Linked server is configured with SQL Native client. Unable to connect linked server getting below error.

     

    MSG 18456, Level 14, State 1, Line 1

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'


    Please Vote & "Mark As Answer" if this post is helpful to you. Cheers Prakash Nandwana Bangalore , India
    Tuesday, May 11, 2010 3:22 PM

Answers

All replies

  • In SQL Server Management Studio, expand the server, expand Server Objects, expand Linded Server, right-click your linked server, and then click Properties.

    On the Security page, you might have something misconfigured. (I suspect you have selected "Be made without using a security context.") In most cases, you don't need anything in the topc box, but in the bottom section, you most likely want "Be made using the login's current security context." That means, users of the linked server will connect as themselves to the linked server. But if you are using SQL Server authentication you might need to specify what user context should be used. Or if you want the connection to always be made using a certain context you need to provide it.


    Microsoft, SQL Server Books Online
    Tuesday, May 11, 2010 3:43 PM
  • I have shared my logshipping script here, The user is a member of group account. The user has database level access. The databases are different on both servers although same group account is available on both server.

     

    /****** Object:  LinkedServer [SERVER2]    Script Date: 05/12/2010 08:01:10 ******/
    EXEC master.dbo.sp_addlinkedserver @server = N'SERVER2', @provider=N'SQLNCLI', @datasrc=N'SERVER2'
     /* For security reasons the linked server remote logins password is changed with ######## */
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SERVER2',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

    GO
    EXEC master.dbo.sp_serveroption @server=N'SERVER2', @optname=N'collation compatible', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'SERVER2', @optname=N'data access', @optvalue=N'true'
    GO
    EXEC master.dbo.sp_serveroption @server=N'SERVER2', @optname=N'dist', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'SERVER2', @optname=N'pub', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'SERVER2', @optname=N'rpc', @optvalue=N'true'
    GO
    EXEC master.dbo.sp_serveroption @server=N'SERVER2', @optname=N'rpc out', @optvalue=N'true'
    GO
    EXEC master.dbo.sp_serveroption @server=N'SERVER2', @optname=N'sub', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'SERVER2', @optname=N'connect timeout', @optvalue=N'0'
    GO
    EXEC master.dbo.sp_serveroption @server=N'SERVER2', @optname=N'collation name', @optvalue=null
    GO
    EXEC master.dbo.sp_serveroption @server=N'SERVER2', @optname=N'lazy schema validation', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'SERVER2', @optname=N'query timeout', @optvalue=N'0'
    GO
    EXEC master.dbo.sp_serveroption @server=N'SERVER2', @optname=N'use remote collation', @optvalue=N'true'


    --------------------------------------------------------------------------------
    Please Vote & "Mark As Answer" if this post is helpful to you. Cheers Prakash Nandwana Bangalore , India

    Wednesday, May 12, 2010 7:23 AM
  • Hi Prakash,

    The option useself only works good only if the account accessing the linked servers have their windows login to be same in both the servers ( like a domain account) . For instance , if you using a local windows account to access a linked server which was configured with useself option , it wont be able to verify your credentials at the second server and thats when you see errors for anonymous logon .

    Are you using a domain account account to access the SQL server which has access to both the local sql server and the linked one ?


    Thanks, Leks
    Wednesday, May 12, 2010 7:59 AM
  • Hi Lekss

    I am using the SYSADMIN account to create the linked server which is able to test connection successfully. But a login in group account which has database level access db_datareader is unable to access it. Please note that both servers have different database available.


    Please Vote & "Mark As Answer" if this post is helpful to you. Cheers Prakash Nandwana Bangalore , India
    Wednesday, May 12, 2010 8:46 AM
  • Linked server is configured with SQL Native client. Unable to connect linked server getting below error.

     

    MSG 18456, Level 14, State 1, Line 1

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'


    Please Vote & "Mark As Answer" if this post is helpful to you. Cheers Prakash Nandwana Bangalore , India


    Please refer below link :

     http://social.msdn.microsoft.com/Forums/en/transactsql/thread/8ec97763-c978-48dd-a92c-4b47e6a959d7


    Paresh Prajapati
    http://paresh-sqldba.blogspot.com/
    LinkedIn | Tweet Me | FaceBook | Brijj
    Friday, May 14, 2010 9:05 AM