none
Security of data transfers from server to server

    Question

  • Hi -

    I am new to SSIS, and looking to start using it as a replacement for a set of DTS Packages.

    Firstly, if I want to transfer data from one SQL Server 2012 database to another (residing on different servers) how secure is the data transfer - relative to SFTP or FTPS of a .txt file?

    Secondly, assume I have Sql Server 2012 on server A, and a .txt file on server B.  I use SSIS on server A to connect to server B and read in the contents of the .txt file, and write them to the SQL Server 2012 database.  Is this as secure (or more secure) than sending the .txt file from server A to server B (using SFTP or FTPS for example) and then reading the data from the file and writing it to my database?

    Thanks in advance for any info.

    Wednesday, February 12, 2014 4:52 PM

Answers

  • If I understand your questions correctly. 

    1.  The data is transfered using the database connection.  As a default it is not encrypted, however you can use SSL encryption. http://support.microsoft.com/kb/316898

    2.  The connection to the file system is not encrypted and cannot be.

    3.  You can do this.  But the file "at rest" wil be unencrypted.

    What exactly are you concerned about?

    • Marked as answer by SO'M Thursday, February 13, 2014 2:17 PM
    Wednesday, February 12, 2014 6:24 PM

All replies

  • You can use SSIS for all these

    to do SFTP see below link

    http://visakhm.blogspot.in/2012/12/implementing-dynamic-secure-ftp-process.html

    For transfering txt file to SQL Server you just need a data flow task with FlatFile source and with OLEDB destination

    it looks something like this except the looping

    http://visakhm.blogspot.in/2012/05/package-to-implement-daily-processing.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by vr.babu Thursday, February 13, 2014 1:51 PM
    Wednesday, February 12, 2014 5:41 PM
  • Thanks for the reply Visakh.

    However, my question was not how to do these things, but how secure the movement of data is when doing these things.

    Which is most secure?

    1. Using SSIS on Server A for transferring data from database on server B to database on server A
    2. Using SSIS on server A to read data from a flat file on server B (and write it to the databse)
    3. Using SFTP/FTPS to transfer the file to server A from server B, and then using SSIS on serverA to load the data to the database

    I have security concerns around moving data between servers using SSIS.



    • Edited by SO'M Wednesday, February 12, 2014 6:18 PM
    Wednesday, February 12, 2014 6:16 PM
  • SFTP is most secured out of the given options above. So I would prefer option 3 if security is a concern.

    But if Server A and ServerB are in same domain you can even create a domain proxy account for the process with required permissions and configure ssis package to use it so as minimize any security issues too.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, February 12, 2014 6:22 PM
  • If I understand your questions correctly. 

    1.  The data is transfered using the database connection.  As a default it is not encrypted, however you can use SSL encryption. http://support.microsoft.com/kb/316898

    2.  The connection to the file system is not encrypted and cannot be.

    3.  You can do this.  But the file "at rest" wil be unencrypted.

    What exactly are you concerned about?

    • Marked as answer by SO'M Thursday, February 13, 2014 2:17 PM
    Wednesday, February 12, 2014 6:24 PM
  • Thanks Tom.

    We currently use FTP to transfer these files from server to server and then read them into SQL Server 2000 using a DTS Package.

    New security policy dictates that FTP can no longer be used, even inside the firewall due to the classification of the data being transferred.

    As we now migrate to SQL Server 2012, I need to assess the options available for moving this data from B to A securely.

    Server B runs Oracle.  Will the SSL solution to option 1 encrypt data between Oracle and SQL Server or only SQL Server to SQL Server transfers?

    Wednesday, February 12, 2014 6:35 PM
  • Configuring Oracle to support SSL connections can be done.  You would need to talk to your Oracle DBA.


    PS.  This not really a SQL 2012/SSIS question.  The problems/questions would exist if you were trying to do this in SQL 2000.
    • Edited by Tom Phillips Wednesday, February 12, 2014 8:04 PM
    Wednesday, February 12, 2014 8:02 PM
  • Hi Tom - thanks again. 

    So can I consider the use of SSL encryption on database connections for any data transfer as secure as using SFTP/FTPS to transfer the source file from server to server?

    It is only a SQL 2012 question because we need to move data from a remote txt files into a SQL 2012 database, and I am exploring the best way to do this - SSIS, or not SSIS - from a security point of view.

    What has changed is the policy around using unsecured FTP.

    Thanks again

    Thursday, February 13, 2014 8:36 AM
  • Secure FTP and SSL encrypting the connection to Oracle are both encrypting point to point communication (slightly differently) between the servers.  They would offer the same level of security across the network.

    The only thing you are protecting against in either mode is someone using a "packet sniffer" on your private network between the 2 servers.  If you don't trust your network admins, you have a much bigger problem.

     


    • Edited by Tom Phillips Thursday, February 13, 2014 1:20 PM
    Thursday, February 13, 2014 1:20 PM
  • Ha.. I'm not sure it's a trust issue, it's just a security policy decision to encrypt certain classifications of data even inside the firewall. 

    You can never be too careful with sensitive data handling.

    Thanks again Tom.

    Thursday, February 13, 2014 1:24 PM
  • I have worked with extremely sensitive data and internal network connections are generally assumed to be "trusted" and not subject to this kind of encryption restrictions.  Only if you are crossing into a "public" or untrusted network boundary does this come into play.

     
    Thursday, February 13, 2014 1:33 PM
  • It's a fair comment.  I don't write the policy, I just have to make sure we adhere to it :-)
    Thursday, February 13, 2014 1:36 PM