locked
Column level data encryption in SQL Server 2014 RRS feed

  • Question

  • Hello All,

    I need to encrypt some column level data in multiple tables in SQL server 2014. I've never tried encryption in SQL server 2014. How can I achieve it? Any help/inputs is greatly appreciated.

    Thanks
    Wednesday, June 17, 2015 3:33 PM

Answers

  • That article demonstrates how to create a new column which contains the encrypted value of an existing column.  So the direct answer to your question is both yes and no depending on how you intend to go about this. 

    It is yes because you seem to be in the same boat - you have an existing column that contains real data.  Your encrypted column will be a binary value, which means that you cannot simply update the existing column with the encrypted content.  You must add a new column of the appropriate datatype and then update that new column as demonstrated. Though not obvious, the different datatype is a requirement since you cannot store varbinary data in a varchar column - well, you can try but you will be disappointed.

    Which leads to the no part.  Once you have the encrypted column populated, you would obviously need to remove the original column since its presence would negate the whole idea of encryption. Ultimately, the idea is that you store the encrypted value only and that means you have a single column.  Had you taken this approach originally, then you would have created the table with the single column for your sensitive information and stored just the encrypted value when inserted or updating this value.

    Perhaps before you start down this path, you should consider how this column is used throughout your system.  Indexing is an obvious concern, and that leads to searching.  So perhaps some simple web searching is in order.  For example

    msdn - using cell-level encryption

    msdn - indexing encrypted data

    Wednesday, June 17, 2015 7:19 PM

All replies

  • Try this link

    https://msdn.microsoft.com/en-us/library/bb964742.aspx


    Please click "Mark As Answer" if my post helped. Tony C.

    Wednesday, June 17, 2015 3:34 PM
  • Hi Toni,

    I appreciate your quick response. I'm a little confused here. I read the article on Encrypt a column of data from above link. Do I need to create a new column in the table for encrypted data? I mean is it possible to encrypt the existing column instead of creating a new column for encrypted data? Say Column A has a credit card information which I need to encrypt. Per the article there is a need to create Column B which will store the encrypted credit card information. Is it possible to do an encryption on column A instead of creating extra Column B.

    Any help is appreciated.

    Thanks 

    Wednesday, June 17, 2015 3:43 PM
  • Any help is greatly appreciated.
    Wednesday, June 17, 2015 6:19 PM
  • That article demonstrates how to create a new column which contains the encrypted value of an existing column.  So the direct answer to your question is both yes and no depending on how you intend to go about this. 

    It is yes because you seem to be in the same boat - you have an existing column that contains real data.  Your encrypted column will be a binary value, which means that you cannot simply update the existing column with the encrypted content.  You must add a new column of the appropriate datatype and then update that new column as demonstrated. Though not obvious, the different datatype is a requirement since you cannot store varbinary data in a varchar column - well, you can try but you will be disappointed.

    Which leads to the no part.  Once you have the encrypted column populated, you would obviously need to remove the original column since its presence would negate the whole idea of encryption. Ultimately, the idea is that you store the encrypted value only and that means you have a single column.  Had you taken this approach originally, then you would have created the table with the single column for your sensitive information and stored just the encrypted value when inserted or updating this value.

    Perhaps before you start down this path, you should consider how this column is used throughout your system.  Indexing is an obvious concern, and that leads to searching.  So perhaps some simple web searching is in order.  For example

    msdn - using cell-level encryption

    msdn - indexing encrypted data

    Wednesday, June 17, 2015 7:19 PM