Add a linked server & remote login to Sql Server via Powershell

Sort of.  Trying to do this via just SMO and powershell was immensely frustrating when dealing with the remotelogin security.  What I wanted the login to do was to use a sql login no matter the context (the bottom-most radio button in the screenshot below).

image

 

Alas, every time I set the remotelogin property and called the SetRemotePassword() method it would automatically add it to the Local server login to remote server login mappings list.

image

Not what I want.  In order to determine the proper…properties to set I created a new linked server and scripted it out to have a gander at the properties.

image

Two properties stood out.  The @locallogin and the @useself.  Okay, cool.  Easy enough, the smo linkedserverlogin has a properties collection in it, should be easy enough to set.  Except, the bloody properties never get populated.  Even after calling the Initialize($true) on the LinkedServerLogin smo object, said properties are not there. 

Okay, let’s add them.  Nope.  There is no Add() method on the SqlPropertyCollection (or on the smo PropertyCollection inherited class).  In the end, I ended up just having to execute a sql call in order to get this to work, as I’d already spent 4 stinking hours trying to get this to work correctly.  I detest having to resort to doing this, but I HAVE to move on.

Here it is.  If you have this figured, please please let me know what the poop is.

Import-Module sqlps -DisableNameChecking

$srvName = 'ServerName'
$linkedServerLogin = 'LinkedServerLoginName'
$pwd = 'LinkedServerPassword'
$servers = gc -Path C:\LinkedServers.txt

cls

try{
    $srv = New-Object Microsoft.SqlServer.Management.smo.server $srvName
    $servers | %{
        if($srv.LinkedServers.Contains($_)){
            return;
        }
        
        $linkedServer = New-Object Microsoft.SqlServer.Management.Smo.LinkedServer 
        $linkedServer.Parent = $srv
        $linkedServer.Name = $_
        $linkedServer.DataSource = $_
        #$srv.LinkedServers.Add($linkedServer);
        $linkedServer.Create();

        $sql = "
        USE [master]
        GO
        EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'$($_)', @locallogin = NULL , @useself = N'False', @rmtuser = N'$($linkedServerLogin)', @rmtpassword = N'$($pwd)'
        GO
        "
        $srv.ConnectionContext.ExecuteNonQuery($sql);

    }
}
catch{
    $_ | fl -Force
}
finally{

}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: