[Powershell] Automating SFTP Jobs

DISCUSSION

I’ve been working with a Managed File Transfer (MFT) tool to download files from a remote SFTP site. But here’s the thing—I ran into a bit of a problem. The MFT tool keeps creating tons of separate connections in a short amount of time, and my firewall isn’t too thrilled about it. To solve this, I’m putting together a PowerShell script with some help from WinSCP to download the files in a smoother, more controlled way.

SECURITY

When it comes to security, I wanted to be extra careful about handling login credentials. Nobody wants their username and password sitting around in plain text—that’s just asking for trouble! The good news is that PowerShell 7.0 and above offers a great solution: we can create a secure hash value of our login credentials instead. This way, we can keep our authentication details safe while still getting the job done.

PowerShell Script to Get Secure String Hash Values

$ssUsername = Read-Host "Enter your username" -AsSecureString
ConvertFrom-SecureString $ssUsername
$ssPassword = Read-Host "Enter your password" -AsSecureString
ConvertFrom-SecureString $ssPassword

WinSCP Host Key

Use WinSCP app to connect to your SFTP Server in order to copy the Host Key

XML Configuration File

<Configuration>
   <url>sftp.testserver.com</url>
   <un>01000000d08c9ddf0115d1118c7a00c04fc297eb010000005f2316a089ea5c4aa1a0c5bb003944230000000002000000000010660000000100002000000048da96290df258d432ceaabdd1ae3e54005f661f04ac4d75c47772af5739fa47000000000e800000000200002000000026aaa2c5378d6ac71c9ce2e265f5140ce4661505a9a897fca8fb4c046e1c3fea20000000b5dd06d302700e7cb9c3c0ae71149de671c5d231ac08788af50d52745d3447d3400000004578c32ee687d53329b4d736ba5407553b48c5e9f3abaf8e6d129686481f0aabed2353b88301d227cbb0df1342f4223b6b50764fee54228e4734ac41c2df7e40</un>
   <pw>01000000d08c9ddf0115d1118c7a00c04fc297eb010000005f2316a089ea5c4aa1a0c5bb0039442300000000020000000000106600000001000020000000d71f31809cbb2ecc138b79f1c3f199e69c6d04239d92d7258cbcb0142a4ffa6a000000000e8000000002000020000000240118917eb8e954eb4f8caec779c5709c3af8c18e18a241e1d24a907c748ba72000000061e0af0ddca3efc5c9221571f12b6d8335ba41c4e8278872f11a32371f7cba3840000000f0d08a5ab75233cd1c091b1ebb45f2b41bcd097d787a376097ef72a6fff0b047cb8982f55fc1e28847b7abfa9460d3bbf768eb728d65cbdbf129f1ff9fec0002</pw>
   <hostkey>ssh-rsa 2048 da:cf:7a:3e:7e:33:89:70:c8:4b:27:3c:93:40:76:9b</hostkey>
</Configuration>

WinSCP Automation DLL

You will need WinSCP Automation Files in order to write this script. Go to https://winscp.net/downloads.php and scroll down to the bottom area to find .NET assembly / COM library and download this folder. I would extract and copy the files to the Program Files (x86) where WinSCP is installed.

Let’s start Scripting

## Declare parameters
param (
    [Parameter(Position=0, Mandatory=$false)]
    $programPath = "C:\Program Files (x86)\WinSCP\netstandard2.0\",
    [Parameter(Position=1, Mandatory=$false)]
    $config = "C:\Scripts\Automation\config.xml",
    [Parameter(Position=2, Mandatory=$false)]
    $logDir = "C:\Scripts\Automation\Logs",
    [Parameter(Position=3, Mandatory=$true)]
    [ValidateSet("PDF", "DOCX", "XSLX")]
    [string] $DocType
)

## Preset locations based on document type
switch ($DocType) {
   "PDF" {
      $localPath = "C:\Downloads\PDF"
      $remotePath = "/PDF"
   }
   "DOCX" {
      $localPath = "C:\Downloads\DOCX"
      $remotePath = "/DOCX"
   }
   "XLSX" {
      $localPath = "C:\Downloads\XLSX"
      $remotePath = "/XLSX"
   }
}

## MAIN PROGRAM ##
try
{
    Write-Host "Starting to download $($DocType)" -ForegroundColor Green
    
    # Read Config file
    [xml]$xmlConfig = Get-Content -Path $config
    
    # Load WinSCP .NET assembly
    Add-Type -Path (@($programPath, 'WinSCPnet.dll') -join '')
 
    # Setup session options
    $sessionOptions = New-Object WinSCP.SessionOptions -Property @{
        Protocol = [WinSCP.Protocol]::Sftp
        HostName = $xmlConfig.Configuration.url
	PortNumber = 22
	UserName = ConvertTo-SecureString -String $xmlConfig.Configuration.un | ConvertFrom-SecureString -AsPlainText
        SecurePassword = ConvertTo-SecureString -String $xmlConfig.Configuration.pw
        SshHostKeyFingerprint = $xmlConfig.Configuration.hostkey
    }

    # Create a WinSCP session object
    $session = New-Object WinSCP.Session
 
    try
    {
        # Initiate the connection
        $session.Open($sessionOptions)
 
        # Move files from remote to local and capture the results to a variable
	$transferResult = $session.GetFilesToDirectory($remotePath, $localPath, $null, $true)

        # Process the result and store it in a JSON file for other purposes
	if ($transferResult.Transfers.Count -gt 0) {
	   $transferResult | ConvertTo-Json -Depth 10 | Set-Content "$($logDir)\$($DocType)_$(Get-Date -Format "yyyy_mm_dd_HHmmss").json"
	   
           # Display the list of files that were downloaded
           ForEach ($fileTransfer in $transferResult.Transfers) {
               $fileList += "$($fileTransfer.FileName)`n"
           }
           Write-Host $fileList
        }
    }
    finally
    {
        # Disconnect session and clean up
        $session.Dispose()
    }

    # Close the PowerShell window
    exit 0
}
catch
{
    # Output Exception
    Write-Host "Error: $($_.Exception.Message)"
}

Uploading Files to Remote Site

# Declare Backup Path variable
$backupPath = "C:\Automation\Backup"
# Upload files, collect results
$transferResult = $session.PutFiles($localPath, $remotePath)
 
# Iterate over every transfer
foreach ($transfer in $transferResult.Transfers)
{
      # Success or error?
      if ($transfer.Error -eq $Null)
      {
         Write-Host Download of $($transfer.FileName) succeeded, moving to backup"
                
         # Upload succeeded, move source file to backup
         Move-Item $transfer.FileName $backupPath
       }
       else
       {
            Write-Host "Upload of $($transfer.FileName) failed: $($transfer.Error.Message)"
       }
}