locked
Encrypt individual columns or consolidate all such data into a single table.. RRS feed

  • Question

  • Currently we have sensitivie data in columns scattered in different tables.  What are the pros\cons of encryting the columns "in place" in the tables where they stand vs. creating a master table where all encrypted data is stored?



    TIA,

    barkingdog


    Tuesday, March 17, 2009 6:16 AM

Answers

  •   By encrypting “in place”, do you mean without any changes in the schema? If that is the case, then I have to strongly advise against it. Encrypted data is binary and the columns to store encrypted data should be of type varbinary. You should also consider that when encrypting the data, the length of the ciphertext will be larger than the plaintext length due to the ciphertext message (i.e. envelope).

      Please take a look to the following article I wrote, it may be useful in helping migrating existing applications: http://blogs.msdn.com/raulga/pages/589691.aspx

      Please let us know if this information helped,
      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, March 17, 2009 10:37 PM

All replies

  •   By encrypting “in place”, do you mean without any changes in the schema? If that is the case, then I have to strongly advise against it. Encrypted data is binary and the columns to store encrypted data should be of type varbinary. You should also consider that when encrypting the data, the length of the ciphertext will be larger than the plaintext length due to the ciphertext message (i.e. envelope).

      Please take a look to the following article I wrote, it may be useful in helping migrating existing applications: http://blogs.msdn.com/raulga/pages/589691.aspx

      Please let us know if this information helped,
      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, March 17, 2009 10:37 PM
  •  I Raul

    You were absolutely right about my original intention. I am reading through your blog and I think it will clarify the issues for me.

    Barkingdog
    Wednesday, March 18, 2009 1:41 AM