SSIS Package Host key not matching.

Advertisement

alaw_aoh
Joined:
Posts:
2
Location:
Wilmington, ma

SSIS Package Host key not matching.

This is driving me mad… spent over 16 hours trying to get this to work in SSIS package.

This package was working find until it came time to change the password. So we changed the password on the hosting server and within the ssis .net package code in. And since then we get host key not matching.

Here is the thing. If I take all the log in information and use the WinSCP GUI it works. If I run this inside a batch file with a script file through a command prompt it works. If I literally run a command prompt and type all the commands out from the script file. It works.

And finally if I run it via visual studio on my local machine pointing to all the files on the server it will work as well.

It refuses to work from SSIS package on a sql server 2008 R2. I have tried various ways to get this to work and it just doesn’t within the ssis package.

The final way I am mentioning here is what I have come to to try and get this to work.

Execute Task
Executable = C:\Program Files (x86)\WinSCP\WinSCP.com
Arguments = /script="\\server01\Files\Scripts\client\clientDOT-Script.txt" /log="\\hrwobsql05\Files\Scripts\client\winscp.txt"

Script text
open sftp://support%40test.com:***@halftp.client.com/ -hostkey="ssh-rsa 2048 39it6ir4BopO1w05H42ssREGaX279A0b2SEmTlQvb/U=" -privatekey="\\ server01\files\clientKey\clientSFTP-Pair-Putty.ppk" -passphrase="****"
cd /
lcd \\ server01\Files\client\
put \\ server01\Files\client\test.results.RESULTS_20190716092040.xml
exit

Log file
Receives this error:
. 2019-07-17 10:31:35.602 Working directory: C:\Windows\system32
. 2019-07-17 10:31:35.602 Process ID: 6900
. 2019-07-17 10:31:35.602 Command-line: "C:\Program Files (x86)\WinSCP\WinSCP.exe" /console=577 /consoleinstance=_3744_227 "/script=\\server01\Files\Scripts\Client\clientDOT-Script.txt" "/log=\\server01\Files\Scripts\Client\winscp.txt"
. 2019-07-17 10:31:35.602 Time zone: Current: GMT-4, Standard: GMT-5 (Eastern Standard Time), DST: GMT-4 (Eastern Daylight Time), DST Start: 3/10/2019, DST End: 11/3/2019
. 2019-07-17 10:31:35.602 Login time: Wednesday, July 17, 2019 10:31:35 AM
. 2019-07-17 10:31:35.602 --------------------------------------------------------------------------
. 2019-07-17 10:31:35.602 Script: Retrospectively logging previous script records:
> 2019-07-17 10:31:35.602 Script: open sftp://support%40test.com:***@halftp.client.com/ -hostkey="ssh-rsa 2048 39it6ir4BopO1w05H42ssREGaX279A0b2SEmTlQvb/U=" -privatekey="\\server01\files\ClientKey\ClientSFTP-Pair-Putty.ppk" -passphrase=***
. 2019-07-17 10:31:35.602 --------------------------------------------------------------------------
. 2019-07-17 10:31:35.602 Session name: support@test.com@halftp.client.com (Ad-Hoc site)
. 2019-07-17 10:31:35.602 Host name: halftp.client.com (Port: 22)
. 2019-07-17 10:31:35.602 User name: support@test.com (Password: Yes, Key file: Yes)
. 2019-07-17 10:31:35.602 Tunnel: No
. 2019-07-17 10:31:35.602 Transfer Protocol: SFTP
. 2019-07-17 10:31:35.602 Ping type: -, Ping interval: 30 sec; Timeout: 15 sec
. 2019-07-17 10:31:35.602 Disable Nagle: No
. 2019-07-17 10:31:35.602 Proxy: none
. 2019-07-17 10:31:35.602 Send buffer: 262144
. 2019-07-17 10:31:35.602 SSH protocol version: 2; Compression: No
. 2019-07-17 10:31:35.602 Bypass authentication: No
. 2019-07-17 10:31:35.602 Try agent: Yes; Agent forwarding: No; TIS/CryptoCard: No; KI: Yes; GSSAPI: No
. 2019-07-17 10:31:35.602 Ciphers: aes,blowfish,3des,WARN,arcfour,des; Ssh2DES: No
. 2019-07-17 10:31:35.602 KEX: dh-gex-sha1,dh-group14-sha1,dh-group1-sha1,rsa,WARN
. 2019-07-17 10:31:35.602 SSH Bugs: A,A,A,A,A,A,A,A,A,A,A,A
. 2019-07-17 10:31:35.602 Simple channel: Yes
. 2019-07-17 10:31:35.602 Return code variable: Autodetect; Lookup user groups: A
. 2019-07-17 10:31:35.602 Shell: default
. 2019-07-17 10:31:35.602 EOL: 0, UTF: 2
. 2019-07-17 10:31:35.602 Clear aliases: Yes, Unset nat.vars: Yes, Resolve symlinks: Yes
. 2019-07-17 10:31:35.602 LS: ls -la, Ign LS warn: Yes, Scp1 Comp: No
. 2019-07-17 10:31:35.602 SFTP Bugs: A,A
. 2019-07-17 10:31:35.602 SFTP Server: default
. 2019-07-17 10:31:35.602 Local directory: default, Remote directory: home, Update: Yes, Cache: Yes
. 2019-07-17 10:31:35.602 Cache directory changes: Yes, Permanent: Yes
. 2019-07-17 10:31:35.602 DST mode: 1
. 2019-07-17 10:31:35.602 --------------------------------------------------------------------------
. 2019-07-17 10:31:35.602 Looking up host "halftp.client.com"
. 2019-07-17 10:31:35.602 Connecting to 134.132.54.140 port 22
. 2019-07-17 10:31:36.024 Server version: SSH-2.0-7.9.0.0_openssh Test
. 2019-07-17 10:31:36.024 Using SSH protocol version 2
. 2019-07-17 10:31:36.024 We claim version: SSH-2.0-WinSCP_release_5.7.7
. 2019-07-17 10:31:36.087 Doing Diffie-Hellman group exchange
. 2019-07-17 10:31:36.337 Doing Diffie-Hellman key exchange with hash SHA-1
. 2019-07-17 10:31:38.883 Verifying host key rsa2 0x10001,0xdad7424fce799a2b c9d2c1e7f49c05cd 216dae79db1a3afc 960e8f92e78711bd 68a22886cf0ca6e2 75e184b672bab79b 20f52cca486cf242 4a9c3a8c4defd45f 16984ab9a7515ca5 dff10a91646a21f0 613ac583273a62d4 656cec5d9a6549ed 116d07a379a564ef f19f7f61cace3db6 d1c2e16099fda772 fbd053ceae76d8c4 0aaaeb960f53ea0a 45578a8cf7ea7b53 76c1ecfac70b8b56 36f294cf57c3be1a c16671ded8ba5169 dc8ffaca89a17ddf 5409ec1eb806a551 4563154455fe63f9 7612743b67b1deb4 3f056c70341625c5 1538b5251d906047 d02bd4a924ae2d3d 313213921bb71e70 7c4716c8113abbbe f30f6b21cda977e1 4b6dff2446d7896f with fingerprint ssh-rsa 2048 90:6d:50:ea:30:71:78:fd:43:87:e3:52:3c:1e:aa:49
. 2019-07-17 10:31:38.883 Host key does not match configured key ssh-rsa 2048 39it6ir4BopO1w05H42ssREGaX279A0b2SEmTlQvb/U=
. 2019-07-17 10:31:38.883 Attempt to close connection due to fatal exception:
* 2019-07-17 10:31:38.883 Host key fingerprint is ssh-rsa 2048 90:6d:50:ea:30:71:78:fd:43:87:e3:52:3c:1e:aa:49.
* 2019-07-17 10:31:38.883 (Exception) **Host key does not match configured key "ssh-rsa 2048 39it6ir4BopO1w05H42ssREGaX279A0b2SEmTlQvb/U="!**
. 2019-07-17 10:31:38.883 Closing connection.
. 2019-07-17 10:31:38.883 Sending special code: 12

