Topic "Executing WinSCP3.com using SQL server 2005"

Author Message
dmalesd
[View user's profile]

Joined: 2006-09-28
Posts: 9
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
Advertisements
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 25015
Location: Prague, Czechia
Try to enable WinSCP logging to see what's happening there.
_________________
Martin Prikryl
dmalesd
[View user's profile]

Joined: 2006-09-28
Posts: 9
prikryl 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
dmalesd
[View user's profile]

Joined: 2006-09-28
Posts: 9
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
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 25015
Location: Prague, Czechia
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.
_________________
Martin Prikryl
Mathew

Guest


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
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 25015
Location: Prague, Czechia
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
_________________
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