FileStream: T-SQL vs Win32

Question

One of our teams is building a database (and application) that will use the FileStream feature of SQL 2008 to store documents. According to the MS best practices for filestream, the Win32 APIs are the preferred way to access FileStream data, vs using T-SQL.

However, using the Win32 APIs requires using integrated authentication to connect to the database. This is a concern for the team because they do not want to give users direct access to the database. They want the application to connect using a SQL username and password.

What are the pros / cons of using Win32 vs T-SQL to access filestream data? Are there any factors that would make using T-SQL impossible?

Answer 1

First lets analyze the concern: an embedded user name and password are more secure than using Windows Authentication. This is an embarrassingly wrong assumption. All it does it gives a false sense of security. As a matter of fact, one cannot hide a secret inside an application. It can always be revealed. In this day and age, all it take is one intrepid skillful hacker to reveal the embedded password, or the method how to retrieve it, and thanks to Google and friends everybody interested will learn it, no matter how unskillful he or she is. In a security analysis, a login and password 'hidden' on the user workstation should be considered as secure as if they were handed in writing to the said user. By using a hidden login and password as means to protect access all you achieve is you loose accountability and the track record of who did it, when they'll do it. Always rely on proper access rights for security protection. Never rely on obfuscated password that are 'hidden' on the very attacker's machine.

If you want a protection scheme that allows users only access to the specific functionality (ie. they can only update data in this way as opposed to writing arbitrary UPDATEs), use the good ole' tried and tested methods of ownership chains via stored procedures and grant only EXECUTE access to the true authenticated user. For an even better solution, use code signing.

As for the T-SQL vs. Win32 access, the FILESTREAM Best Practices document contains this wording:

• The FILESTREAM API is designed for Win32 streaming access to data. Avoid using Transact-SQL to read or write FILESTREAM binary large objects (BLOBs) that are larger than 2 MB. If you must read or write BLOB data from Transact-SQL, make sure that all BLOB data is consumed before you try to open the FILESTREAM BLOB from Win32. Failure to consume all the Transact-SQL data might cause any successive FILESTREAM open or close operations to fail.
• Avoid Transact-SQL statements that update, append or prepend data to the FILESTREAM BLOB. This causes the BLOB data to be spooled into the tempdb database and then back into a new physical file.

Answer 2

The key difference between T-SQL and Win32 Filestream access is the way data is transferred to the client - using T-SQL to retrieve Filestream data means the storage engine must open the file on NTFS, stream data back through the SQL engine and through TDS (the standard way SQL data is transferred) back to the client. If using Win32 the storage engine is still in the path during the open file operation however once this is complete the data can be transferred directly from the file to the client through Win32 streaming, completely bypassing the SQL engine.

As the blob size increases the overhead of opening the file and transferring through the engine becomes a smaller percentage of the total time required to complete the data transfer. Recent benchmarks for a very specific case study put the thresholds at 60KB for inline (varbinary max storage), 60KB-1.2MB for T-SQL transfer and >1.2MB for Win32 transfer. As I mentioned this was for a very specific case so YMMV.

As far as security goes I can see a number of issues with using SQL security in this way, but it's hard to offer suggestions without a little more context. You're really restricted from benefiting from Filestream by accessing it solely through T-SQL.