sql server secure ftp

Question

Is there a way that can do secure ftp using sql server 2005? I need to secure ftp to some server and pull a file. It would be best to do it using T-SQL, if not, any other way? Actually I already googled a bit and below is my results, I have no experience with CLR, some experience with SSIS. I would like to here your suggestions regarding these two approaches or any other way?

using SSIS with third party component. SSIS is integrated ETL tool of SQL Server 2005. It has a ftp component but it can only do FTP not secure ftp. But there are many third party components developed for SSIS that can do SFTP. SSIS can use these third party components to achieve SFTP function. Then the SSIS task can be scheduled in SQL agent job.
use CLR (common language runtime), which is using .NET code to extend the ability of SQL server. E.g. using .NET code to generate some class which can do SFTP job, then this can be integrated into SQL server as a stored procedure kind of thing. Then it can be called from within T-SQL code/stored procedure.

Thank you, Nian

score 1 · Answer 1 · answered Aug 22 '12 at 11:25

1

You can use xp_cmdshell, if that is enabled on your server, to execute a command line.

eg: http://www.tekkies.co.uk/v2/articles/1-general/15-ftp-using-xpcmdshell-sql2k

However, whatever you do, SQL server is not the best tool for the job. It's a database server.

answered Aug 22 '12 at 11:25

podiluska

50,950
7
98
104

It should work with anything - see http://stackoverflow.com/questions/3477471/whats-a-decent-sftp-command-line-client-for-windows for SFTP clients – podiluska Aug 22 '12 at 12:52
I will be able to test next week. We will see. Thanks. – thotwielder Aug 23 '12 at 10:48

score 0 · Answer 2 · answered Aug 22 '12 at 12:54

Indeed, your main options for SSIS are either take a pre-created task for SFTP and FTPS or write one using corresponding .NET components. In regards to existing tasks the alternatives are /n software's SSIS tasks, Cozyroc and our BizCrypto . Our BizCrypto uses SecureBlackbox .NET components for SFTP and FTPS (another our product).

Note that "Secure ftp" is a confusing term. FTP protocol (as defined in RFC 959) is secured using SSL/TLS and you get FTPS. SFTP (SSH File Transfer Protocol) is a completely different thing - though it's also almost always used as a file transfer protocol, it has very little in common with FTP protocol. Our products support both SFTP and FTPS.

sql server secure ftp

2 Answers2