I'm trying to add a linked server to my local SQL Server 2008 R2 setup from within a Powershell script. Here's what I've been trying so far (and variants):
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'<RemoteComputerName>', @useself=N'False' , @locallogin = N'domain\username' , @rmtuser = N'username', @rmtpassword = N'password'; GO
The linking seems to work out alright, but as soon as I try to execte a query (using Invoke-Sqlcmd in Powershell), I receive an error saying that the login failed.
UPDATE: I've also tried linking the servers and executing a simple query in the SQL Server Management Studio. I receive the same results, so the problem isn't likely related to Powershell. Furthermore, I can query against linked servers if I set @useself to true. However, this condition won't always hold in practice (ie my script shouldn't require all the servers to be logged in as the same user).
Am I missing something?