How Can I Securely Destroy Some Data Using Sql Server 2008 ? (using Dod Secure Wipe Or An Equivalent)

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

Solution 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.

Solution 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."

Solution 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".

Solution 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.

Solution 5:

Actually, chances of retrieving the data destroyed with DELETE are quite big, close to 100% :)

Data that you delete are kept in the transaction log, it's a part of how the transactions work. In other case, you either would not be able to ROLLBACK a transaction, or a COMMIT would take forever (like in old versions of PostgreSQL).

Best you can do without messing with the datafiles is:

1. Delete your data.
   • Perform multiple UPDATEs on the table to destroy old data.
   • Perform several large transactions and commit them for the trasaction log to be truncated. How many exactly depends on your log size.
   • CleanSweep space on disk occupied by old transaction logs.