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

Rick.Cornell.SME

SSCrazy Eights

Points: 8597

July 20, 2010 at 11:17 am

#1195693

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

Rick.Cornell.SME

SSCrazy Eights

Points: 8597

July 20, 2010 at 11:19 am

#1195697

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

rnikandrov SSChasing Mays Points: 635 More actions · Answer 3

rnikandrov

SSChasing Mays

Points: 635

July 20, 2010 at 12:32 pm

#1195736

I cant use SSIS,

how do i specify port 22 in xp_cmdshell ?

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

Rick.Cornell.SME

SSCrazy Eights

Points: 8597

July 20, 2010 at 1:50 pm

#1195780

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 :

id ouputtmp

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

2 Invalid command.

3 Invalid command.

4 Interactive mode Off .

5 Not connected.

6 Not connected.

7 Not connected.

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

9 user

10 pass

11 prompt

12 binary

13 cd /users/file.txt

14 mput \file.txt

15 quit

16 NULL

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

rnikandrov

SSChasing Mays

Points: 635

July 21, 2010 at 7:38 am

#1196154

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.