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 12»»

FILESTREAM - Should you let antivirus software can the filestream volume Expand / Collapse
Author
Message
Posted Tuesday, June 1, 2010 7:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 15, 2011 10:16 AM
Points: 16, Visits: 375
FILESTREAM - Should you let antivirus software can the filestream volume?
Post #931053
Posted Tuesday, June 1, 2010 11:45 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:19 AM
Points: 3,928, Visits: 5,115
you need to consider the pro's and cons
> pro - if someone uploads a large object, that contains a virus, it can be identified (but not actioned)
>con - will result in a performance hit (or locking and / or timeouts).


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #931112
Posted Wednesday, June 2, 2010 12:06 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
I think it is a good idea that the anti virus software is configured to scan the FILESTREAM files. However, you need to make sure that if an infected file is found, it is not deleted immediately but quarantined. If the anti virus software deletes infected files (from the filestream data container) you will end up with a corrupted database.

You can do a dbcc checkdb to check the consistency of the database. If a filestream data file is missing (in case the anti virus software finds an infected file and move it to the quarantine folder), dbcc will raise an error and will report the name of the file that is missing. You can then match this information with the files in the quarantined folder and verify whether the error is caused by the antivirus (which moved the file to quarantine folder) or due to something else.

If such a case is found, you can repair the database by running DBCC with REPAIR_ALLOW_DATA_LOSS in which case, the entire row (not only reference to the filestream data) will be deleted from the table.

One 'un-documented' work around is to create an empty file with the same name and put in the filestream folder. You will certainly lose the filestream data, but the row will not be deleted and the database corruption will be fixed. This option is applicable only in case you care for the data in the row and do not mind the filestream data getting deleted. In many cases you may not want to do this because one of the basic purpose of FILESTREAM is to ensure consistency between the relational data and data stored in NTFS. So if the filestream data is lost, the relational row should also be deleted.



.
Post #931120
Posted Thursday, June 3, 2010 6:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 15, 2011 10:16 AM
Points: 16, Visits: 375
is it safe to say we dont scan db files mdf/ldf why should we scan filestream files?
Post #932085
Posted Thursday, June 3, 2010 7:05 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:19 AM
Points: 3,928, Visits: 5,115
Laura-205946 (6/3/2010)
is it safe to say we dont scan db files mdf/ldf why should we scan filestream files?

Definitely not.
database files are unlikely to pick up viruses, while a document with an embedded image that may contain a virus could be introduced via filestream.

As stated by Jacob above, it would be a good idea for the antivirus to monitor the folder.
My earlier post points out the pros and cons of doing so.


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #932094
Posted Thursday, June 3, 2010 7:07 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 @ 6:33 AM
Points: 42,836, Visits: 35,967
http://technet.microsoft.com/en-us/library/dd206979.aspx

Disable antivirus scanning of FILESTREAM volumes when it is not necessary (typo removed). If antivirus scanning is necessary, avoid setting policies that will automatically delete offending files.



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 #932098
Posted Thursday, June 3, 2010 9:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 15, 2011 10:16 AM
Points: 16, Visits: 375
so if someone says how r you controlling if the blob is in the database if at that point we dont scan the db file while do we have to scan when using filestream.
Post #932212
Posted Thursday, June 3, 2010 9:23 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
I do not insist that you 'should' do a scan of the FILESTREAM data. As MSDN (link posted by gail) says, if it is 'necessary' do it, if not 'do not do it'.

There is a big difference from how the blob data is stored within SQL Server and how it is stored in FILESTREAM. When you store some blob data (usually content of a file) into FILESTREAM, the content is stored 'as is' into the filestream data container. It means an exact copy of your original file (which could be infected) is stored in filestream. This file can be opened/executed by a malicious program or human being who has the required windows permissions without letting SQL Server know about it.

I **think** this could be one of the reason why someone might decide to enable a virus scan on the FILESTREAM data.


.
Post #932226
Posted Thursday, June 3, 2010 9:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 30, 2014 5:24 PM
Points: 7,139, Visits: 15,191
The answer is to scan it BEFORE it gets to the DB. Once you have them in, you shouldn't scan the internal volume like has been mentioned before.

Just like any other type of connection to SQL, scrub your inputs. a direct connection into the DB which is not secured like this is just a hand-grenade waiting to happen.

Most of the current AV programs have the ability to act as a proxy to file uploads: instead of uploading directly into the DB, upload to them, it scans and then pushes the contents wherever you want.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #932255
Posted Thursday, June 3, 2010 11:29 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
I think Matt has put forward a very interesting point. Not being an web developer, I did a Google search and found this thread which talks about a similar approach. http://forums.asp.net/t/890935.aspx

It looks like these scanning software products cannot work with memory streams. You will have to save the stream to a disk file before running the scanning software. Which means your upload procedure might look like the following:

1. Save the incoming stream (the file to be uploaded) into a disk file
2. Run the scanning software
3. Wait for the scanning software to complete
4. (if there is no .NET interface, you might need to read the output file generated by the anti virus program to see the results)
5. If the file is clean, begin a transaction and open the FILESTREAM data file for write
6. Open the source file (the fiel you created at step 1) for read operation
7. read from the source file and write into the FILESTREAM data store.
8. close the files and commit the transaction.

Note that this approach involves writing two copies of the source file..first write it into a local file for scanning and then store it into the FILESTREAM data store.

This approach has more over head than a regular upload and more than double IO operations. However, it makes sure that the data getting into the FILESTREAM store is clan.

It is still not safe from some malicious software or human with sufficient permissions trying to overwrite the FILESTREAM data file with infected content. SQL Server does not lock the files in the FILESTREAM data store, unlike the MDF/LDF files. So a user or software with sufficient permissions can still replace the filestream data files or modify it. In such a case, when the data is served back to the client, it could be completely different from what is uploaded and could be an infected file as well (though the chances of something like that will be very very rare).



.
Post #932585
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse