Sql-server – Need to make sense of FileTable permissions

filestreamfiletablesql serversql-server-2012

I'm setting up a FileTable to allow users to quickly stuff some PDF report files into a database for later searching via an intranet application (to be built later). Sorting out the necessary permissions is a bit of a challenge, and I'm hitting a few roadblocks.

First, I have confirmed that I (with sysadmin privileges) can add and remove files to the share without any issues, so the FileTable is functional at least.

I've added an end user to an AD group, created a Windows login in SQL Server for this group, mapped it to a database user, and granted that user select, insert, update, and delete permissions on the FileTable. The user has logged out of his computer and logged in again to make sure the group I added him to is in the auth token.

He can access the base filestream share for the instance (\\servername\FileStream), but when he tries to go deeper to the database-specific directory (\\servername\FileStream\DatabaseFileStreamDirectoryName), he gets a permission error ("You do not have permission to access…"). Same thing if he tries to access the FileTable path directly (\\servername\FileStream\DatabaseFileStreamDirectoryName\PDFReports).

So, what is the proper way to grant access so that users can browse to the share subdirectory for a specific database, see the directories in there for FileTables, and ultimately browse/access files within the FileTables? It's apparently more than just a user mapping in the database, and select/insert/update/delete permissions on the table, and I'm not finding a whole lot of clear documentation on this so far.

Here's the (slightly anonymized) code I've used for setting this up:

USE master
GO
ALTER DATABASE AppData ADD FILEGROUP FS CONTAINS FILESTREAM
ALTER DATABASE AppData ADD FILE ( NAME = N'FS', FILENAME = N'S:\Filestream\AppData\FS' ) TO FILEGROUP [FS]
ALTER DATABASE AppData SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE AppData SET FILESTREAM( DIRECTORY_NAME = N'AppData' ) WITH NO_WAIT
ALTER DATABASE AppData SET MULTI_USER
GO
USE AppData
GO
CREATE TABLE PDFReports AS FileTable WITH (FileTable_Directory = 'PDFReports')

GRANT SELECT, INSERT, UPDATE, DELETE ON PDFReports TO [MyDomain\App Operators]

Best Answer

I've had to resort to a bit of guesswork, but as far as I can tell, I haven't done anything overly permissive.

I started with a non-privileged test account that's a member of the AD group I created, and I was able to navigate as far as the FileStream directory for the database (\\servername\FileStream\DatabaseFileStreamDirectoryName), but nothing was visible in there.

So, I tried the most obvious approach first:

GRANT VIEW DEFINITION ON PDFReports TO [MyDomain\App Operators]

...Then hit F5 in the VM I was browsing from, and the directory for PDF reports showed right up. I was able to browse in there, and copy some files to the directory.

So I think I'm good, but I'd appreciate anybody piping up if I've done anything bone-headed.

Related Solutions

Sql-server – Finding AD group permissions source

select * from sys.login_token

Somewhere in the output list will be token that grants you access. For instance you can try this (not guaranteed to work):

select * 
from sys.login_token lt
join sys.server_principals sp on sp.sid = lt.sid;

Sql-server – In Search of FILESTREAM Insider Information

When the FILESTREAM feature is activated on Microsoft SQL Server 2012 then SQL Server will create a "hidden" share on the system.

It does not do this by default, you have to CHOOSE to enable the share. This is done via SQL Server Configuration Manager. If you deselect the Enable FILESTREAM for file I/O access the share will be removed.

It's nice to know that SQL Server has everything nice and tied up, but what does that share actually do?

The share allows for clients (local and remote) to have a singular shared location to use the streaming windows api for access to filestream data. This works in conjunction with the SQL Server Instance level settings for filestream access of Full Access Enabled, any other access setting should not work with the streaming API.

... Is it the so called "file system filter driver"?

~~No, it is not. This is just a file share.~~

I was trying not to muddy the waters but you did ask for as much information as possible. In the above strikethrough text I did, in fact, say that this was not the filter driver. However that technically is a half truth. Yes, it is a shared folder but it actually shares through the filter driver. I really debated about this because it starts becoming a rabbit hole that you really can't go down without the source code (and to be honest it's of little value other than academic in my opinion).

The whole point of the filter driver is to do a few things, but the one of those things is to give transactional access to the data stored in the filestream target via a variety of interfaces; SQL Server, Transact SQL, Windows APIs. It also does a handful of other items - however the access given through the share is done via the filter driver. In fact, if you attempt to access files in a filestream and are not an administrator or SQL Server you shouldn't be able to access them.

So, yes this both is and is not the filter driver. It's half a windows fileshare that is exposed through a filter driver. You can see this is you view the path property of the share.

get-wmiobject -class Win32_share | where {$_.Description -like 'SQL Server*'} | ft name, path -autosize

2.Seeing as any authenticated user can access the "share", what are the security implications?

You can change the permissions and requires the settings to be properly set. The security implications are that of any other file share.

3.Is the Device RsFx0320 a predecessor to the resilient file system format that was introduced with Windows Server 2012?

No, this is the name of a specific version of the filter driver. For example, here is a system with the 2016 one loaded RsFx0410. ReFS is a file system, this is a filter driver that sits between the filesystem and the miniport driver. It's actually quite disconcerting that this is a legacy filter driver as denoted by the .10 at the end of the altitude... hmm. You'll also notice it has quite a low altitude, which is generally not acceptable for 3rd party filter drivers.

If you can supply answers to my questions, then it would be nice if you could provide a source reference.

I have no sources for this but have backed up my information through screenshots and configuration options that change settings. Everything in this answer can be found by looking through the product itself and knowing how pieces of windows work (ex: filter drivers).

Best Answer

Related Solutions

Sql-server – Finding AD group permissions source

Sql-server – In Search of FILESTREAM Insider Information

Related Question