SQL Scheduled FTP Task not running, WinSCP

Posted on 2014-10-08
Medium Priority
Last Modified: 2016-06-12

     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..
Question by:FNDAdmin
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 35

Assisted Solution

ste5an earned 2000 total points
ID: 40370445
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.

Author Comment

ID: 40370521
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?

Accepted Solution

FNDAdmin earned 0 total points
ID: 40373059
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.
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41648812
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
LVL 35

Expert Comment

ID: 41648813
It's exactly what I wrote: The job was run under an account with not enough privileges.

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question