Adding a linked server via Powershell

Question

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?

In powershell is there an object for linked servers in the `SQL:` drive? I can't remember off the top of my head, but that could help with some interactive debugging — Mark Henderson, Mar 15 '12 at 05:43
Can you, using the same user account, successfully perform the same action with `sqlcmd.exe` (or other SQL Server tool)? (Ie. is this a PowerShell issue, or a SQL access issue?) — Richard, Mar 15 '12 at 10:32

score 0 · Answer 1 · answered Mar 22 '12 at 07:23

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:

#Get a server object which corresponds to the default instance
$svr = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server

#Create a linked server object which corresponds to an OLEDB type of SQL server product
$lsvr = New-Object -TypeName Microsoft.SqlServer.Management.SMO.LinkedServer -argumentlist $svr,"OLEDBSRV"

#When the product name is SQL Server the remaining properties are not required to be set. 
$lsvr.ProductName = "SQL Server"

#Create the Database Object
$lsvr.Create()

You can specify the credentials for your local SQL server using a ServerConnection object and for the linked server using a LinkedServerLogin object.

Adding a linked server via Powershell

1 Answers1