none
Store Confidential Documents in SQL Server RRS feed

  • Question

  • Hi,

    I have HRMS application on MySQL and I would like to move to Microsoft SQL Server.

    In my application, I need to store scanned copy of confidential documents such as employee contracts, warning letters.

    I though of storing the document as blob but I guess this will add a massive size to the database.

    I though of just storing a link to the document file and store the file on the file server but that means the file will be accessible for people accessing the file server.

    What's the best solution for such scenario?

    Thanks,

    Jassim

    Friday, March 11, 2016 8:54 PM

Answers

  • does this mean you recommend to store it in the sql server and not as a separate file with a file location only store in the sql server?
    Yes, I think I would store it in SQL server and encrypt it in the way described above. Otherwise, how would you encrypt in your File System location? How did you solve this problem in your MySQL solution?

    Benjamin Kettner

    • Marked as answer by Jassim Rahma Friday, March 11, 2016 9:52 PM
    Friday, March 11, 2016 9:51 PM

All replies

  • The size of your DB would not be my primary concern, after all when storing your documents in your filesystem they won't get less. 

    You can encrypt the data in SQL Server using Encryptbykey (https://msdn.microsoft.com/de-de/library/ms174361(v=sql.120).aspx)

    A nice tutorial can be found here: http://blogs.extremeexperts.com/2014/08/06/basic-column-encryption-of-data-with-sql-server/

    Does that help you in any way?


    Benjamin Kettner

    Friday, March 11, 2016 9:00 PM
  • my only two concerns are confidentiality and performance.
    Friday, March 11, 2016 9:11 PM
  • my only two concerns are confidentiality and performance.

    from https://www.simple-talk.com/sql/t-sql-programming/encryption-without-the-confusion/

    Encrypting data

    OK, so we've created objects and protected them but how do we protect data? This is when encryption gets interesting. SQL Server provides three different paths to encrypting data:

    • Using an asymmetric key directly – the danger here is the password is exposed in the T-SQL
    • Using a certificate with a symmetric key – the issue here is performance. It is not well suited for internet facing applications
    • Using a certificate with an asymmetric key – this is highly secure and fine for internet facing applications.

    Saying anything about performance without knowing the stress your system will be under, the concurrency you expect on it and the hardware would be like fortune telling imho. But given that there are not that many alternatives I would either go for it or think of a different technique that does not require storing sensitive data in a server that is accessible.

    As for security, I think that is also a question that is very hard to answer, because your database is not your single point of failure. Protecting everything and then storing certificates in a public share or using passw0d1 as Password will expose your system.

    In the end I think that both your concerns cannot be addressed on such a high level without any further information. But then again I am no security-wizard, there are many who are way more into this topic than me. :)

     


    Benjamin Kettner

    Friday, March 11, 2016 9:25 PM
  • does this mean you recommend to store it in the sql server and not as a separate file with a file location only store in the sql server?
    Friday, March 11, 2016 9:36 PM
  • does this mean you recommend to store it in the sql server and not as a separate file with a file location only store in the sql server?
    Yes, I think I would store it in SQL server and encrypt it in the way described above. Otherwise, how would you encrypt in your File System location? How did you solve this problem in your MySQL solution?

    Benjamin Kettner

    • Marked as answer by Jassim Rahma Friday, March 11, 2016 9:52 PM
    Friday, March 11, 2016 9:51 PM
  • In MySQL, I am storing it in MySQL database.

    Thank you so much for your replies.

    Friday, March 11, 2016 9:52 PM
  • You can store the documents in the SQL Server database in the file system at the same time. No, you don't store them double, you use the FILESTREAM feature. You can then store the documents through Win32API and encrypt them client-side which is the safest means of encryption, since no one with access to only the database cannot retrieve any data.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, March 11, 2016 11:26 PM