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';

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 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 '12 at 10:32

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.