Encrypting fields in a huge SQL DB
I have a huge SQL 2008 DB with ~300 million rows. some of the tables have columns with an encrypted value. The DB was gradually build using an application (.net 4). We are considering to move to some hardware (USB token) like encryption but that mean we will have to change the encrypted values in the DB. We'开发者_JAVA技巧ve written a small app that decrypts with the old key and then encrypt with the token but it takes days for it to run since I have to SELECT the row and UPDATE one at a time by ID. the db is indexed but still...
The Encrypt(string) method is a functionality provided by the USB token and I can access it via .net I'm looking for a more direct way to use that functionality. maybe access it through SQL or something.
You can use a CLR stored procedure to access the USB dongle on the server. You'd need UNSAFE access of course
This will reduce network overhead because you don't want to do a single 300 million row update. You'd still need it RBAR or batched: I'd suggest RBAR to keep it simple.
精彩评论