Any help would be greatly appreciated... Right now I am manually copying the files over wiht the winscp gui.

Yours in deepest gratitude,

Angela

Reply with quote

Advertisement

martin
Site Admin
martin avatar
Joined:
Posts:
31,808
Location:
Prague, Czechia

Re: SSIS Package Host key not matching.

Your script specifies SHA-256 fingerprint of the host key. SHA-256 fingerprints are supported since WinSCP 5.12 only:
https://winscp.net/tracker/1589
While you use WinSCP 5.7.7 (over 3 years old).

Reply with quote

alaw_aoh
Joined:
Posts:
2
Location:
Wilmington, ma

Confused

Hi Martin,

thanks so much for taking the time to read what I wrote and reply. I looked at the link you gave me.

I am not understanding your idea of what is wrong.

It doesn't make sense to me why it works on the literal windows server that is running SQL server; I can run the script either via command prompt or a batch file but does *not* work in SSIS package on the same server.

I would think if the problem was version based of winscp it would not matter where I ran it on the server. I would get the same problem. but that is not the case.

Can you help me better understand? Cause I can't go to my IT folks and tell them to upgrade this version without being able to explain why it all of a sudden became a problem when nothing else changed except the password.

Thanks,

Angela

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
31,808
Location:
Prague, Czechia

Re: Confused

Two options:

  • Either you have two copies of WinSCP on the machine and SSIS finds another copy.
  • Or (more likely), the host key of the server is cached in registry of the account that you use to run manually, so the fingerprint in the script is not even used. While the SSIS account does not have the host key cached, so it tries to verify the host key using the (unsupported) fingerprint in the script.
    This is covered at: https://winscp.net/eng/docs/guide_schedule
If you want us to give you a definitive answer, we need a log file from your manual test too.

In general it's safer for your script to have its own copy of WinSCP executables, then rely on the installed version.

Reply with quote

Advertisement

You can post new topics in this forum