How can I securely destroy some data using sql server 2008 ? (using DoD secure wipe or an equivalent)

Question

One of my clients wants me to perform a periodic "real" destruction of some of his old data, and I'm evaluating the best way to do it.

The data is in a table, and I want to destroy some of the rows contained in it.

I could do it manually by deleting/exporting the database on another computer/degaussing the hard drive/reimporting the saved data, but I need an automatic solution.

Is there an equivalent to the delete (as in delete * from foo) command which would perform a secure destruction of the data (using DoD secure wipe, or something like that?)

Do you see other ways to perform this automatic deletion?

Btw, I know the odds of someone retrieving some of the data I've destroyed using the sql delete command are very small, but some of my clients require it. So please don't turn this question into a global debate on the topic of data disposal procedures !

Edit : the problem I want to address is not "How should I destroy the data so it cannot be recovered" but rather "How can I convince my clients that their data cannot be recovered".

Answer 1

Use some form of encryption to store the data fields in the table.

When you decide to "delete", re-encrypt the data you will continue to use with a new key. Discard the old key, and delete the rows encrypted with the old key. Shrink.

Even if someone recovers the rows, w/o the old key no one will be able to restore the data. Just make sure the old key is really discarded - you can have it on a single usb stick only, and destroy the stick, etc.

Answer 2

From Books Online:

Delete operations from a table or update operations that cause a row to move can immediately free up space on a page by removing references to the row. However, under certain circumstances, the row can physically remain on the data page as a ghost record. Ghost records are periodically removed by a background process. This residual data is not returned by the Database Engine in response to queries. However, in environments in which the physical security of the data or backup files is at risk, you can use sp_clean_db_free_space to clean these ghost records.

This should zero-out your "free" data pages. It can also be used if Instant Initialization was used, but you decided you want to zero-out pages instead.

To answer your updated question, "How can I convince my clients that their data cannot be recovered", that BOL entry states it clearly, "Ghost records are periodically removed by a background process."

Answer 3

Basically, no. The standard operation won't do it, and if it did the data could still be reconstructed from transaction logs etc. Probably the closest you can come is to do it externally, copying and purging the database to another device, then doing a high-quality scrub delete on the old device, but as a security guy I'm not sure I'd even want to say that was a sssured delette.

Secure delete is a difficult problem. You might do better with a cryptographic approach, like Radia Perlman's "ephemerizer".

Answer 4

I am not sure if this meets the requirments of the DOD, but at a minimum I would be going through the following.

1. Delete the records the standard way
2. Take a new backup of the database (for future use)
3. Delete all existing backups (As they have the data), using a standard file deletion process that meets the standards
4. Shrink the database to free-up the unused space from the deleted records.

I think this will get you pretty close, the key though is the management of the shrink operation, which I am not 100% sure how that clears/handles data. Secondly, removing the old backups would be the "biggest risk" if you were looking at risk points in my opinion.