Topic "winscp run in sql job"

Author Message
tristonyip
[View user's profile]

Joined: 2014-01-30
Posts: 5
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.
Advertisements
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24552
Location: Prague, Czechia
Please read documentation:
http://winscp.net/eng/docs/scripting#hostkey

See also:
http://winscp.net/eng/docs/faq_scheduler
_________________
Martin Prikryl
tristonyip
[View user's profile]

Joined: 2014-01-30
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
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24552
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).
_________________
Martin Prikryl
tristonyip
[View user's profile]

Joined: 2014-01-30
Posts: 5
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
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24552
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.
tristonyip
[View user's profile]

Joined: 2014-01-30
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.
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24552
Location: Prague, Czechia
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.
tristonyip
[View user's profile]

Joined: 2014-01-30
Posts: 5
thanks, it works, by the way, will the hostkey change if i reboot the server? thanks
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24552
Location: Prague, Czechia
tristonyip wrote:
thanks, it works, by the way, will the hostkey change if i reboot the server? thanks

No, it should not.
_________________
Martin Prikryl
Advertisements

You can post new topics in this forum






Search Site

What is WinSCP?

It is award-winning SFTP client, SCP client, FTPS client and FTP client integrated into one software program for file transfer to FTP server or secure SFTP server. [More]

And it's free!

Donate

About donations

$9   $19   $49   $99

About donations

Recommend

WinSCP Privacy Policy

WinSCP License