To work around this problem, configure the connection between the two SQL Server linked servers to use the Named Pipes protocol instead of the TCP/IP protocol. The Named Pipes protocol does not use Kerberos authentication at the SQL Server level. To configure the connection, follow these steps:
1. Make sure that the Named Pipes protocol is enabled on both linked servers.
To determine whether the Named Pipes protocol is enabled on both linked servers, run the following command at a command prompt on the source computer that is running SQL Server. Assume that the source server name is Server1 and that the target server name is Server2:
SQLCMD –E –Snp:Server2
If this command fails, the Named Pipes protocol is probably not enabled on one or both of the SQL Server linked servers. To enable the Named Pipes protocol, use SQL Server Configuration Manager.
2. If the command in step 1 succeeds, follow one of these steps:
o Drop the existing linked server configuration.
o Create a new linked server configuration that uses a different name and that uses the Named Pipes protocol. Enter the following statements on Server1 to configure a linked server that uses the Named Pipes protocol to connect to Server2.
EXEC sp_addlinkedserver @server='Server2', @srvproduct='', @provider='SQLNCLI', @datasrc='np:Server1', @provstr='Integrated Security=SSPI'
Example: Here i wanted to put a link from sqlcluster to cs8allrep1 so that i could transfer a database over
EXEC sp_addlinkedserver @server='cs8allrep1', @srvproduct='', @provider='SQLNCLI', @datasrc='np:sqlcluster', @provstr='Integrated Security=SSPI'
3. Open SQL Server Management Studio on Server1, and then create a new query. In the query window, run the following linked server query against Server2 to determine whether the Named Pipes protocol is being used.
select net_transport, auth_scheme from Server2.master.sys.dm_exec_connections where session_id=@@spid
If the Named Pipes protocol is being used, you receive the following result.
net_transport auth_scheme
------------- ---------------
Named pipe NTLM