Securely remove columns (and traces thereof) from a SQL database

I’ve come upon a database that has previously held sensitive data (think “passwords/credit-card-numbers” kind of sensitive) in plain text. All of the sensitive data in question has been deleted, but I’m concerned that if accessed by someone with malicious intentions, the data might be recoverable through backups and/or transaction logs.

Is there a way to permanently and securely delete certain columns out of transaction logs and/or backup files while maintaining the integrity of all other data in a SQL database?

Well there is always a chance that someone can find the bits at various hardware layers if they had access to the physical disk(s), but I think you can be fairly confident you have removed traces of the data accessible through less nefarious means by:

1. rebuilding the table where the column was removed (perhaps perform a select into, drop the old table, rename the new one, and re-create any indexes)
2. starting the log chain over (a new full backup)
3. forcing the log to wrap around and re-zero-initialize by backing the log up twice and then performing a slight shrinkfile
4. eradicating any old full/diff/log backups (you may want to wait on this step until your data recovery window has passed, e.g. if you might still need to recover back to last Monday, wait until two Mondays from now – in the meantime moving them to a more secure location). At some point you know that a backup from two weeks ago is absolutely useless, because you’re not going to restore to a point in time that far back, so why keep it around?

No. The point of backups and transaction logs are to maintain the integrity of a database at a point in time. You’re asking to basically be able to scrub data from a historical record, but if you could do that, then you would invalidate the integrity of your backups. Consider someone doing that on “non-sensitive” information. That would totally destroy the ability to recover from that sort of logical corruption, something that transaction logs and backups are in place to prevent.

If you are not currently encrypting your backups and data files using either column level encryption or Transparent Data Encryption, your best approach would be to encrypt the individual backup files using something like PGP or placing them in an encrypted archive using 7zip or WinRAR.

Is there a way to permanently and securely delete certain columns out of transaction logs and/or backup files while maintaining the integrity of all other data in a SQL database?

No this cannot be done.

What you can do is possibly below :

1. Use eraser or secure delete to delete all old logs/database backups
   using sysinternal tool called SDelete -when you are absolutely
   100% sure that you dont need the old files.
2. Encrypt existing backups using third party encryption tools or you can even use open source tools.

3. Develop a manual procedure to overwrite sensitive information stored in database three times, then drop or delete the data. This way you are replacing the original bits on the disk with special values.e.g.

   • Enter 1111111111111111111s.
   • Enter 0000000000000000000s.
   • Enter 1010101010101010101s.
   • Delete / Drop.

Note: Doing step 3 will still leave traces in old T-logs or backups. As a result, there is a window of exposure during which your system may continue to contain sensitive from hours, days to months.