Connect to SFTP using Powershell and PSFTP

Quick post on how to use sftp with powershell via PSFTP. The jist of it, is you connect to the sftp server, do an ls (the ls command is contained within an sftp file) and from there use powershell to filter the files you want. While filtering the files, you write the commands (get, in this case) to a command file. You then re-connect to the sftp server and execute the commands in the sftp file you just created. I did look to another post to give me a clue as to how to accomplish this, but I can’t seem to find the site to give proper attribution.

$Error.Clear();
$location = 'c:\FtpFilesDownloaded'
$ignore = @('quit', '/bdn483', '.', '..', '.ssh', 'ls')  #ignore these commands in the return when doing an ls to build the 'get' batch file
$ftpUser = 'ftpUserName'
$ftpPassword = 'ftpPassword'
$ftpGetCommandFile = 'C:\ftpGetCommand.sftp'  #just an ls command
sl $location

if(Test-Path $ftpGetCommandFile){
	Remove-Item $ftpGetCommandFile
}
ls $location | %{  #remove all files in local download location
	Remove-Item $_
}

$lsFiles = & C:\psftp.exe -l $ftpUser -pw $ftpPassword  ftp.ftplocation.com -b C:\lsCommand.sftp -be #the lsCommand just contains an ls command
if($LastExitCode -ne 0){
	throw "Error connecting to ftp..."
}
$colActiveRemoteFiles = $lsFiles | %{$_.Split()[-1]}
$colActiveRemoteFiles | where {$ignore -notcontains $_ } | %{
	"get $_" | Out-File $ftpGetCommandFile -Append -Encoding Ascii
}
& c:\psftp.exe -l $ftpUser -pw $ftpPassword  ftp.ftplocation.com -b c:\ftpGetCommands.sftp -be -bc
if($LastExitCode -ne 0){
	throw "Error downloading files.  "
}

Set PageVerify in Sql Server via Powershell

Quick post on how to set the page verification level in sql server with powershell. This script will set all the user database page verification option to CheckSum if they’re not already set that way. It’s best practice (and the safest option) to have this set to CheckSum. For a more in-depth description of page verification, see here. This uses SQLPSX.

Import-Module SqlServer

Get-SqlDatabase -sqlserver $(Get-SqlServer -sqlserver 'ServerName' -username 'UserName' -password 'Password') | where{-not $_.IsSystemObject} |%{
	if($_.PageVerify -ne [Microsoft.SqlServer.Management.Smo.PageVerify]::Checksum){
		$_.PageVerify = [Microsoft.SqlServer.Management.Smo.PageVerify]::Checksum
		$_.Alter();
	}
}

Using Secure Connections With SQLPS in Powershell

Quick note on how to use secure connections for Sql Server Authentication with SQLPS. First step, you’ll need to create a text file to hold your encrypted password. In this case, it’s just encrypted using the user key of the current logged in user (Windows Data Protection API). Keep this in mind, as your sql server agent will (hopefully) be running under a different account, you’ll need to encrypt the file using that account if you’re planning on calling this via sql jobs. Only the account that created this file will be able to decrypt and read it.

read-host -assecurestring | convertfrom-securestring | out-file C:\Powershell\Password.txt -Force

Next, you’ll need to create a ServerConnection object and pass in your credentials to it. One caveat to note, is that you don’t pass in your encrypted password to the Password property, you have to pass it to the SercurePassword property of the ServerConnection. The Password takes just a regular string as a parameter, whereas the SercurePassword property takes a securestring.

Import-Module SQLPS -DisableNameChecking

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.RMO") | Out-Null

$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
$srvConn.ServerInstance = $InstanceName
$srvConn.Login = "LoginName"
$srvConn.SecurePassword = get-content "c:\Powershell\Password.txt" | convertto-securestring 

$RepInstanceObject = New-Object "Microsoft.SqlServer.Replication.ReplicationServer" $srvConn
$RepInstanceStatus = New-Object "Microsoft.SqlServer.Replication.ReplicationMonitor" $srvConn

In this case I’m using the connection to connect to RMO for a script for monitoring replication, but you can use this same method to connect to anything that accepts a ServerConnection.