Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' for insert reference using New Linked Server

• AbdullahGhaleb

  How did you setup the linked server Seems that it is not passing the actual credentials. Are you logged in with sa Try to create the SP in QA rather than in the SQL EM, I think this won’t do any schema check against the linked server.
  
  
  HTH, Jens Suessmeyer.

  ---
  http://www.sqlserver2005.de
  ---

  
  

• WinFX_Girl

  Hi ,

  Please go through the following KB article.

  http://support.microsoft.com/ id=238477

  WORKAROUND

  To work around this problem, use one of the following methods:

  ?

  Map the clients on server A to a standard security login on server B, by using either the sp_addlinkedsrvlogin stored procedure or the Security tab of the Linked Server Properties dialog box in Enterprise Manager.

  ?

  If you are running the distributed query on an instance of SQL Server that is running on a Microsoft Windows 2000-based computer, configure SQL Server to listen for client requests by using the Named Pipes Server network library, instead of using the TCP/IP Server network library or the Multiprotocol Server network library. To configure the Server network libraries for SQL Server, use the Server Network Utility.

  Best Regards

  Rajesh Basa

  
  

• Laurence Fass

  hello flaovorfave,

  you are running on permission problems hope this helps.

  sp_addlinkedsrvlogin

  Creates or updates a mapping between logins on the local instance of MicrosoftR SQL Server and remote logins on the linked server.

  Syntax

  sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
  [ , [ @useself = ] 'useself' ]
  [ , [ @locallogin = ] 'locallogin' ]
  [ , [ @rmtuser = ] 'rmtuser' ]
  [ , [ @rmtpassword = ] 'rmtpassword' ]

  Arguments

  [@rmtsrvname =] 'rmtsrvname'

  Is the name of a linked server that the login mapping applies to. rmtsrvname is sysname, with no default.

  [@useself =] 'useself'

  Determines the name of the login used to connect to the remote server. useself is varchar(8), with a default of TRUE. A value of true specifies that SQL Server authenticated 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. If rmtuser and rmtpassword are also set to NULL, no login or password is used to connect to the linked server. true for useself is invalid for a Windows NT authenticated login unless the Microsoft Windows NTR environment supports security account delegation and the provider supports Windows Authentication (in which case creating a mapping with a value of true is no longer required but still valid).

  [@locallogin =] 'locallogin'

  Is a login on the local server. locallogin is sysname, with a default of NULL. NULL specifies that this entry applies to all local logins that connect to rmtsrvname. If not NULL, locallogin can be a SQL Server login or a Windows NT user. The Windows NT user must have been granted access to SQL Server either directly, or through its membership in a Windows NT group granted access.

  [@rmtuser =] 'rmtuser'

  Is the username used to connect to rmtsrvname when useself is false. rmtuser is sysname, with a default of NULL.

  [@rmtpassword =] 'rmtpassword'

  Is the password associated with rmtuser. rmtpassword is sysname, with a default of NULL.

  Return Code Values

  0 (success) or 1 (failure)

  Remarks

  When a user logs on to the local server and executes a distributed query that accesses a table on the linked server, the local server must log on to the linked server on behalf of the user to access that table. Use sp_addlinkedsrvlogin to specify the login credentials that the local server uses to log on to the linked server.

  A default mapping between all logins on the local server and remote logins on the linked server is automatically created by executing sp_addlinkedserver. The default mapping states that SQL Server uses the local login's user credentials when connecting to the linked server on behalf of the login (equivalent to executing sp_addlinkedsrvlogin with @useself set to true for the linked server). Use sp_addlinkedsrvlogin only to change the default mapping or to add new mappings for specific local logins. To delete the default mapping or any other mapping, use sp_droplinkedsrvlogin.

  Rather than having to use sp_addlinkedsrvlogin to create a predetermined login mapping, SQL Server can automatically use the Windows NT security credentials (Windows NT username and password) of a user issuing the query to connect to a linked server when all these conditions exist:

  • A user is connected to SQL Server using Windows Authentication Mode.
    
    
  • Security account delegation is available on the client and sending server.
    
    
  • The provider supports Windows Authentication Mode (for example, SQL Server running on Windows NT).

  After the authentication has been performed by the linked server using the mappings defined by executing sp_addlinkedsrvlogin on the local SQL Server, the permissions on individual objects in the remote database are determined by the linked server, not the local server.

  sp_addlinkedsrvlogin cannot be executed from within a user-defined transaction.

  Permissions

  Only members of the sysadmin and securityadmin fixed server roles can execute sp_addlinkedsrvlogin.

  
  
  
• Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' for insert reference using New Linked Server