In Search of FILESTREAM Insider Information

Question

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

Sharename          FILESTREAM_SHARE
Path               \\?\GLOBALROOT\Device\RsFx0320\<localmachine>\FILESTREAM_SHARE
Remark             SQL Server FILESTREAM share
Maximum users      unlimited
Users Caching      Manual caching of documents 
Permissions        NT-AUTHORITY\Authenticated Users, FULL

The name is the name of the share you provide when initially configuring FILESTREAM in the SQL Server Configuration Manager. But what is it for?

So far

I read through all the available FILESTREAM documentation starting at:

• FILESTREAM (SQL Server)
• Enable and Configure FILESTREAM
• Create a FILESTREAM-Enabled Database
• Create a Table for Storing FILESTREAM Data
• ... and everything related
• FILESTREAM Compatibility with Other SQL Server Features
• FILESTREAM DDL, Functions, Stored Procedures, and Views
• Access FILESTREAM Data with OpenSqlFilestream
• the paper SQL Server 2008 - FILESTREAM Storage in SQL Server 2008
• and the article FILESTREAM (OLTP)---a Technical Reference Guide for Designing Mission-Critical OLTP Solutions which references a lot of other material

...but there was no mention of the share and what it does or what it is for. You enter the name and SQL Server will create the share under-the-hood.

FILESTREAM-enabled database

When you create a FILESTREAM-enabled database, the database references a filegroup which references a directory (recommended on a separate drive) that has absolutely nothing to do with the share, which was initially created during FILESTREAM configuration.

Screenshot FILESTREAM-enabled database script

Yes, I do realise that all paths are on C:; it is just an example

The white paper by Paul Randall et al. goes on to explaing that...

FILESTREAM data is stored in the file system in a set of NTFS directories called data containers, which correspond to special filegroups in the database. Transactional access to the FILESTREAM data is controlled by SQL Server and a file system filter driver that is installed as part of enabling FILESTREAM at the Windows level. The use of a file system filter driver also allows remote access to the FILESTREAM data through a UNC path. SQL Server maintains a link of sorts from table rows to the FILESTREAM files associated with them. This means that deleting or renaming any FILESTREAM files directly through the file system will result in database corruption.

... further down the document (page 14) they carry on with ...

There is a single FILESTREAM file system filter driver for each NTFS volume that has a FILESTREAM data container, and there is also one for each version of SQL Server that has a FILESTREAM data container on the volume. Each filter driver is responsible for managing all FILESTREAM data containers for that volume, for all instances that use a particular version of SQL Server.

For example, an NTFS volume that is hosting three FILESTREAM data containers, one for each of three SQL Server 2008 instances, will have only one SQL Server 2008 FILESTREAM file system filter driver.

Questions

1. It's nice to know that SQL Server has everything nice and tied up, but what does that share actually do? Is it the so called "file system filter driver"?
2. Seeing as any authenticated user can access the "share", what are the security implications?
3. Is the Device RsFx0320 a predecessor to the resilient file system format that was introduced with Windows Server 2012?

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

Answer 1

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.

1. 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.

1. ... 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).

Answer 2

Here's my take on your questions:

1.It's nice to know that SQL Server has everything nice and tied up, but what does that share actually do? Is it the so called "file system filter driver"?

SQL Server File stream access is all about, well, access to files. The share provides that location via a file share.

You can easily see this with some C# code using OpenSqlFilestream on

https://learn.microsoft.com/en-us/sql/relational-databases/blob/access-filestream-data-with-opensqlfilestream

As you can see, no FILE_SHARE_READ for CreateFile and there's no file magic, no filestream:

try
    {
        if ( (srcHandle = CreateFile(
            srcFilePath,
            GENERIC_READ,
            FILE_SHARE_READ,
            NULL,
            OPEN_EXISTING,
            FILE_FLAG_SEQUENTIAL_SCAN,
            NULL)) == INVALID_HANDLE_VALUE )
            throw szErrMsgSrc;

Note: As to why there's no local server/shareless FILESTREAM option--I have no clue. Sounds like some folks might like that extra level of security.

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

Wow, great question. It appears, that the share permissions are available to any authenticated user, but, underlying NTFS permissions should fine tune the security. But, this does seem like a security hole to me. I'd have to investigate further how fine tuned the NTFS permissions are but I'm definitely not liking the generalized file share permissions. Come on Microsoft, ALL "authenticated users"? Right or wrong, I think Microsoft tends to ignore the share/focus on the NTFS permissions.

Supplying "authenticated users" has been a debated topic for years. Here's one of the best blogs I've read on the back and forth of "authenticated users". It's so heavily entrenched in Windows I don't think they'd ever say it's a legitimate risk--until their new product comes out that gets rid of it.

https://social.technet.microsoft.com/Forums/windowsserver/en-US/bb74fa7c-89bd-476d-88bf-e88cd66618e6/why-is-authenticated-users-in-the-local-users-group-by-default?forum=winserversecurity

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

Looks to be SQL based rather than O/S based. RsFx0320.sys is SQL 2008. As seen below SQL 2012 is RsFx0201.sys and SQL 2014 is RsFx0300.sys:

https://support.microsoft.com/en-us/help/2961258/fix-cannot-access-the-data-in-filetable-after-you-upgrade-from-sql-ser

Microsoft shows what can happen when you upgrade from 2012 to 2014:

To work around this issue, change manually the path property of the resource <AvailabilityGroupName>_FSShare to point to the correct driver: From
'\\?\GLOBALROOT\Device\RsFx0201\<localmachine>\{AvailabilityGroupID}' to '\\?\GLOBALROOT\Device\RsFx0300\<localmachine>\{AvailabilityGroupID}'