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?

  • What SQL Agent job type do you have selected? Is it "Powershell"? Commented Oct 26, 2012 at 12:06
  • @PatrickKeisler Yes it is. Commented Oct 26, 2012 at 12:16
  • Have you tried to set it up with a job type "Operating System (CmdExec)"? Commented Oct 26, 2012 at 14:12
  • @PatrickKeisler No I haven't - how would I do that? Commented Oct 29, 2012 at 12:06
  • @Barry: not the job type, but the job step can be changed to "Operating System (CmdExec)". Under the step name you have a Type, though I'm not sure it will help, it might just be a particularity of that pscp tool to request a local key save in the registry.
    – Marian
    Commented Nov 1, 2012 at 17:46

3 Answers 3


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


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.


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
  • 1
    Unfortunately that won't work unless you've already accepted the host key, as acknowledging the host key is seen as part of the secure connection method, as you're confirming that the host you've connected to is the one you expected (prevents man in the middle style exploits). Commented Nov 1, 2012 at 10:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.