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?