Post reply

How Can A File be Totaly Removed from an SQL Server Database

  • October 15, 2012 at 8:38 am

    #269514

    Hi Folks

    My administrators have asked how does sql server handle the "Total" removal of a file from a SharePoint SQL Server database.

    Basically the scenario is something like this:

    We have an unclassified sharepoint sql server database in which only unclassified documents are placed via SharePoiint

    Ocassionaly, someone gets a classified document an unknowingly uploads it into Sharepoint.

    The question posed to me is how can we ensure that the document is entirely removed from the SQL Server database since sql server stores files in the database itself ?

    Thanks

    Jim

  • October 15, 2012 at 8:56 am

    #1549165

    Um.... With extreme amounts of difficulty. Given that it's sharepoint, even harder.

    In a normal, non-sharepoint DB, one option would be something like

    Delete the sensitive data (just marks the space as not needed, does not overwrite)

    Export all remaining data and all objects

    Detach the database that had the sensitive data in

    Use a secure delete program to wipe those files off disk (and all backups that may have the sensitive data in them)

    Recreate the DB.

    But you can't do that with sharepoint, which makes the problem significantly harder

    If you find out soon enough you could shut down SQL use a secure delete to erase the DB files then restore a backup that didn't have the sensitive data, but that means identifying the problem very soon, and still may result in data loss.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 15, 2012 at 9:01 am

    #1549166

    Thanks Gail

    I did nolt think it was going to be easy

    This sounds like SharePoint is going to be a problem when it comes to deleting the files

    Jim

  • October 15, 2012 at 9:06 am

    #1549167

    Even without sharepoint it's an incredibly difficult process (and the bigger the DB the worse it gets). This is definitely one place where prevention (warning messages in sharepoint, training) is better than cure

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 15, 2012 at 9:18 am

    #1549172

    Maybe with something like this?

    http://www.titus.com/software/sharepoint/index.php

    If not to delete them after the fact but, as Gail suggested, keep them from uploading them in the first place.

  • October 15, 2012 at 10:13 am

    #1549192

    Thanks Scott

    i think tis is a product for securing and protiectin documents

    I need the opposite to get rid of them

    Jim

  • October 16, 2012 at 5:08 am

    #1549461

    Use a secure delete program to wipe those files off disk

    If you do this, make sure you use a secure delete process that is compatible with the storage you are using.

    Any 'secure delete' program that simply works by multiple writes to the file as defined by NTFS will do nothing to the underlaying data on most SANS and all SSD storage.

    This is because most modern storage gets it performance by only doing writes and reads, and never does updates. When the OS wants to update a buffer full of data, the storage grabs some space from a free space pool and does the write to that. The original slot for the data on the storage device is then put in a garbage collection pool for recycling. The recycling process typically just marks the slot as empty and puts it back into the free space pool.

    A 'secure delete' program that writes zeroes 5 times to the file space as defined by NTFS will only succeed in writing 5 sets of zeroes to the storage, leaving the original data untouched except for what garbage collection does.

    Therefore if you want to do any form of secure delete that removes any trace of the bit pattern of the original data, you need something that can tie up the NTFS view and the storage device view of where the data is, and make sure the storage slots that hold the data get securely cleared. Most SAN vendors will supply such a program, but only as an extra-cost item to the basic SAN package.

    If you have data on commodity SSD storage (eg OCZ, Crucial, Kingston, etc) the best way to do a secure delete is to put your SSD storage device into a furnace or a shredder.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply