I've gone through literally hundreds of web pages and suggestions on how to remedy this solution but nothing works.
Trying to create a linked server between two 2005 sql servers. The servers are located in different states. They are both joined to the local domain. We had issues initially setting this up, but finally got it working about 4 or 5 months ago. About 1 week ago it stopped working (have no idea, never touch this server), re-booted the server and it worked for a day. After it stopped working, we updated both servers with all updates. This was about the middle of last week and since then it has never worked again. The users have the correct permissions to both servers. It doesn't even work for me from Server Management Studio, I am a sysadmin on both servers.
Here is the tsql I use to create the linked server:
EXEC sp_addlinkedserver @server='SERVERNAME', @srvproduct='', @provider='SQLNCLI', @datasrc='SERVERNAME', @provstr='Trusted_Connection=yes'
The tsql I use to create the linked server login:
EXEC sp_addlinkedsrvlogin 'SERVERNAME','true'
I've tried setting the following options:
exec sp_serveroption 'SERVERNAME','data access','true'
exec sp_serveroption 'SERVERNAME','rpc','true'
exec sp_serveroption 'SERVERNAME','rpc out','true'
I've ran the following sql on both servers and receive the following results:
select net_transport, auth_scheme from master.sys.dm_exec_connections where session_id=@@spid
results from server1 (the server I'm trying to create the linked server on):
results from server2 (the server I'm trying to link to):
I think that they both should be using KERBEROS, but we cannot figure out how to set server1 to use KERBEROS.
I've even tried (which we would prefer not to have to do) hard coding a Remote login and password. This doesn't even work.
Any help on this would be greatly appreciated. Thanks in advance.