Reporting across multiple servers, login fails for user NT AUTHORITY\ANONYMOUS LOGON

Category: Troubleshooting & error messages
Date: 24 Mar 2009
Product: SQL Backup
When generating a report for multiple servers, SQL Backup requires that a central reports database is created with Linked Servers.

When creating the report, the generation process may produce an error when updating server history, as follows:

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

This message indicates that you are attempting to access a linked server by using Windows NT authentication to impersonate the client connecting.
NTLM authentication does not support double hop because security tokens and hashes are only valid for the computer where they are generated.
For example, suppose the following configuration exists:
  · You want to create a report of backup history for instances of SQL Server installed on servers A and B.
  · You want to generate the report on a SQL Backup console on a client computer C.
  · The server hosting the SQL Backup reporting database is server A, meaning SQL Backup will create a linked server on A, linking to B.

Given this configuration, the following applies:
Clients on server A can connect to server A by means of Windows NT authentication, and will be able to successfully run queries against linked server B under their own Windows NT security account (assuming they have been granted appropriate access). The Windows NT credentials only have to make a "single hop" from server A to server B.
Clients on client C can successfully connect to server A by means of Windows NT authentication. However, if they try to run queries against linked server B, they fail with Msg 18456. This is considered a "double hop" because the Windows NT credentials from client C are sent to server A initially, and the second hop from server A to server B for the remote query is not supported.

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

Modify the linked server configuration on server A for the linked server named B. Choose the option "Be made using this security context" and choose a SQL Authentication account that is valid for server B and enter that account's password and save the configuration.

For an instance of SQL Server that is running on a Microsoft Windows 2000-based computer, a second option would be to 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.

This Microsoft article contains further information:
http://support.microsoft.com/?id=238477