Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How Can A File be Totaly Removed from an SQL Server Database Expand / Collapse
Author
Message
Posted Monday, October 15, 2012 8:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:43 AM
Points: 442, Visits: 620
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
Post #1372761
Posted Monday, October 15, 2012 8:56 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 40,385, Visits: 36,827
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 2008, MVP
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

Post #1372769
Posted Monday, October 15, 2012 9:01 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:43 AM
Points: 442, Visits: 620
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
Post #1372770
Posted Monday, October 15, 2012 9:06 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 40,385, Visits: 36,827
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 2008, MVP
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

Post #1372771
Posted Monday, October 15, 2012 9:18 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 8:13 PM
Points: 356, Visits: 921
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.
Post #1372776
Posted Monday, October 15, 2012 10:13 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:43 AM
Points: 442, Visits: 620
Thanks Scott

i think tis is a product for securing and protiectin documents

I need the opposite to get rid of them

Jim
Post #1372804
Posted Tuesday, October 16, 2012 5:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 14, 2014 8:26 AM
Points: 2,894, Visits: 3,278
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: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1373140
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse