Message 18456 from a distributed query

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: 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: This problem occurs when SQL Server uses the TCP/IP Server network library or the Multiprotocol Server network library to listen for client requests.

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.

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.