I'm trying to set up a linked server between 2 instances of Sql Server 2008 R2.
The login works perfectly when I select the impersonate option. However when I try to use the remote user/password option it fails, even though the remote user is the same as the local user being impersonated.
The user account I'm trying to connect with is a windows admin on both machines.
Unfortunately, I need to be able to connect to machines that the local user doesn't have admin rights to (though I will have the username/password of an admin when this occurs), so impersonation won't work.
Where am I going wrong?