Powershell script executing within SQL Server Agent

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?

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

StackExchange Database Administrators Q#27583, answer score: 4