Jayizzle Jayizzle - 1 year ago 77
SQL Question

Updating a production database with SQL encryption - mass update shortcuts?

I am trying to encrypt a lot of columns in a lot of tables. I have a basic understanding of how to encrypt and decrypt using keys and certificates, but not how I can implement this on a large scale.

For example, I have a few columns in a table with sql code on our production apps to select to insert or update. Would I need to go back to revise each and every sql query to include a ENCRYPTBYKEY() and DECRYPTBYKEY() method in these queries? Are there any solutions or shortcuts to update a production server to do this?

Answer Source

yes, you would need to update all writes to use encryptbykey and all reads to use decryptbykey

If you are using stored procedures or have CRUD operations centralized and locked down, this can make things easier - but it is still a time consuming process. Another option is Transparent Data Encryption (TDE) which can be applied to an entire database w/o changing code; though there are still several commands that need to be run on the server.