So this blog post has a long title, apologies for that. This is an important FileTable issue that you must be aware of before implementing this feature.
Now that SQL Server 2012 is officially released, people are looking to deploy it and take advantage of the many new and improved features. While HADRON is the big news in SQL 2012, FileTables are a great addition to the non-relational capabilities of SQL Server. I have already written some and I will write more about FileTables, but at this point, I need to share the results of some research I did while writing the FileTable chapter in an upcoming book on FILESTREAM. (FileTable is squarely built on FILESTREAM.)
There is a significant issue (a bug based on my analysis, but not yet confirmed by Microsoft) that makes it impossible to provide users with read-only access to the FileTable data. This issue is also discussed on Connect: http://connect.microsoft.com/SQLServer/feedback/details/721349/cannot-prevent-users-with-select-permission-on-filetable-enabled-for-non-transactional-access-to-make-modifications-to-filestream-data. Microsoft closed the issue as “Resolved as By Design” about 2 months ago with a note that it is being researched and will be addressed in an upcoming service release.
This effectively means that if you have a group of users in your environment who should only be able to read the FileTable data, but you have other users who need the modify the FileTable data, you are not able to set this up. Any login or role (i.e. user or group, as FileTable only works with Windows authentication) granted SELECT permission on the FileTable will be able to modify the FILESTREAM data stored on the file share.
The following conditions must be met:
- You have created a FileTable.
- You have enabled full non-transactional access to the FileTable. (Presumably this will be the most common scenario, as FileTables without non-transactional access enabled don’t add much value over FILESTREAM.)
- If you have enabled non-transactional access at the Read_Only level only, then SQL Server will work as expected and only provide read-only access – but that applies to all users and groups.
- You have created a SQL Server login and user mapping for a user or group that does not have Update permission on the FileTable.
- This could be because the user has only been granted SELECT permission.
- It could also be because you have explicitly denied UPDATE permission to that user.
When these conditions are met, the users who only have SELECT permission on the FileTable will be able to open files (using Notepad let’s say) and successfully save those files. They are not able to create new files (the equivalent of inserting a new row in the FileTable) or delete files (the equivalent of deleting a row from the FileTable).
At this time, I am unable to suggest a workaround. So while FileTable security may already be a deal breaker in some environments (details in the book!), this issue creates additional concerns around the security mechanism of FileTable. If your access rights call for all users with any permission to the table to be able to update any FILESTREAM data, then FileTable may work for you “as is.” However, if some of your users should only have read-only permission while others require read/write, FileTable will not work for you at this time.
Sven Aelterman 
Interesting report. I had tabled using FILESTREAM in a SQL 2008 project (because that installation used a NAS for files, so it was completely out) and just decided to dive into 2012 with a recent rollout (shh, don’t tell the client 🙂 and wondered what else FileTables had to offer since I’m using local DASD for this one.
Do you have the same bug if you have, say, stored proc access but not direct SELECT access to a table?
Hi SW,
Thanks for reading.
The issue I am referring to in this blog post is specific to accessing FileTables using non-transactional Win32 API access, i.e. treating the FileTable as if it were just another Windows share. My understanding is that if you are using T-SQL access or transactional streaming access (using the .NET SqlFileStream class for example) the permissions will be honored.
Hope this helps,
Sven Aelterman.
But when you log in to the special UNC, aren’t your current Windows credentials still being honored for your SQL Server login? It sounds like they are not completely discarded (because SQL Server is still limiting the corresponding user’s access to create new files via UNC). So I was wondering if perhaps having harder lockdown of the user’s grants would prevent them from messing with files via UNC.
Of course, I would expect the harder lockdown to also prevent them doing anything outside of T-SQL, but perhaps that would be a way of having non-transactional read/write access to some users while having no non-transactional access for others.
I haven’t tested it with FileTable, but with FILESTREAM (which FileTables are based on of course), for a user to have access to the VARBINARY(MAX) field using streaming APIs, you need at least SELECT permission on the table.
So, if I understand your possible workaround correctly, you want to remove all permissions the user or group has to the FileTable and attempt to use stored procs? In which case you likely will not be able to use non-transactional access and you’re back to square one where you can’t open files using the traditional Win32 I/O APIs.
My thought was that you would still be able to allow privileged users to read/write files using Win32 I/O, while less privileged users would not be able to escape their limited permissions by connecting to the files directly (they would have to be gatewayed through an app that uses other APIs). This would at least prevent you from implicitly letting all your files be clobbered by unprivileged users (as I think your bug report is a major reason that companies allowing direct access to SQL server, without a middle tier, shouldn’t be using FileTables/FILESTREAM).
SW,
I understand now what you mean, and yes, that might indeed work, although as you stated, it’s functionally not equivalent.
Microsoft has prepared a fix for this issue which will be released “soon.” The best I can make of that is that it will be in a forthcoming Cumulative Update (CU 1 is out already at this time and the fix isn’t included).
Glad you got my meaning now. I’ll check back on your blog to see where things stand (as I don’t think I’ll be on top of these fixes without people like you).