What is the best way to securely delete a database in MS SQL Server 2012?

ScottBurkett
ScottBurkett used Ask the Experts™
on
When deleting an entire SQL database, what is the recommended/best practice for making sure that the data contained in that database is deleted securely?  I need to be able to delete a database from a system that I run and verify to a client that their data has been deleted securely and cannot be recovered (at least, it's not practically feasible to do so).

Thanks,
--Scott
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John EsraeloDatabase / SQL Developer

Commented:
Scott,
Just to verify and confirm;  when you say 'securely' deleting, are you referring to SQL server deletion process or the residue in the file system (OS)?    Deletion should have removed the LDF and MDF, right?   Do you still see them files?  


John E
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
but don't forget backups! Any backups would let the client restore and recover the data. That you cannot do anything about it.
John EsraeloDatabase / SQL Developer

Commented:
Unless it is protected by  the Transparent Data Encryption.    Or, simply as Eric pointed out, just make sure that the backups are secured physically and away.  Whether are TDE'd or not.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I'm actually talking about both issues:  SQL secure deletion and OS system residue.  I can account for our backups because we have and follow a rigid policy for deleting backup files with defined data destruction policies and procedures around this task, but the data in the database and the file system remnants are another story.

Basically, I need to be able to confirm with individual clients that any data  that may have contained sensitive information  that was stored on my system has been securely and permanently removed from the system that I run after the database has been deleted.

This is doable, though not necessarily easy, on the OS side by wiping empty file space with a secure deletion program.  That process, however is pretty I/O intensive and has downstream effects on other databases and  other server processes.  Is this the best/most secure option or is there a better one?

And, even if the LDF and MDF files are deleted, how are they deleted?  Are they overwritten a certain number of times?  Does that depend on how the database is deleted?

Cheers,
--Scott
John EsraeloDatabase / SQL Developer

Commented:
Scott,
You may want to take a look at TDE.  Might give you some ideas on how to handle your situation.
TDE, works great against sql data files and the backup files.  
Take a look at it and let us know what you think.
thx
https://www.simple-talk.com/sql/database-administration/transparent-data-encryption/

John E

Author

Commented:
Thanks, John.

I do have some databases that I host that are encrypted with TDE and understand that there are some pretty substantial benefits to using it, including being able to effectively prevent any further access to data by securely deleting the keys to the TDE-protected database.  But there are some drawbacks, too, including the fact that there are issues with DR sites (at least my DR site) being able to manage TDE in a failover and having to have the Enterprise version of SQL Server in both locations (production and DR) in order to get anything remotely resembling a fully functional DR site.

Add to that the fact that I need to be able to assure clients that don't want TDE for whatever reason that they can be assured that their data has been securely deleted from my systems and you have the reason for my question.

Thanks for the feedback!  Keep it coming if you have more information.

--Scott
John EsraeloDatabase / SQL Developer

Commented:
Scott,

You have mentioned the following
verify to a client that their data has been deleted securely and cannot be recovered
What do you use (tool) to display the content of the drive that used to host your database and display that in a low-level to client in order to assure the destruction of your database?    I was thinking about a carved up drive (shared) space in your SAN that can be easily used for your DB and also get destroyed and replaced by another (with the same drive letter) may be a good solution.  Especially when the destroyed or release drive letter's space is returned to the pool without a trace.   And, of course the new provisioned space would be blank.   I am not a network engineer but something that I am thinking out loud.
 

John E
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
the .mdf/.ldf files are just files like any other for the OS. At some point the space will be overwritten just like any other file.

Author

Commented:
I understand that the space taken up by the data files in question will be marked as available to the OS and will eventually be overwritten, but for some clients, this assurance is not enough and we need to provide information, usually in the form of a log file from a data shredding tool like Eraser, for files in the file system.  What I'm really looking for is a way that we can get a similar report out of SQL Server that would verify that the contents of the database were securely disposed of.  If we delete the database from within SQL Server we have neither the assurance that the data in the database has been wiped securely nor that the files themselves were deleted securely, since my understanding is that the MDF and LDF files are deleted during the SQL procedure that deletes the database.

I realize that it may seem like I'm being really picky about this, but I assure you that I'm only  being exactly as picky as my clients. :)

Thanks, everyone, for the feedback.

Cheers,
--Scott
John EsraeloDatabase / SQL Developer

Commented:
Scott,
You are right about client(s) may be picky about this situation.  However, I agree that the deleted files still contain partial information.  Think of the agencies that are able to retrieve partial data from a damaged spindle / platter on a physical dedicate drive (old school :)   for X$$$ and return the data on a certain type of media back to the client  / customer.

And, yes, you are right about 0'ing the data is going to take a long time and do not want that in production.    A virtual/ non-physical/SAN/etc  drive could be still a possible solution as I mentioned earlier.
I am actually going to research this myself. I would probably might face your situation one of these days :)

John E
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
I really don't think you have another option then to use an application that will write on each empty spot of the disk. I know it is CPU intensive but I don't see another way of doing it. It won't be at the SQL level for sure.
John EsraeloDatabase / SQL Developer

Commented:
Scott,
I did my research and here is the deal.  If you whether having a physical or network assigned space; and, if you have one drive letter associated with that DB of yours (as an example) then upon deletion of the database files, that drive letter may be disconnected, assigned to another device / computer (non production) then you can spend all day 0'ing that out the drive without affecting the live I/O.
Then you can reassign that back to the server and showing the client that it has been 0'd out.
Don't have any other suggestions :)  sorry  and good luck and share with me if you should find a good solution.
thx

John E
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
You could first detach the db.  Then you can physically overwrite the database files yourself as many times as you feel the need to.  Finally then delete the overwritten files.
John EsraeloDatabase / SQL Developer

Commented:
Scott P. ,

How are you proposing the physical overwrite to Scott?  What's Scott is trying to minimize, if I am not mistaken, is the I/O in production. Even copying or moving the detached DB to another media will still have the original digital data on the production drive (physical drive or san drive).
As a matter of fact, even if the command
sp_msforeachtable 'truncate table ?'

Open in new window

is not going to be sufficient.  By the way, please be careful with this command as once it starts you cannot stop it without any data-loss.  :)   So, even then, the actual meat is still on the platter, so to speak.   So, possibly having a dedicated drive / space and its disconnection from production for sole purpose of destruction would be a better choice at this level.

thx


John E

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial