PRB: Message 18456 from a Distributed Query

ID: Q238477

The information in this article applies to:

Microsoft SQL Server, Enterprise Edition, version 7.0

SYMPTOMS

When you run a distributed query against a SQL Server linked server, you receive the following message:

Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user '\'.

CAUSE

This message indicates that you are attempting to access the linked server using Windows NT authentication to impersonate the client connecting. With Windows NT 4.0, delegation of credentials (sometimes called a "double hop") is not supported.

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 under Windows NT 4.0.

WORKAROUND

To work around this problem, 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.

Additional query words: err error dist

Keywords : kbSQLServ700
Version : winnt:7.0
Platform : winnt
Issue type : kbprb