winscp run in sql job

Advertisement

tristonyip
Joined:
Posts:
5

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.

Reply with quote

Advertisement

tristonyip
Joined:
Posts:
5

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

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
40,476
Location:
Prague, Czechia

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

Reply with quote

Advertisement

martin
Site Admin
martin avatar
Joined:
Posts:
40,476
Location:
Prague, Czechia

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.

Reply with quote

tristonyip
Joined:
Posts:
5

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.

Reply with quote

martin
Site Admin
martin avatar

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.

Reply with quote

Advertisement

martin
Site Admin
martin avatar

tristonyip wrote:

thanks, it works, by the way, will the hostkey change if i reboot the server? thanks
No, it should not.

Reply with quote

Advertisement

You can post new topics in this forum