Home / Databases / SQL Server

DatabasesHow-toIntermediate

Calling WinSCP from a SQL Agent job: script files, host keys, logging and exit codes

The basics of running WinSCP's command-line client from a SQL Server Agent CmdExec step without it hanging, prompting or failing silently. Pin the host key, use key authentication, write a session log and let WinSCP's exit code fail the step.

Deepak Bhardwaj3 min read

Applies toSQL Server 2016–2025 on Windows, WinSCP 6.x (winscp.com console client)

Why jobs with WinSCP go wrong

  • WinSCP asks "Continue connecting and add host key to cache?". Agent has no console, so the job hangs until someone kills it.
  • The script keeps going after an error and the step reports success.
  • Nobody knows what was transferred, because nothing was logged.

1. Install and use the console client

Install WinSCP on the SQL Server host (or copy the portable version to D:\Tools\WinSCP). Always call winscp.com, the console version, not winscp.exe.

2. Get the host key fingerprint once

Connect interactively from your own session (or ask the partner for the fingerprint), then copy it:

ssh-ed25519 255 Y9f0wN3x2b...=

You pin this in the script, so the job never prompts and refuses if the server's key changes.

3. Write a script file

D:\Jobs\sftp\download_rates.txt:

option batch abort
option confirm off
open sftp://ratesfeed@sftp.partner.example/ -hostkey="ssh-ed25519 255 Y9f0wN3x2b...=" -privatekey="D:\Jobs\sftp\keys\ratesfeed.ppk" -rawsettings ReconnectTime=60
cd /outbound/rates
get -neweronly *.csv D:\Inbound\Rates\
exit
  • option batch abort: any prompt aborts instead of waiting.
  • option confirm off: no overwrite questions.
  • Key authentication (-privatekey) avoids storing a password. Protect the key file's ACL so only the job's account can read it.

4. The job step

EXEC msdb.dbo.sp_add_jobstep @job_name = N'SFTP - Download Rates', @step_name = N'WinSCP get',
     @subsystem = N'CmdExec',
     @command = N'"D:\Tools\WinSCP\winscp.com" /ini=nul /log="D:\SQLAgentLogs\winscp_rates_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).log" /loglevel=0 /script="D:\Jobs\sftp\download_rates.txt"',
     @cmdexec_success_code = 0,
     @proxy_name = N'SFTP Jobs',
     @retry_attempts = 2, @retry_interval = 5;
Switch Purpose
/ini=nul Don't read or write a WinSCP config. Makes runs reproducible
/log=... Session log per run, so you see exactly what was transferred
/script=... Commands from the file
Exit code 0 = success, 1 = any error. @cmdexec_success_code = 0 fails the step on 1

5. Run it as the right account

The proxy account needs: read on the script and key file, write on the target folder and log folder, and outbound network access to the SFTP host (firewall rule on port 22).

Troubleshooting

Log line / symptom Cause
Host key does not match configured key Server key changed. Confirm with the partner before updating the fingerprint
Authentication failed Wrong user/key, or key not in OpenSSH/PuTTY format WinSCP expects (convert with PuTTYgen or WinSCP)
Network error: Connection timed out Firewall or proxy. Test with Test-NetConnection sftp.partner.example -Port 22
Step hangs Missing option batch abort, or winscp.exe used instead of winscp.com
Can't open file Proxy account can't read script/key or write target

Warning

Never use -hostkey=* (accept any key) in production. It removes the protection against connecting to the wrong server.

Next

For moving files after download, archiving, checking for empty folders and handling partial failures, see the end-to-end SFTP automation post with PowerShell and the WinSCP .NET assembly.

Related fixes