sftp using xp_cmdshell

Question

Post reply

sftp using xp_cmdshell

rnikandrov

SSChasing Mays

Points: 635
More actions
July 20, 2010 at 9:08 am

#387393

What are my options if i cant use ssis?
I need to write a proc that can transfer files from/to sftp server.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Rick.Cornell.SME SSCrazy Eights Points: 8597 More actions · Answer 1

Check out this website! I believe you can find a Secure FTP component for SSIS!

http://www.sqlis.com/

Rick.Cornell.SME SSCrazy Eights Points: 8597 More actions · Answer 2

I have also use SFTP command line within xp_cmdshell to this also.

rnikandrov SSChasing Mays Points: 635 More actions · Answer 3

I cant use SSIS,

how do i specify port 22 in xp_cmdshell ?

Rick.Cornell.SME SSCrazy Eights Points: 8597 More actions · Answer 4

if you are sending to an ip then xxx.xxx.xxx.xxx:port, I think you can do the same for a url just tack on the :port.

It has been awhile since I done this too.

rnikandrov SSChasing Mays Points: 635 More actions · Answer 5

that didnt work for me,

all is get is :

idouputtmp

1Unknown host xxx.xxx.xxx.xxx:22.

2Invalid command.

3Invalid command.

4Interactive mode Off .

5Not connected.

6Not connected.

7Not connected.

8open xxx.xxx.xxx.xxx:22.

9user

10pass

11prompt

12binary

13cd /users/file.txt

14mput \file.txt

15quit

16NULL

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

Is this for a system that you plan on, or currently sell? If so I'd recommend using a SQL Agent job with a CmdExec step that does this. Then use sp_start_job to kick odd the job to do the sftp. One of my vendor system review questions is do you use xp_cmdshell because it is a significant security risk, that's why it is disabled by default in 2005/2008.

The other thing would be to use a certificate to sign your stored procedure and create a certificate login that is added to the sysadmin role allowing your stored procedure to call sp_configure and RECONFGURE to turn xp_cmdshell on and then back off. If you do this make sure you drop the password on the certificate, preventing it from being reused for other elevated purposes.

rnikandrov SSChasing Mays Points: 635 More actions · Answer 7

I was going with xp_cmdshell since i didnt know any better, now i do :).

Can you collaborate on how to do sftp with CmdExec ?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 8

The same way you would from a batch file or from a command prompt. Generally you can create a batch file to test your commands and develop your automation by calling that file from the command line. Then to create the job copy the contents of the batch file into the CmdExec job step, or just call the batch file from the job step.

rnikandrov SSChasing Mays Points: 635 More actions · Answer 9

That could work, however how would i connect to sftp on port 22 ?

rnikandrov SSChasing Mays Points: 635 More actions · Answer 10

putty requires host key verification, is there anything else that i could use?

rnikandrov SSChasing Mays Points: 635 More actions · Answer 11

Once again i cant use SSIS.

I've decided to go with WINSCP and have the following challenge:

single step job executes:

----------------------------------------------------------

\\app\WinSCP.exe /console /script=\\app\config.ul

----------------------------------------------------------

config.ul is written as:

----------------------------------------------------------

option batch on

option confirm off

open sftp://user:pass@ftp.net.com -hostkey="ssh-rsa 1024 00:00:00:00:00:00:00:00:00:00:00:00:00:00:00:00"

option transfer binary

put \\app\files\file.txt

close

exit

----------------------------------------------------------

and i get the following output:

----------------------------------------------------------

Executed as user: DOMAIN\USER. The step did not generate any output. Process Exit Code 1. The step failed.

----------------------------------------------------------

File is partly transferred, how do i increase time???

help...

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Lynn Pettis

SSC Guru

Points: 442467

July 23, 2010 at 2:00 pm

#1197749

I'm curious, why can't you use SSIS?

DataDog Hall of Fame Points: 3305 More actions · Answer 13

1) that is the default port for sftp so no need to specify

2) try space: servername<space>22

to be clear: "sftp" = the ssh ftp protocol?

that trace looks like actual "ftp" - completely different protocol

rnikandrov SSChasing Mays Points: 635 More actions · Answer 14

port 22 is default so its connecting fine and file is partly transferred,

how do i increase processing time in sql server???