Powershell script executing within SQL Server Agent

Question

I am trying to use Powershell within a SQL Server Agent job to download a zip file. The script uses PuTTY (PSCP.exe) to download a zip file from a SFTP site.

The issue I am having is that when the job runs it connects to the SFTP site and PuTTY sends a prompt back about storing the server's host key in the registry. I don't want to do this so I am trying to pipe the echo n command to PuTTY. This doesn't seem to be working though.

$SrcPath = "/somedirectory/somewhere/files/"
$DstPath = "D:\Download\"
$currentDate = (Get-Date).ToString('yyyyMMdd')
$FileName = "$currentDate.zip"
$ArchivePath = "D:\Archive\"

$File = "$SrcPath$FileName"

Set-Location $DstPath

echo n | C:\"Program Files (x86)"\PuTTY\pscp -P 99999 -pw password username@{IP_ADDRESS}:$File $DstPath
# Check the file is there
If (Test-Path "$DstPath$FileName")
{
  # Unzip the contents
  C:\"Program Files"\7-Zip\7z.exe e "$DstPath$FileName"
  #Move the zip file to the archive directory
  Move-Item $DstPath$FileName $ArchivePath -force
}

If I open up a command window and do this manually it works fine.

e.g. Typing this in the command window

C:\"Program Files (x86)"\PuTTY\pscp -P 99999 -pw password username@{IP_ADDRESS}:/somedirectory/somewhere/files/20121025.zip D:\Download\

and then pressing n when the prompt appears, downloads the files correctly.

When the job runs via SQL Agent, it errors initially complaining about the host key and then states that the file does not exist.

Can anyone point me in the right direction?

Answer 1

Your first problem is that Putty/PSCP want to store the host key for each user for security reasons (stop Fred from storing a fake host key that can be used to con George into trusting a fake server). The -batch option won't override that as it's seen as a flaw in the security process.

So it's fine when you run it interactively, as you can accept the key for your account. When you run it via SQL Agent, then it wants to store it for the user running the SQL Agent service.

If you are running SQL Agent under a 'normal' user account, then one way around this is to log in interactively with that account, run the pscp command and accept the host key. This will then be stored for future runs.

The other option is to look at using another tool for the SCP functionality. Personally I'm a fan of winscp ( http://winscp.net/ ) for this. WinSCP allows you to specify the host key as part of the connection string (here is a PowerShell example - http://winscp.net/eng/docs/library#powershell ).

Answer 2

For those like me who got here from Google: pscp now has a -hostkey option which can be used like this:

pscp -P 22 -hostkey "ssh-ed25519 255 aa:bb:cc..." ...

This eliminates the need for an interactive PuTTY session to store the host key. Here's what the documentation has to say:

-hostkey key

Specify an acceptable host public key. This option may be specified multiple times; each key can be either a fingerprint (99:aa:bb:...) or a base64-encoded blob in OpenSSH's one-line format.

Specifying this option overrides automated host key management; only the key(s) specified on the command-line will be accepted (unless a saved session also overrides host keys, in which case those will be added to), and the host key cache will not be written.

Answer 3

Looks like pscp has a -batch switch which disables interactive prompts as per here.

Try something like:

C:\"Program Files (x86)"\PuTTY\pscp -P 99999 -pw password username@{IP_ADDRESS}:$File $DstPath -batch