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!
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
Thanks,
--Scott
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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
but don't forget backups! Any backups would let the client restore and recover the data. That you cannot do anything about it.
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.
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
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
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
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
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
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
Scott,
You have mentioned the following
John E
You have mentioned the following
verify to a client that their data has been deleted securely and cannot be recoveredWhat 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
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.
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
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
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
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
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.
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
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
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.
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
thx
John E
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 ?'
thx
John E
Premium Content
You need an Expert Office subscription to comment.Start Free Trial