I used to be able to do this :-((((
I'm trying to define a new linked server from server A to server B and I always hit the connection refused error.
In the Linked server definition, I force the connection to use the service account that runs both instances and that is part of the administrator group on each machine.
TCP Provider: No connection could be made because the target machine actively refused it.
OLE DB provider "SQLNCLI" for linked server "bla bla bla" returned message "Login timeout expired".
ve tried with :@provstr=N'Integrated Security=SSPI;' and @provstr=N'Integrated Security=TRUE;'
One detail, ONLY TCPIP protocol is enabled on these servers. Shared Memory, Named Pipes and VIA are disabled and we are not using Kerberos. Security is Windows on both servers.
This is the script that I used:
EXEC master.dbo.sp_addlinkedserver @server = N'MYSERVER\MYINSTANCE', @provider=N'SQLNCLI',@provstr=N'Integrated Security=True;',@srvproduct=''
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MYSERVER\MYINSTANCE',@useself=N'False',@locallogin=NULL,@rmtuser=N'MYDOMAIN\$Myuser',@rmtpassword='hahaha'