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?
Best Answer
It might be worth your time checking out the SQL Server Management Objects. Here is an example of how you can create Link Servers via SMO:
You can specify the credentials for your local SQL server using a ServerConnection object and for the linked server using a LinkedServerLogin object.