Executing WinSCP3.com using SQL server 2005

Hi,
I would like to execute WinSCP3.com using a job in MS SQL Server 2005. The goal is to copy files from a different server to the server hosting the SQL server.

The details are:
WinSCP3.com - ver 1.4.0.67 from download of package ver 3.8.2.330
Server:Win2003
DB: Job configured in SQL server agent to execute a VB6.exe
vb6.exe: creates a script file depending on certain parameters. It then does ShellExecute using WinSCP3.com /privatekey=keyfile /script=newscriptfile

Problem: The script file appears to be executed but no files are transferred from the destination server.

Debug info:
No errors are reported in the SQL server job history.
I have a log file for the vb6.exe and this captures the command line call for WinSCP3.com and the shell response. The response is 42 (so not an error) and to check the command line call I opened a command prompt on the server and copied across the text from the log file *exactly*. This completes successfully and the appropriate files are copied across from the other server.

Question:
Is it a SQL server job configuration issue or is it a WinSCP3 issue. I think the former as when executed at the cmd prompt all is well. Any help would be gratefully received.
Thanks
David

Try to enable WinSCP logging to see what's happening there.

martin wrote:

Try to enable WinSCP logging to see what's happening there.

I have done this and I was surprised to see the following:

. 2006-10-02 11:17:44.385 Asking user:
. 2006-10-02 11:17:44.385 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.
. 2006-10-02 11:17:44.385
. 2006-10-02 11:17:44.385 The server's rsa2 key fingerprint is:
. 2006-10-02 11:17:44.385 ssh-rsa 1024 e7:6b:ee:6f:9a:43:0e:e1:97:7c:e9:16:fd:67:6c:2d
. 2006-10-02 11:17:44.385
. 2006-10-02 11:17:44.385 If you trust this host, press Yes. To connect without adding host key to the cache, press No. To abandon the connection press Cancel.
. 2006-10-02 11:17:44.385
. 2006-10-02 11:17:44.385 Continue connecting and add host key to the cache? ()
. 2006-10-02 11:17:44.385 Attempt to close connection due to fatal exception:
* 2006-10-02 11:17:44.385 Host key wasn't verified!
. 2006-10-02 11:17:44.385 Closing connection.

I remember seeing this message when I first logged on using the WinSCP UI. Do I need to add the key file to another cache? or does something else have to be done?
Thanks
David

OK I've solved it now. I think this is what happened:

1)Logged onto the server with administrator privilege
2)Ran the WinSCP and said Yes to the message "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."
3)Then the VB6.exe was executed under a different user and the private key file was not found, and hence the message again.
4)So I changed the SQLServerAgent service to run as the admin account I used in 1)...and it has now worked.

Just a final question - where is the private key stored to be reused for later sessions? is it a WinSCP cache or a machine cache?

David

dmalesd wrote:

Just a final question - where is the private key stored to be reused for later sessions? is it a WinSCP cache or a machine cache?

I guess you mean the accepted host key of the server, do you? It is kept in Windows registry, under HKEY_CURRENT_USER.

I have a SQL server 2008 sql agent job running on windows 2008 server calling the WinSCP. It runs fine mostly but some time is fails with the following error.
'The server's host key was not found in the cache'
It seems like this happen after the user logs off the machine. if the user is logged in the script seems to run fine.

unsuccessful run
. 2011-03-12 13:04:04.351 Using SSH protocol version 2
. 2011-03-12 13:04:04.351 We claim version: SSH-2.0-WinSCP_release_4.2.8
. 2011-03-12 13:04:04.399 Using Diffie-Hellman with standard group "group14"
. 2011-03-12 13:04:04.399 Doing Diffie-Hellman key exchange with hash SHA-1
. 2011-03-12 13:04:04.946 Asking user:
. 2011-03-12 13:04:04.946 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.
. 2011-03-12 13:04:04.946
. 2011-03-12 13:04:04.946 The server's dss key fingerprint is:
. 2011-03-12 13:04:04.946 ssh-dss 1024 e7:86:54:20:7b:1b:4e:ba:9c:33:4f:9f:f8:85:10:20
. 2011-03-12 13:04:04.946
. 2011-03-12 13:04:04.946 If you trust this host, press Yes. To connect without adding host key to the cache, press No. To abandon the connection press Cancel.
. 2011-03-12 13:04:04.946
. 2011-03-12 13:04:04.946 Continue connecting and add host key to the cache? ()

Successful run
. 2011-03-12 12:04:04.025 Using SSH protocol version 2
. 2011-03-12 12:04:04.025 We claim version: SSH-2.0-WinSCP_release_4.2.8
. 2011-03-12 12:04:04.077 Using Diffie-Hellman with standard group "group14"
. 2011-03-12 12:04:04.077 Doing Diffie-Hellman key exchange with hash SHA-1
. 2011-03-12 12:04:04.616 Host key fingerprint is:
. 2011-03-12 12:04:04.616 ssh-dss 1024 e7:86:54:20:7b:1b:4e:ba:9c:33:4f:9f:f8:85:10:20
. 2011-03-12 12:04:04.616 Initialised AES-256 CBC client->server encryption
. 2011-03-12 12:04:04.616 Initialised HMAC-SHA1 client->server MAC algorithm
. 2011-03-12 12:04:04.660 Initialised AES-256 CBC server->client encryption
. 2011-03-12 12:04:04.660 Initialised HMAC-SHA1 server->client MAC algorith

Mathew wrote:

I have a SQL server 2008 sql agent job running on windows 2008 server calling the WinSCP. It runs fine mostly but some time is fails with the following error.
'The server's host key was not found in the cache'
It seems like this happen after the user logs off the machine. if the user is logged in the script seems to run fine.

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

. 2017-08-24 16:01:18.739 Prompt (keyboard interactive, "SSH server authentication", "Using keyboard-interactive authentication.", "Password: ")
. 2017-08-24 16:01:18.740 Using stored password.
. 2017-08-24 16:01:20.600 Keyboard-interactive authentication failed
! 2017-08-24 16:01:20.600 Access denied
. 2017-08-24 16:01:20.600 Server offered these authentication methods: publickey,gssapi-with-mic,password,keyboard-interactive
. 2017-08-24 16:01:20.600 Attempting keyboard-interactive authentication
. 2017-08-24 16:01:20.901 Prompt (keyboard interactive, "SSH server authentication", "Using keyboard-interactive authentication.", "Password: ")
. 2017-08-24 16:01:20.901 Disconnected: Unable to authenticate

Anonymous wrote:

. 2017-08-24 16:01:18.739 Prompt (keyboard interactive, "SSH server authentication", "Using keyboard-interactive authentication.", "Password: ")
. 2017-08-24 16:01:18.740 Using stored password.
. 2017-08-24 16:01:20.600 Keyboard-interactive authentication failed
! 2017-08-24 16:01:20.600 Access denied
. 2017-08-24 16:01:20.600 Server offered these authentication methods: publickey,gssapi-with-mic,password,keyboard-interactive
. 2017-08-24 16:01:20.600 Attempting keyboard-interactive authentication
. 2017-08-24 16:01:20.901 Prompt (keyboard interactive, "SSH server authentication", "Using keyboard-interactive authentication.", "Password: ")
. 2017-08-24 16:01:20.901 Disconnected: Unable to authenticate

Did you read documentation?
https://winscp.net/eng/docs/message_access_denied