I'm going nuts with the same problem! I remember doing this with 2000 was always easy. I have been all over google and I can't get this to work. Exact same setup, both servers running on a domain account, Windows auth.
I'm trying to use named pipes instead of TCP and at least I get a different error:
EXEC sp_addlinkedserver
@server='statler',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='np:statler',
@provstr='Integrated Security=SSPI'
-- Then I try this:
select net_transport, auth_scheme
from statler.master.sys.dm_exec_connections
where session_id=@@spid
/*
Getting closer, but still fails:
OLE DB provider "SQLNCLI" for linked server "statler" returned message
"Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "statler" returned message
"An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow
remote connections.".
Msg 5, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [5].
OLE DB provider "SQLNCLI" for linked server "statler" returned message
"Invalid connection string attribute".
*/
This might have something to do with enabling names pipes, but I can connect via sqlcmd from server A to server B like this:
WALDORF:> Sqlcmd.exe /E /Snp:statler
If I don't used named pipes, and just do:
New Linked Server
Server Type: SqlServer
Security: be made using the current login's security context
I get this:
Login failed for user NT AUTHORITY\ANONYMOUS LOGIN
[Edit] I started a discussion on Sql Server Central about this. Basically, you have to do some complicated configuration related to Kerberos delegation to get this to work.
http://www.sqlservercentral.com/Forums/Topic574262-146-1.aspx
I decided to just create a single, limited Sql Login account to handle the linked queries. I hate resorting to that, but it seems more secure than the changes you have to make to get it working with windows auth.