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

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
ScottBurkettAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John EsraeloDatabase / SQL DeveloperCommented:
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 ConsultantCommented:
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 DeveloperCommented:
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.
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

ScottBurkettAuthor 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 DeveloperCommented:
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
ScottBurkettAuthor 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 DeveloperCommented:
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 ConsultantCommented:
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.
ScottBurkettAuthor 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 DeveloperCommented:
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 ConsultantCommented:
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 DeveloperCommented:
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
Scott PletcherSenior DBACommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John EsraeloDatabase / SQL DeveloperCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.