Column level data encryption in SQL Server 2014

Question

I need to encrypt few column level data in multiple tables in SQL server 2014. I'm a little confused after reading an article on Encrypt a column of data from the microsoft forum. 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. Thanks

Answer 1

In almost every case, you have to create a new column for encrypted data. Encrypted data is stored in SQL Server as varbinary type. Plaintext data is almost always in varchar or some other character type. In your case, your ColumnA is probably varchar(16) or something like that while ColumnB is probably varbinary(128).

You can and should drop the original column after the encryption process is complete else you are still exposed to most security risks. You can also rename the new varbinary type column that holds the ciphertext of the sensitive data to the original column name if you like. Just remember that it now holds data in ciphertext instead of the original plaintext.

If your source type is also varbinary and has sufficient length to store the newly encrypted data, you could do an in-place encryption but the risk there is if you change your mind or discovered a bug in your code during or shortly after execution, you don't have a quick & easy way to back out of the changes. You also lose the ability to do side-by-side testing/verification when you encrypt in place. Finally, you probably won't have too many varbinary columns that require encryption, hopefully.