I have several fields in a database table I would like to encrypt, but I would like to do it in a way that would allow me to easily change the encryption key every n months for security reasons.

My initial thought was to store the IV with the row so I could use that to decrypt each entry where the key for the encryption would be stored elsewhere, in code or a configuration file. This seems like it will be a nightmare every n months when I needed to change the key, as I would have to have some job run that would need to change the IV for every row along with decrypting/encrypting again all of the necessary columns to the new key.

It just seems like this approach could also bog the system down while trying to decrypt and encrypt again. I have not seen a lot of articles describing practical approaches to this problem, just mentioning that the key should be changed periodically. Any help would be greatly appreciated, I am new to the encryption game.

Also, I am using AES-256 encryption. I am using SQL Server but might migrate to MySQL in the future. The application is coded in C# .Net. We are storing personal information (irst, middle name, last name, ssn, etc.) and must be HIPAA compliant and we want to make sure as well that if a key is ever compromised we could quickly change the key on the fly.

  • Define "easily". If I write a script once (even if it is fairly complicated) and all I have to do is run it every n months, to me that is easy.
    – mikeazo
    Jul 16, 2015 at 18:07
  • I mean a way that does not bog the database down or the system as a whole down updating and re-encrypting rows of a database. Also, with this being for a social network we might have to implement some for of sharding, and I could imagine that having an impact with this approach. It seems as if there should be a more efficient way of doing this. Someway of separating concerns where changing the password is a one step process without having to run a job, etc. Jul 16, 2015 at 18:10
  • This table could group very rapidly, and having a job that went column by column, I could see become a very long running process. Jul 16, 2015 at 18:19
  • you might get better answers if you give a little more background on what the "security reasons" are for changing the key every two months. In that time assuming you're using something like 128-bit AES there's no chance of a brute-force, and if you're concerned about the key being compromised, then there may be alternate counter measures that would be more effective... Jul 16, 2015 at 18:38

1 Answer 1


If you are using MSSQL, switch to Transparent Data Encryption and don't encrypt by yourself. This will be massively more maintainable as you will be able to run queries against the data while encrypted.


Failing that (due to licensing?) use the built in cell level ecryption

  • 1
    I have seen several articles suggest that application level encryption was better. Also, at some point, we do plan to move to mysql, would that cause a problem with data migration scenarios? Jul 16, 2015 at 19:55
  • You would be able to migrate the data without problem (but would lose the built in encryption unless MySql does something similar) In app level security you lose most of the queryability of the database, and have to maintain all this logic yourself. Its very unlikely (but possible!) you are are as smart as MS, and almost impossible that you will be able to spend the time finding all the bugs/loopholes/gotchas that they have. Jul 16, 2015 at 20:02
  • You actually raise an intersting scenario, as I would have to query the data as well. Now, I thought about hashing the same fields and just searching for the hashed verion of the column when applicable. Does MS have to decrypt the table first to search or is there another mechanism in place, as it seems like that could be expensive. Jul 16, 2015 at 20:32
  • It does have a cost,but in most cases its about 5-10%. On the upside, all data is always protected at rest, and you don't have to worry about it in your application. If you use app encryption, unless you are doing queries on exact match (in which case a hash/encrypted query will work) you pretty much have to pull back the entire dataset, across the wire, decrypt all of it, and then filter locally (without any indexing etc) Thats likely to be much worse performance in almost every scenario. Jul 16, 2015 at 20:37
  • There are likely to be improvements in this space in the future (see article linked below), but for now the choices are pretty few. zdnet.com/article/… Jul 16, 2015 at 20:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .