June 1, 2010 at 7:29 pm
FILESTREAM - Should you let antivirus software can the filestream volume?
June 1, 2010 at 11:45 pm
This was removed by the editor as SPAM
June 2, 2010 at 12:06 am
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.
.
June 3, 2010 at 6:54 am
is it safe to say we dont scan db files mdf/ldf why should we scan filestream files?
June 3, 2010 at 7:05 am
This was removed by the editor as SPAM
June 3, 2010 at 7:07 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 3, 2010 at 9:00 am
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.
June 3, 2010 at 9:23 am
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.
.
June 3, 2010 at 9:51 am
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?
June 3, 2010 at 11:29 pm
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).
.
June 4, 2010 at 7:41 pm
jacob sebastian (6/3/2010)
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:
/quote]
McAfee publishes specific directions on how to set up theie scanner as a proxy. There is a transient copy saved locally only long enough for the scan to occur (there's some form of a "scan and delete" option with the command-line version, destroying the temporary copy in the process). You actually can keep the filestream open, so you don't have to reopen and reread it.
----------------------------------------------------------------------------------
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?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply