Message 18456 from a distributed query

Article translations Article translations
Article ID: 238477 - View products that this article applies to.
This article was previously published under Q238477
Expand all | Collapse all

SYMPTOMS

When you run a distributed query against a Microsoft SQL Server linked server on a Microsoft Windows NT 4.0-based computer, you receive the following error message:
Server: Msg 18456, Level 14, State 1, Line 1 Login failed for user '\'
When you run a distributed query against a Microsoft SQL Server linked server on a Microsoft Windows 2000-based computer, you receive the following error message:
Server: Msg 18456, Level 14, State 1, Line 1 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
This problem occurs when SQL Server uses the TCP/IP Server network library or the Multiprotocol Server network library to listen for client requests.

CAUSE

This message indicates that you are attempting to access the 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 have SQL Server installed on servers A and B.
  • You have a client computer C.
  • Server B has been set up up as a linked server on server A by means of either of the following:
    • The useself option of the sp_addlinkedsrvlogin stored procedure.

      -or-
    • The They will be impersonated option on the Security tab of the Linked Server Properties dialog box in Enterprise Manager.
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 on SQL Server 7.0 on a Microsoft Windows NT 4.0-based computer.

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.

Properties

Article ID: 238477 - Last Review: June 1, 2004 - Revision: 2.2
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbprb KB238477

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com