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"? – Patrick Keisler Oct 26 '12 at 12:06
  • @PatrickKeisler Yes it is. – codingbadger Oct 26 '12 at 12:16
  • Have you tried to set it up with a job type "Operating System (CmdExec)"? – Patrick Keisler Oct 26 '12 at 14:12
  • @PatrickKeisler No I haven't - how would I do that? – codingbadger Oct 29 '12 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 Nov 1 '12 at 17:46

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

| improve this answer | |

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
| improve this answer | |
  • 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). – Stuart Moore Nov 1 '12 at 10:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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