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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Security » How Can A File be Totaly Removed from an SQL...

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

Rate Topic

Display Mode

Topic Options

Author

Message

JC-3113

Posted Monday, October 15, 2012 8:38 AM

SSC-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

GilaMonster

Posted Monday, October 15, 2012 8:56 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:30 AM
Points: 37,742, Visits: 30,021

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

JC-3113

Posted Monday, October 15, 2012 9:01 AM

SSC-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

GilaMonster

Posted Monday, October 15, 2012 9:06 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:30 AM
Points: 37,742, Visits: 30,021

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

Scott D. Jacobson

Posted Monday, October 15, 2012 9:18 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:17 PM
Points: 335, Visits: 841

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

JC-3113

Posted Monday, October 15, 2012 10:13 AM

SSC-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

EdVassie

Posted Tuesday, October 16, 2012 5:08 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:26 AM
Points: 2,621, Visits: 2,759

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.

Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005. 25 March 2013: now over 23,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 »

Permissions