SQL Scheduled FTP Task not running, WinSCP


     I have a SQL job that uploads some TXT files to a remote sever nightly using WinSCP. The job runs fine when I manually run it but when it is scheduled it does not complete properly, nor does it provide an accurate error message. I'm not sure if it's a remote host problem or a local user rights issue.

Windows Server 2008 R2
MS SQL Server 2008 R2

The BAT file that runs the job:

cd \MILLHTML\online~1\export
echo ********** Start Export ********** >> export.log
date /T >> export.log
time /T >> export.log
copy  *.txt .\savefile\
echo ********** Initiating SFTP Transfer ********** >> export.log
psftp -v -pw <<password>> <<user>>@ssh.<<host>>.com -b sshtransfer.ftp >>export.log
date /T >> export.log
time /T >> export.log
echo ********** SFTP Transfer Complete ********** >> export.log
erase *.txt
echo ********** End Export ********** >> export.log
date /T >> export.log
time /T >> export.log

The sshtransfer.ftp command:
mput *.txt

The Log:
********** Start Export **********
Tue 10/07/2014
05:30 PM
********** Initiating SFTP Transfer **********
. 2014-10-07 17:30:09.284 --------------------------------------------------------------------------
. 2014-10-07 17:30:09.284 WinSCP Version 5.5.5 (Build 4605) (OS 6.1.7601 Service Pack 1 - Windows Server 2008 R2 Standard)
. 2014-10-07 17:30:09.284 Configuration: D:\MILLHTML\ONLINE~1\export\WinSCP.ini
. 2014-10-07 17:30:09.284 Local account: FND\SQLUser
. 2014-10-07 17:30:09.284 Working directory: D:\MILLHTML\ONLINE~1\export
. 2014-10-07 17:30:09.284 Process ID: 2696
. 2014-10-07 17:30:09.284 Command-line: "D:\MILLHTML\ONLINE~1\export\WinSCP.exe" /console=555 /consoleinstance=_4884_670 "/console" "/script=WinSCP.ftp" "/log=WinSCPexportPCI.log"
. 2014-10-07 17:30:09.284 Time zone: Current: GMT-4, Standard: GMT-5 (Eastern Standard Time), DST: GMT-4 (Eastern Daylight Time), DST Start: 3/9/2014, DST End: 11/2/2014
. 2014-10-07 17:30:09.284 Login time: Tuesday, October 07, 2014 5:30:09 PM
. 2014-10-07 17:30:09.284 --------------------------------------------------------------------------
. 2014-10-07 17:30:09.284 Script: Retrospectively logging previous script records:
> 2014-10-07 17:30:09.284 Script: option batch abort
< 2014-10-07 17:30:09.284 Script: batch           abort    
> 2014-10-07 17:30:09.284 Script: option confirm off
< 2014-10-07 17:30:09.284 Script: confirm         off      
> 2014-10-07 17:30:09.284 Script: option transfer binary
< 2014-10-07 17:30:09.284 Script: transfer        binary    
> 2014-10-07 17:30:09.284 Script: open <<user>>:***@ssh.<<host>>.com:22
. 2014-10-07 17:30:09.284 --------------------------------------------------------------------------
. 2014-10-07 17:30:09.284 Session name: <<user>>@ssh.<<host>>.com (Ad-Hoc site)
. 2014-10-07 17:30:09.284 Host name: ssh.<<host>>.com (Port: 22)
. 2014-10-07 17:30:09.284 User name: <<user>> (Password: Yes, Key file: No)
. 2014-10-07 17:30:09.284 Tunnel: No
. 2014-10-07 17:30:09.284 Transfer Protocol: SFTP (SCP)
. 2014-10-07 17:30:09.284 Ping type: -, Ping interval: 30 sec; Timeout: 15 sec
. 2014-10-07 17:30:09.284 Proxy: none
. 2014-10-07 17:30:09.284 Send buffer: 262144
. 2014-10-07 17:30:09.284 SSH protocol version: 2; Compression: No
. 2014-10-07 17:30:09.284 Bypass authentication: No
. 2014-10-07 17:30:09.284 Try agent: Yes; Agent forwarding: No; TIS/CryptoCard: No; KI: Yes; GSSAPI: No
. 2014-10-07 17:30:09.284 Ciphers: aes,blowfish,3des,WARN,arcfour,des; Ssh2DES: No
. 2014-10-07 17:30:09.284 SSH Bugs: A,A,A,A,A,A,A,A,A,A
. 2014-10-07 17:30:09.284 Simple channel: Yes
. 2014-10-07 17:30:09.284 Return code variable: Autodetect; Lookup user groups: A
. 2014-10-07 17:30:09.284 Shell: default
. 2014-10-07 17:30:09.284 EOL: 0, UTF: 2
. 2014-10-07 17:30:09.284 Clear aliases: Yes, Unset nat.vars: Yes, Resolve symlinks: Yes
. 2014-10-07 17:30:09.284 LS: ls -la, Ign LS warn: Yes, Scp1 Comp: No
. 2014-10-07 17:30:09.284 SFTP Bugs: A,A
. 2014-10-07 17:30:09.284 SFTP Server: default
. 2014-10-07 17:30:09.284 Local directory: default, Remote directory: home, Update: Yes, Cache: Yes
. 2014-10-07 17:30:09.284 Cache directory changes: Yes, Permanent: Yes
. 2014-10-07 17:30:09.284 DST mode: 1; Timezone offset: 0h 0m
. 2014-10-07 17:30:09.284 --------------------------------------------------------------------------
. 2014-10-07 17:30:09.284 Looking up host "ssh.<<host>>.com"
Tue 10/07/2014
05:30 PM
********** SFTP Transfer Complete **********
********** End Export **********
Tue 10/07/2014
05:30 PM

After the Looking up host "ssh.<<host>>.com" is should then connect. I don't know why is doesn't connect and continue or at least provide me with an error message, something..
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Well, you need to consider that a scheduled job is per default run with very few priveleges. Thus create an normal account for running WinScp, test it, and use this account to run the job.
FNDAdminAuthor Commented:
The SQL job is setup with Owner of our domain admin account. In the SQL Job I am unable to choose a 'Run as" option for this FTP Step. The drop down box has no entries nor any way to choose a user.

The WinSCP log shows that, "Local account: FND\SQLUser", which is the domain user account that SQL Server Agent uses to log on as and start-up with. I have granted SQLUser "Allow log on locally"rights on the SQL Server. Is there any other rights I should be granting this account to allow it to function properly?
FNDAdminAuthor Commented:
It seems that my issue was resolved by editing the compatibility of the WinSCP.exe and enabling the 'Run this program as an administrator' setting.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for FNDAdmin's comment #a40373059

for the following reason:

No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: FNDAdmin (https:#a40373059)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

Experts-Exchange Cleanup Volunteer
ste5anSenior DeveloperCommented:
It's exactly what I wrote: The job was run under an account with not enough privileges.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.