I am trying to make a SFTP connection using SSIS Execute process task with below command line arguments.
/log=G:\USER_DATA\USER_USER_SYNC\SFTP_LOG\user_sync_winscp.log /command "open sftp://username:[email protected]/" "get /DATA/UserRoleUpdate.csv G:\USER_DATA\USER_USER_SYNC\Download\" "exit"
The given Execute process task uses WinSCP exe to download files to the local drive. Package is running successfully in debug mode and also when I am running manually through SSISDB folder at my SQL server.
We are facing an issue while trying to execute SSIS package using job agent. If I disable that specific task which makes SFTP connection then package run successfully through job agent as well. It seems that it's having problem with that specific task only. I'm struggling to understand why this is happening and what needs to be done to fix this issue.
When I checked user_sync_winscp.log file I found this.
. 2019-08-20 08:10:23.931 Host key fingerprint is:
. 2019-08-20 08:10:23.931 ssh-rsa 2048 9b:63:5e:c4:26:bb:35:0d:49:e6:74:5e:5a:48:c0:8a bLgYggTcP4U6A/IFkFoyfY8BhlCB/+i5fFhzq1z1MjQ=
. 2019-08-20 08:10:23.931 Asking user:
. 2019-08-20 08:10:23.931 **Continue connecting to an unknown server and add its host key to a cache?** . 2019-08-20 08:10:23.931 . 2019-08-20 08:10:23.931 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.
. 2019-08-20 08:10:23.931
. 2019-08-20 08:10:23.931 The server's RSA key details are:
. 2019-08-20 08:10:23.931
. 2019-08-20 08:10:23.931 Algorithm: ssh-rsa 2048
. 2019-08-20 08:10:23.931 SHA-256: bLgYggTcP4U6A/IFkFoyfY8BhlCB/+i5fFhzq1z1MjQ=
. 2019-08-20 08:10:23.931 MD5: 9b:63:5e:c4:26:bb:35:0d:49:e6:74:5e:5a:48:c0:8a
. 2019-08-20 08:10:23.931
. 2019-08-20 08:10:23.931 If you trust this host, press Yes. To connect without adding host key to the cache, press No. To abandon the connection press Cancel.
. 2019-08-20 08:10:23.931 In scripting, you should use a -hostkey switch to configure the expected host key. () . 2019-08-20 08:10:33.933 Attempt to close connection due to fatal exception:
* 2019-08-20 08:10:33.933 Host key fingerprint is ssh-rsa 2048 bLgYggTcP4U6A/IFkFoyfY8BhlCB/+i5fFhzq1z1MjQ=.
* 2019-08-20 08:10:33.933 (Exception) **Host key wasn't verified!** . 2019-08-20 08:10:33.933 Closing connection.
. 2019-08-20 08:10:33.933 Sending special code: 12
I then copied host key from the log file and changed my command line arguments by adding a switch -hostkey="..." as shown below.
/log=G:\USER_DATA\USER_USER_SYNC\SFTP_LOG\user_sync_winscp.log /command "open sftp://username:[email protected]/" -hostkey="ssh-rsa 2048 9b:63:5e:c4:26:bb:35:0d:49:e6:74:5e:5a:48:c0:8a" "get /DATA/UserRoleUpdate.csv G:\USER_DATA\USER_USER_SYNC\Download\" "exit"
Now Task is failing at local itself.
Kindly let me know in case I need to add further more details about this. Thanks
The correct syntax is:
... /command "open sftp://username:[email protected]/ -hostkey=""ssh-rsa 2048 9b:63:5e:c4:26:bb:35:0d:49:e6:74:5e:5a:48:c0:8a""" "get ..." ...
See WinSCP command-line syntax and open command syntax.
WinSCP GUI can generate a correct script/command-line for you, including the -hostkey switch.