1

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?

2
  • 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 Mar 15, 2012 at 5: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, 2012 at 10:32

1 Answer 1

0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .