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).

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.

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.

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{

}