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).
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://docs.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}'