Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Have you tried to set it up with a job type "Operating System (CmdExec)"?
–
Patrick KeislerOct 26 '12 at 14:12

@PatrickKeisler No I haven't - how would I do that?
–
BarryOct 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.
–
MarianNov 1 '12 at 17:46

2 Answers
2

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.

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 MooreNov 1 '12 at 10:54