Unable to authenticate from SSIS using script file with hostkey.

Advertisement

Learner
Joined:
Posts:
3

Unable to authenticate from SSIS using script file with hostkey.

Hi,
I am having authentication issue with the script file run by SSIS service account. The open command in the script file has -hostkey provided. The outside-profile was created from WinSCP GUI with my login credential. I have no problem connecting to the remote site from WinSCP GUI and running the script with my credential. When SSIS uses my script, it is get authentication failed. The SSIS is using a Windows service account not my credential.

From the log with my credential, I see "User name: outside-profile (Password: Yes, Key file: No)" – password is Yes. I also see "Using stored password.". From the SSIS log, I see "User name: outside-profile (Password: No, Key file: No)" and do not see "Using stored password". A hostkey is provided in the script file like:
open outside-profileg@sftp.outside.com -hostkey="ssh-rsa 2048 fe:b6:a7:24:e1:6e:a8:d2:2d:a2:3a:77:b2:c5:b0:f9"
Did I miss anything? Any suggestion?

Below is the log file with authentication failed.
. 2017-05-14 10:50:20.459 --------------------------------------------------------------------------
. 2017-05-14 10:50:20.459 WinSCP Version 5.7.6 (Build 5874) (OS 6.1.7601 Service Pack 1 - Windows Server 2008 R2 Enterprise)
. 2017-05-14 10:50:20.459 Configuration: HKCU\Software\Martin Prikryl\WinSCP 2\
. 2017-05-14 10:50:20.459 Log level: Normal
. 2017-05-14 10:50:20.459 Local account: mydomain\serviceaccount 
. 2017-05-14 10:50:20.459 Working directory: D:\outside
. 2017-05-14 10:50:20.459 Process ID: 5928
. 2017-05-14 10:50:20.459 Command-line: "C:\Program Files (x86)\WinSCP\WinSCP.exe" /console=576 /consoleinstance=_5932_207 "/script=D:\outside\Getoutside.txt" "/log=D:\outside\Logs\WinSCP_log.txt" 
. 2017-05-14 10:50:20.459 Time zone: Current: GMT-7, Standard: GMT-8 (Pacific Standard Time), DST: GMT-7 (Pacific Daylight Time), DST Start: 3/12/2017, DST End: 11/5/2017
. 2017-05-14 10:50:20.459 Login time: Sunday, May 14, 2017 10:50:20 AM
. 2017-05-14 10:50:20.459 --------------------------------------------------------------------------
. 2017-05-14 10:50:20.459 Script: Retrospectively logging previous script records:
> 2017-05-14 10:50:20.459 Script: open outside-profile@sftp.outside.com -hostkey="ssh-rsa 2048 fe:b6:a7:24:e1:6e:a8:d2:2d:a2:3a:77:b2:c5:b0:f9"
. 2017-05-14 10:50:20.459 --------------------------------------------------------------------------
. 2017-05-14 10:50:20.460 Session name: outside-profile@sftp.outside.com (Ad-Hoc site)
. 2017-05-14 10:50:20.460 Host name: sftp.outside.com (Port: 22)
. 2017-05-14 10:50:20.460 User name: outside-profile (Password: No, Key file: No)
. 2017-05-14 10:50:20.460 Tunnel: No
. 2017-05-14 10:50:20.460 Transfer Protocol: SFTP (SCP)
. 2017-05-14 10:50:20.460 Ping type: -, Ping interval: 30 sec; Timeout: 15 sec
. 2017-05-14 10:50:20.460 Disable Nagle: No
. 2017-05-14 10:50:20.460 Proxy: none
. 2017-05-14 10:50:20.460 Send buffer: 262144
. 2017-05-14 10:50:20.460 SSH protocol version: 2; Compression: No
. 2017-05-14 10:50:20.460 Bypass authentication: No
. 2017-05-14 10:50:20.460 Try agent: Yes; Agent forwarding: No; TIS/CryptoCard: No; KI: Yes; GSSAPI: No
. 2017-05-14 10:50:20.460 Ciphers: aes,blowfish,3des,WARN,arcfour,des; Ssh2DES: No
. 2017-05-14 10:50:20.460 KEX: dh-gex-sha1,dh-group14-sha1,dh-group1-sha1,rsa,WARN
. 2017-05-14 10:50:20.460 SSH Bugs: A,A,A,A,A,A,A,A,A,A,A,A
. 2017-05-14 10:50:20.460 Simple channel: Yes
. 2017-05-14 10:50:20.460 Return code variable: Autodetect; Lookup user groups: A
. 2017-05-14 10:50:20.460 Shell: default
. 2017-05-14 10:50:20.460 EOL: 0, UTF: 2
. 2017-05-14 10:50:20.460 Clear aliases: Yes, Unset nat.vars: Yes, Resolve symlinks: Yes
. 2017-05-14 10:50:20.460 LS: ls -la, Ign LS warn: Yes, Scp1 Comp: No
. 2017-05-14 10:50:20.460 SFTP Bugs: A,A
. 2017-05-14 10:50:20.460 SFTP Server: default
. 2017-05-14 10:50:20.460 Local directory: default, Remote directory: home, Update: Yes, Cache: Yes
. 2017-05-14 10:50:20.460 Cache directory changes: Yes, Permanent: Yes
. 2017-05-14 10:50:20.460 DST mode: 1
. 2017-05-14 10:50:20.460 --------------------------------------------------------------------------
. 2017-05-14 10:50:20.460 Looking up host "sftp.outside.com"
. 2017-05-14 10:50:20.471 Connecting to 124.53.145.123 port 22
. 2017-05-14 10:50:20.518 Server version: SSH-2.0-OpenSSH_6.6.1p1 Ubuntu-2ubuntu2.8
. 2017-05-14 10:50:20.518 Using SSH protocol version 2
. 2017-05-14 10:50:20.518 We claim version: SSH-2.0-WinSCP_release_5.7.6
. 2017-05-14 10:50:20.540 Doing Diffie-Hellman group exchange
. 2017-05-14 10:50:20.620 Doing Diffie-Hellman key exchange with hash SHA-256
. 2017-05-14 10:57:36.145 Verifying host key rsa2 0x10001,0xcc45addbb3d0c11 0b3c02ad662ee5bc 031425773ed8b3e6 7fa00006d8be6c29 91256781771e9fbd 4c1f5ea2dd9f2675 b3d7c88f7452d7e4 cd27cc5f0ca565ff 64f97436778597e1 a49d0bd567eedd12 64b0fea02e06797c 77a7f6232a97696b 09451aebbab05109 4544cf5664679fa0 fe38c424738c8325 22fe4b789b49ea62 1c45c73a4b8f1b52 0f17dfd8ef176ec7 9881b0981e49222e cd62cd50dbcdc996 443e9b5479067f17 a54129dfc417c41e 172780280903a085 8a5631c624a566ed 537d44c57bde0287 288f04d6a636a9ae e11e02fb21c7c2f2 67de9e2b876c0d2c 2c9f7163578ae5f4 b8f9e74dea84b1ec 43aa03a957720316 bd085a5f9e82360b  with fingerprint ssh-rsa 2048 fe:b6:a7:24:e1:6e:a8:d2:2d:a2:3a:77:b2:c5:b0:f9
. 2017-05-14 10:50:21.461 Host key matches cached key
. 2017-05-14 10:50:21.461 Host key fingerprint is:
. 2017-05-14 10:50:21.461 ssh-rsa 2048 fe:b6:a7:24:e1:6e:a8:d2:2d:a2:3a:77:b2:c5:b0:f9
. 2017-05-14 10:50:21.461 Initialised AES-256 SDCTR client->server encryption
. 2017-05-14 10:50:21.461 Initialised HMAC-SHA-256 client->server MAC algorithm
. 2017-05-14 10:50:21.461 Initialised AES-256 SDCTR server->client encryption
. 2017-05-14 10:50:21.461 Initialised HMAC-SHA-256 server->client MAC algorithm
! 2017-05-14 10:50:21.543 Using username "outside-profile".
. 2017-05-14 10:50:21.565 Prompt (password, "SSH password", <no instructions>, "&Password: ")
. 2017-05-14 10:50:31.567 Sent password
. 2017-05-14 10:50:31.588 Password authentication failed
! 2017-05-14 10:50:31.588 Access denied
. 2017-05-14 10:50:31.589 Prompt (password, "SSH password", <no instructions>, "&Password: ")
. 2017-05-14 10:50:41.596 Disconnected: Unable to authenticate
Thanks,
Brandon.

