Local login impersonation not working with a linked server

Question

I have created a linked server on my server using the following:

exec sp_addlinkedserver
    @server=N'MyOldDB',
    @srvproduct=N'',
    @provider = N'SQLNCLI',
    @datasrc=N'MYSERVER',
    @catalog=N'TheDefaultDatabase';

Now, despite the following description of how logon credentials are determined (from MSDN):

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 user credentials of the local login when connecting to the linked server on behalf of the login. This is equivalent to executing sp_addlinkedsrvlogin with @useself set to true for the linked server, without specifying a local user name.

I am getting the following error:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

When I try to do this:

select top 10 * from MyOldDB.TheDefaultDatabase.dbo.Batches

Can anyone point me in the right direction here?

Answer 1

The error you have is unrelated to logins as such.

This is caused when SQL Server tries to "pass through" the NT login token to the remote server. It doesn't have permission to pass the token through. The remote server looks for this because the local servers connects with Integrated Security.

You need "Security Account Delegation" to be configured for the local server.

As for the default mapping...

What MSDN says is that if you have "MyDomain\bob" locally then you have an "virtual" entry "stored" locally called "MyDomain\bob". There is no reconciliation of the 2 servers between the 2 servers. This is the same as running this:

EXEC sp_addlinkedsrvlogin 'MyLinkedServer', @useself = 'TRUE';

Note that "MyDomain\MyGroup" is not mapped: only discrete NT users and SQL logins. Not NT Groups.

This is all described in "Security for Linked Servers"

The default mapping for a linked server configuration is to emulate the current security credentials of the login. This kind of mapping is known as self-mapping. When a linked server is added by using sp_addlinkedserver, a default self-mapping is added for all local logins. If security account delegation is available and the linked server supports Windows Authentication, self-mapping for the Windows authenticated logins is supported.

...

If security account delegation is not available on the client or sending server, or the linked server/provider does not recognize Windows Authentication Mode, self-mapping will not work for logins that use Windows Authentication.