SFTP file transfer error - connecting to an unknown server and add its host key

I have created the batch to transfer the file using SSH keys, I checked the public and private key mapping on both the servers and it's working fine.

My Windows batch code using SFTP command is as follows:

open sftp://sftp_user@ssh_dest_server -privatekey="D:\directory_full_path\private_key.ppk" -rawsettings TryAgent=0 AuthKI=0 AuthGSSAPI=0

CD "/XYZ_Directory/folder1"
Put "\\full_directory_path\FILE1.zip"

exit

When I execute the batch manually it's executing fine without any issue, but when I execute batch from SQL Job (using different user) then it's shows below error:

Searching for host...
Connecting to host...
Authenticating...
Continue connecting to an unknown server and add its host key to a cache?
The server's host key was not found in the cache. You have no guarantee that the server is the computer you think it is.
The server's RSA key details are:

    Algorithm:  ssh-rsa 2048
    SHA-256:    finger_print_key
    MD5:        zz:xx:yy:xx:yy:xx:yy:xx:yy:xx:yy:xx:yy:zz:zz:00

If you trust this host, 
    press Yes. To connect without adding host key to the cache, 
    press No. To abandon the connection press Cancel.

In scripting, you should use a -hostkey switch to configure the expected host key.
(Y)es, (N)o, C(a)ncel (10 s), (C)opy Key, (P)aste key: Cancel
Host key wasn't verified!
Host key fingerprint is ssh-rsa 2048 finger_print_key.

Authentication failed.

I already tried -hostkey WinSCP command but says "unknown command". Suggestions are most welcome.

Something I want to do like this link "WinSCP" through WinSCP command but inside my Windows batch automatically to verify the host.

Solution

To verify a host key in WinSCP script, add -hostkey switch to the open command:

open sftp://sftp_user@ssh_dest_server -hostkey=... -privatekey="D:\directory_full_path\private_key.ppk" -rawsettings TryAgent=0 AuthKI=0 AuthGSSAPI=0

See Verifying the host key ... in script in WinSCP documentation. It covers everything you need to know. In particular, where to get the host key value.

Also note that WinSCP GUI can generate a script template even with the -hostkey switch for you.

Also covered in My script works fine when executed manually, but fails or hangs when run by Windows Scheduler, SSIS or other automation service. What am I doing wrong?