Reply with quote

Advertisement

martin
Site Admin
martin avatar
Joined:
Posts:
41,504
Location:
Prague, Czechia

Re: Unable to authenticate from SSIS using script file with hostkey.

The -hostkey switch has nothing to do with authentication.
Make sure you understand, what different keys in SSH do:
https://winscp.net/eng/docs/ssh_keys

Your script does not include any password or private key.

If it works outside of SSIS, it's most probably because you are authentication with a private key loaded in Pageant (although you do not mention that).
SSIS runs in a different Windows session. It cannot access Pageant in the user session. Pageant is not intended for automation anyway.

Explicitly specify your private key in the script.

If the above is not true or doe not help, see
My script works fine when executed manually, but fails or hangs when run by Windows Scheduler, SSIS or other automation service. What am I doing wrong?

Or at least post a log file on working session for comparison.

Reply with quote

Learner
Joined:
Posts:
3

Hi Martin,
You are correct. How can I grant access to SSIS to use my profile? What do I need to provide in the script to let it refers to my profile?

Below is the log session which I could access to the remote site per your requested:
. 2017-05-14 10:57:35.047 --------------------------------------------------------------------------
. 2017-05-14 10:57:35.047 WinSCP Version 5.7.6 (Build 5874) (OS 6.1.7601 Service Pack 1 - Windows Server 2008 R2 Enterprise)
. 2017-05-14 10:57:35.047 Configuration: HKCU\Software\Martin Prikryl\WinSCP 2\
. 2017-05-14 10:57:35.048 Log level: Normal
. 2017-05-14 10:57:35.048 Local account: mydomain\hbrandon
. 2017-05-14 10:57:35.048 Working directory: D:\Temp
. 2017-05-14 10:57:35.048 Process ID: 6496
. 2017-05-14 10:57:35.048 Command-line: "C:\Program Files (x86)\WinSCP\WinSCP.exe" /console=576 /consoleinstance=_7748_787 "/script=D:\outside\Getoutside.txt" "/log=D:\Temp\WinSCP_log.txt" 
. 2017-05-14 10:57:35.048 Time zone: Current: GMT-7, Standard: GMT-8 (Pacific Standard Time), DST: GMT-7 (Pacific Daylight Time), DST Start: 3/12/2017, DST End: 11/5/2017
. 2017-05-14 10:57:35.048 Login time: Sunday, May 14, 2017 10:57:35 AM
. 2017-05-14 10:57:35.048 --------------------------------------------------------------------------
. 2017-05-14 10:57:35.048 Script: Retrospectively logging previous script records:
> 2017-05-14 10:57:35.048 Script: open outside-profile@sftp.outside.com -hostkey="ssh-rsa 2048 fe:b6:a7:24:e1:6e:a8:d2:2d:a2:3a:77:b2:c5:b0:f9"
. 2017-05-14 10:57:35.048 --------------------------------------------------------------------------
. 2017-05-14 10:57:35.048 Session name: outside-profile@sftp.outside.com (Modified site)
. 2017-05-14 10:57:35.048 Host name: sftp.outside.com (Port: 22)
. 2017-05-14 10:57:35.048 User name: outside-profile (Password: Yes, Key file: No)
. 2017-05-14 10:57:35.048 Tunnel: No
. 2017-05-14 10:57:35.048 Transfer Protocol: SFTP (SCP)
. 2017-05-14 10:57:35.048 Ping type: -, Ping interval: 30 sec; Timeout: 15 sec
. 2017-05-14 10:57:35.048 Disable Nagle: No
. 2017-05-14 10:57:35.048 Proxy: none
. 2017-05-14 10:57:35.048 Send buffer: 262144
. 2017-05-14 10:57:35.048 SSH protocol version: 2; Compression: No
. 2017-05-14 10:57:35.048 Bypass authentication: No
. 2017-05-14 10:57:35.048 Try agent: Yes; Agent forwarding: No; TIS/CryptoCard: No; KI: Yes; GSSAPI: No
. 2017-05-14 10:57:35.048 Ciphers: aes,blowfish,3des,WARN,arcfour,des; Ssh2DES: No
. 2017-05-14 10:57:35.048 KEX: dh-gex-sha1,dh-group14-sha1,dh-group1-sha1,rsa,WARN
. 2017-05-14 10:57:35.048 SSH Bugs: A,A,A,A,A,A,A,A,A,A,A,A
. 2017-05-14 10:57:35.048 Simple channel: Yes
. 2017-05-14 10:57:35.048 Return code variable: Autodetect; Lookup user groups: A
. 2017-05-14 10:57:35.048 Shell: default
. 2017-05-14 10:57:35.048 EOL: 0, UTF: 2
. 2017-05-14 10:57:35.048 Clear aliases: Yes, Unset nat.vars: Yes, Resolve symlinks: Yes
. 2017-05-14 10:57:35.048 LS: ls -la, Ign LS warn: Yes, Scp1 Comp: No
. 2017-05-14 10:57:35.048 SFTP Bugs: A,A
. 2017-05-14 10:57:35.048 SFTP Server: default
. 2017-05-14 10:57:35.048 Local directory: D:\outside\Incoming, Remote directory: /received/mydata, Update: Yes, Cache: Yes
. 2017-05-14 10:57:35.048 Cache directory changes: Yes, Permanent: Yes
. 2017-05-14 10:57:35.048 DST mode: 1
. 2017-05-14 10:57:35.048 --------------------------------------------------------------------------
. 2017-05-14 10:57:35.049 Looking up host "sftp.outside.com"
. 2017-05-14 10:57:35.062 Connecting to 124.53.145.123 port 22
. 2017-05-14 10:57:35.110 Server version: SSH-2.0-OpenSSH_6.6.1p1 Ubuntu-2ubuntu2.8
. 2017-05-14 10:57:35.110 Using SSH protocol version 2
. 2017-05-14 10:57:35.110 We claim version: SSH-2.0-WinSCP_release_5.7.6
. 2017-05-14 10:57:35.132 Doing Diffie-Hellman group exchange
. 2017-05-14 10:57:35.214 Doing Diffie-Hellman key exchange with hash SHA-256
. 2017-05-14 10:57:36.145 Verifying host key rsa2 0x10001,0xcc45addbb3d0c11 0b3c02ad662ee5bc 031425773ed8b3e6 7fa00006d8be6c29 91256781771e9fbd 4c1f5ea2dd9f2675 b3d7c88f7452d7e4 cd27cc5f0ca565ff 64f97436778597e1 a49d0bd567eedd12 64b0fea02e06797c 77a7f6232a97696b 09451aebbab05109 4544cf5664679fa0 fe38c424738c8325 22fe4b789b49ea62 1c45c73a4b8f1b52 0f17dfd8ef176ec7 9881b0981e49222e cd62cd50dbcdc996 443e9b5479067f17 a54129dfc417c41e 172780280903a085 8a5631c624a566ed 537d44c57bde0287 288f04d6a636a9ae e11e02fb21c7c2f2 67de9e2b876c0d2c 2c9f7163578ae5f4 b8f9e74dea84b1ec 43aa03a957720316 bd085a5f9e82360b with fingerprint ssh-rsa 2048 fe:b6:a7:24:e1:6e:a8:d2:2d:a2:3a:77:b2:c5:b0:f9
. 2017-05-14 10:57:36.146 Host key matches cached key
. 2017-05-14 10:57:36.146 Host key fingerprint is:
. 2017-05-14 10:57:36.146 ssh-rsa 2048 fe:b6:a7:24:e1:6e:a8:d2:2d:a2:3a:77:b2:c5:b0:f9
. 2017-05-14 10:57:36.146 Initialised AES-256 SDCTR client->server encryption
. 2017-05-14 10:57:36.146 Initialised HMAC-SHA-256 client->server MAC algorithm
. 2017-05-14 10:57:36.146 Initialised AES-256 SDCTR server->client encryption
. 2017-05-14 10:57:36.146 Initialised HMAC-SHA-256 server->client MAC algorithm
! 2017-05-14 10:57:36.228 Using username "outside-profile".
. 2017-05-14 10:57:36.251 Prompt (password, "SSH password", <no instructions>, "&Password: ")
. 2017-05-14 10:57:36.252 Using stored password.
. 2017-05-14 10:57:36.252 Sent password
. 2017-05-14 10:57:36.280 Access granted
. 2017-05-14 10:57:36.280 Opening session as main channel
. 2017-05-14 10:57:36.509 Opened main channel
. 2017-05-14 10:57:36.552 Started a shell/command
. 2017-05-14 10:57:36.552 --------------------------------------------------------------------------
. 2017-05-14 10:57:36.552 Using SFTP protocol.
. 2017-05-14 10:57:36.553 Doing startup conversation with host.
> 2017-05-14 10:57:36.553 Type: SSH_FXP_INIT, Size: 5, Number: -1
< 2017-05-14 10:57:36.575 Type: SSH_FXP_VERSION, Size: 150, Number: -1
. 2017-05-14 10:57:36.575 SFTP version 3 negotiated.
. 2017-05-14 10:57:36.575 Unknown server extension posix-rename@openssh.com="1"
. 2017-05-14 10:57:36.575 Supports statvfs@openssh.com extension version "2"
. 2017-05-14 10:57:36.576 Unknown server extension fstatvfs@openssh.com="2"
. 2017-05-14 10:57:36.576 Supports hardlink@openssh.com extension version "1"
. 2017-05-14 10:57:36.576 Unknown server extension fsync@openssh.com="1"
. 2017-05-14 10:57:36.576 We believe the server has signed timestamps bug
. 2017-05-14 10:57:36.576 We will use UTF-8 strings until server sends an invalid UTF-8 string as with SFTP version 3 and older UTF-8 string are not mandatory
. 2017-05-14 10:57:36.576 Limiting packet size to OpenSSH sftp-server limit of 262148 bytes
. 2017-05-14 10:57:36.576 Changing directory to "/received/mydomain".
. 2017-05-14 10:57:36.576 Getting real path for '/received/mydomain'
> 2017-05-14 10:57:36.576 Type: SSH_FXP_REALPATH, Size: 25, Number: 16
< 2017-05-14 10:57:36.597 Type: SSH_FXP_NAME, Size: 53, Number: 16
. 2017-05-14 10:57:36.597 Real path is '/received/mydomain'
. 2017-05-14 10:57:36.597 Trying to open directory "/received/mydomain".
> 2017-05-14 10:57:36.597 Type: SSH_FXP_LSTAT, Size: 25, Number: 263
< 2017-05-14 10:57:36.619 Type: SSH_FXP_ATTRS, Size: 37, Number: 263
. 2017-05-14 10:57:36.619 Getting current directory name.
. 2017-05-14 10:57:36.619 Startup conversation with host finished.
< 2017-05-14 10:57:36.619 Script: Active session: [1] outside-profile@sftp.outside.com
> 2017-05-14 10:57:36.621 Script: cd /received/mydomain
. 2017-05-14 10:57:36.621 Cached directory change via "/received/mydomain" to "/received/mydomain".
. 2017-05-14 10:57:36.621 Getting current directory name.
< 2017-05-14 10:57:36.621 Script: /received/mydata
> 2017-05-14 10:57:36.622 Script: get  report*.csv D:\outside\Incoming\
. 2017-05-14 10:57:36.622 Listing directory "/received/mydomain".
> 2017-05-14 10:57:36.622 Type: SSH_FXP_OPENDIR, Size: 25, Number: 523
< 2017-05-14 10:57:36.643 Type: SSH_FXP_HANDLE, Size: 13, Number: 523
> 2017-05-14 10:57:36.643 Type: SSH_FXP_READDIR, Size: 13, Number: 780
< 2017-05-14 10:57:36.665 Type: SSH_FXP_NAME, Size: 207, Number: 780
> 2017-05-14 10:57:36.665 Type: SSH_FXP_READDIR, Size: 13, Number: 1036
< 2017-05-14 10:57:36.686 Type: SSH_FXP_STATUS, Size: 28, Number: 1036
< 2017-05-14 10:57:36.686 Status code: 1
> 2017-05-14 10:57:36.686 Type: SSH_FXP_CLOSE, Size: 13, Number: 1284
. 2017-05-14 10:57:36.686 .;d;0;2017-05-14T14:49:55.000Z;"1115" [1115];"999" [999];rwxr-xr-x;2
. 2017-05-14 10:57:36.687 ..;d;0;2017-03-20T19:20:22.000Z;"1115" [1115];"999" [999];rwxr-xr-x;0
< 2017-05-14 10:57:36.687 Script: No file matching 'tlog*.csv' found.
. 2017-05-14 10:57:36.687 Copying 0 files/directories to local directory "D:\outside\Incoming\"
. 2017-05-14 10:57:36.687   PrTime: Yes; PrRO: No; Rght: rw-r--r--; PrR: No (No); FnCs: N; RIC: 0100; Resume: S (102400); CalcS: No; Mask: 
. 2017-05-14 10:57:36.687   TM: B; ClAr: No; RemEOF: No; RemBOM: No; CPS: 0; NewerOnly: No; InclM: ; ResumeL: 0
. 2017-05-14 10:57:36.687   AscM: *.*html; *.htm; *.txt; *.php; *.php3; *.cgi; *.c; *.cpp; *.h; *.pas; *.bas; *.tex; *.pl; *.js; .htaccess; *.xtml; *.css; *.cfg; *.ini; *.sh; *.xml
> 2017-05-14 10:57:36.687 Script: close 
. 2017-05-14 10:57:36.688 Closing connection.
. 2017-05-14 10:57:36.688 Sending special code: 12
. 2017-05-14 10:57:36.688 Sent EOF message

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
41,504
Location:
Prague, Czechia

You have a stored site named outside-profile@sftp.outside.com in your local account mydomain\hbrandon.

Your script uses that stored site, which includes a password.

SSIS does not have an access to that.

Solutions are covered here:
My script works fine when executed manually, but fails or hangs when run by Windows Scheduler, SSIS or other automation service. What am I doing wrong?
(I wanted to point your there in my previous post, but I've used a wrong link, corrected now).

Basically use:
open sftp://outside-profile:password@sftp.outside.com -hostkey="ssh-rsa 2048 fe:b6:a7:24:e1:6e:a8:d2:2d:a2:3a:77:b2:c5:b0:f9"
Actually, had you used the latest version of WinSCP (what you should do anyway), it would warn you and suggest you to use the above command, instead of relying on stored site.

Reply with quote

Advertisement

You can post new topics in this forum