winscp run in sql job

hi
i setup a bat file with winscp scripts to move files to the sftp site, it works fine but when schedule it sql job, i this error, is it a way to run a scheduled job in sql, thanks

Authenticating...
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 rsa2 key fingerprint is:
ssh-rsa 2048 16:9c:f5:5b:12:37:82:1XXXXXXXXXXXXXXXX
If you trust this host, press Yes. To connect without adding host key to the cache, press No. To abandon the connection press Cancel.
Continue connecting and add host key to the cache?
(Y)es, (N)o, C(a)ncel, (C)opy Key: Cancel
Host key wasn't verified!
Authentication failed.
No session.

Please read documentation:
https://winscp.net/eng/docs/scripting#hostkey

See also:
https://winscp.net/eng/docs/faq_scheduler

yes, i use session on the script add the -hostkey ="********" but when i run the job under sql agent, it asked my the password again since is the job run under SQL server, i cannot enter user PW, so it waits for a response and lock the file, i need to reboot the server to unlock the file. bellow is the sample of the my script. thanks


option batch abort
option confirm off


open abc@sample.sam.org//abc/outbound -hostkey="ssh-rsa 2048 16:9c:f5:5b:12:37:82:1XXXXXXXXXXXXXXXX "

put \\myserver\output\*.zip

tristonyip wrote:

yes, i use session on the script

Do you mean stored session/site? That's exactly what the FAQ recommends NOT to do.
The best is to specify your session using session URL and switches of open command. Particularly your example is missing the password. That's why WinSCP prompts for it (assuming the SQL server account does not have access to configuration with your stored session).

yes, but we don't want to show the Password on the script, is it away that SQL agent uses my stored session or switch user to my account? thanks

tristonyip wrote:

yes, but we don't want to show the Password on the script, is it away that SQL agent uses my stored session or switch user to my account? thanks

You can use stored session (while I do not recommend it). You can for example use INI file. Please read the resources linked above, it includes all possible solutions.

hi

i use user name and pw instead of session as you recommended, it works when i log in and run the job in sql but it fails when i schedule the job in sql,
same error.

The server's rsa2 key fingerprint is:
ssh-rsa 1024 20:dd:23:50:8d:69:23:9c:bd***************
If you trust this host, press Yes. To connect without adding host key to the cache, press No. To abandon the connection press Cancel.
Continue connecting and add host key to the cache?
(Y)es, (N)o, C(a)ncel, (C)opy Key: Cancel
Host key wasn't verified!
Authentication failed.

You have to specify the host key fingerprint in the script too. Use -hostkey switch of open command. Again, it's documented in the links above.

thanks, it works, by the way, will the hostkey change if i reboot the server? thanks

tristonyip wrote:

thanks, it works, by the way, will the hostkey change if i reboot the server? thanks

No, it should not.