MS-SQL xp_cmdshell coreFTP Pro hangs on download

[dly]

I am using corecmd.exe Version 1.3c build 1447.6

I am trying to download files from within SQL server 2000 using xp_cmdshell. It seems that the upload works fine, files are transferred, logs are created etc.

When I try to down files, the program hangs. I hear a ding sound on the server witch may indicate it’s trying to pop some kind of error message but can’t because it’s running in a background process.

If I start coreftp.exe interactively on the server’s console and then execute the xp_cmdshell to run corecmd.exe –s, I get a C++ runtime pop-up dialog that appears to be coming from the background version of coreftp.exe. The error message is:

Microsoft Visual C++ Runtime Library
Runtime Error!
Program: C:\Program Files\CoreFTP\coreftp.exe
This application has requested the Runtime to terminate it in an unusual way.
Please contact the application’s support team for more information.
[OK]

The strange thing is that the –u option works find with xp_cmdshell but the –d hangs. I have checked security settings on the destination directory. The –u option writes log files in the same directory with no problems. Also the –d option zeros out the output log file which indicates that it has security rights.

I stop the sql server service and ran it from the command line with the –c option (this is how you debug extended stored procedures). The sql server is now running in your user security context vs. the system account context. Now I can upload and download files using xp_cmdshell. To me this indicates that coreFTP is having a security issue during downloads that it’s not having during uploads. Maybe you are creating a temporary file somewhere other then the destination directory.

What file access is different from uploads and downloads? Is there a temporary directory that I need to give the system account access rights to?

Here are the two commands I am executing:

-- download
exec xp_cmdshell '"C:\Program Files\CoreFTP\corecmd.exe" -O -s -site bank -log c:\temp\bank\inbound\log.log -output c:\temp\bank\inbound\output.log -p C:\TEMP\bank\inbound\ -d /outbound/*.txt'

-- upload
exec xp_cmdshell '"C:\Program Files\CoreFTP\corecmd.exe" -s -site bank -log c:\temp\bank\outbound\log.log -output c:\temp\bank\outbound\output.log -p .\ -u c:\temp\bank\outbound\*.txt'

[dly]

Here's a work around:

1. Create a domain account and add it to the local admin group on the sql server box.
2. Change the login security on the sql service to use the new domain account.
3. Restart sql service.
4. On the sql server console, login as the new domain account.
5. Run CoreFTP in interactive mode and make sure you can connect to the remote FTP server.

That's it. Now you should be able to run CoreFTP using xp_cmdshell without it hanging.

...dly