I have an ASP.NET MVC application and a SqlServer 2014 database. The database has a table of type FileTable. I can access the files stored in the table from Windows Explorer in this way:

\\sql-server-name\mssqlserver\MyData\MyFiles

I would like to access these files from the application (for example, to place links to files in the src attributes of the img , video and audio elements). I got a virtual directory for this (let's call it OuterFiles) in my web application in IIS. The directory refers to the directory where the contents of the FileTable is stored. After that, I tried to use this virtual directory in my application. Like that:

 <img src='/OuterFiles/picture.png' />

but the image does not load. Instead, I get an HTTP error 500.19:

Configuration Error:
Failed to read configuration file due to lack of required permissions.
Configuration file:
\\? \ UNC \ sql-server-name \ mssqlserver \ MyData \ MyFiles \ web.config

For the sake of experiment, I created another virtual directory (let's call it AnotherOuterFiles) that refers to a regular folder (for example, C:\SomeFolder ), which also contains images, and wrote the following markup:

 <img src='/AnotherOuterFiles/picture2.png' />

and it worked.

Obviously, Windows (or SqlServer) does not give IIS permissions to access the folder storing the contents of the FileTable, and because of this I get an error 500.19.

Can I somehow give this access, and if so, how?

PS

  1. I tried to access the properties of this folder in order to give access to it to certain users, but in the folder properties there is simply no Security tab where you can assign it.
  2. I went to SqlServer Configuration Manager and set the Allow remote client access to FILESTREAM data flag there, unfortunately it did not help
  3. I created an alias for this folder using the command line mklink command, and already set up a virtual directory for this alias, but the problem remained
  • @ i-one in the properties of the instance is Full access enabled, in the properties of the database Non-Transacted Access is set to Full - Pupkin
  • @ i-one in this case they are on the same machine. allow remote clients has been enabled just in case. And how to see from which account the pool is running? - Pupkin
  • @ i-one is possible, in the settings of the pool there is a property Identity. Its value ApplicationPoolIdentity - Pupkin
  • @ i-one login sql server - Pupkin
  • @ i-one thanks for the comments, I will be happy to answer) - Pupkin

1 answer 1

To read files uploaded to FileTable, accessing them via a UNC directory, you need to give the user (the one on whose behalf the reading is made) access to the file table.

This access, however, is not regulated through the Windows ACL, specified, in particular, in the directory properties in the Security tab, but by means of SqlServer.

For example, if an ASP.NET application is running in IIS in a pool with the name PoolName, then the account of this pool is identified as IIS APPPOOL\PoolName .

Accordingly, for the pool account in the SqlServer instance, you need to create a Windows login:

 USE [master]; GO CREATE LOGIN [IIS APPPOOL\PoolName] FROM WINDOWS; GO

In the database for this login, you need to create a database user and give him the right to read the file table:

 USE [DatabaseName]; GO CREATE USER [IIS APPPOOL\PoolName] FOR LOGIN [IIS APPPOOL\PoolName]; GO GRANT SELECT ON OBJECT::[dbo].[MyFiles] TO [IIS APPPOOL\PoolName]; GO

This is for the case if IIS and SqlServer are installed on the same machine (in this case, by the way, the inclusion of the Allow remote access flag to FILESTREAM data in the configurator is not required).

If IIS and SqlServer are on different machines in the domain, then the ASP.NET application can be placed in a pool running on behalf of a domain user, specifying the corresponding DOMAIN\UserName in the scripts above instead of IIS APPPOOL\PoolName .

Now an ASP.NET application will be able to read files located in the SqlServer file table, accessing them via the UNC path. For example:

 using (var fs = File.OpenRead(@"\\sql-server-name\mssqlserver\MyData\MyFiles\picture.png")) { ... }

As for the mapping of FileTable to the IIS virtual directory, this is a problem. And she's next.

Even if, as stated above, the corresponding application pool in IIS is given access to the FileTable, IIS still does not give the file from the virtual directory associated with the file table, while returning an error 500.19 .

Analysis of ProcessMonitor-ohm shows that if access is configured, then IIS Worker Process is quite capable of reading files from a file table. In particular, if you put a file named web.config in the file table, ProcessMonitor shows a successful read. Further, IIS may return an error about the inability to read web.config due to lack of rights, but this is not the true reason.

The real reason seems to be that IIS is trying to set up tracking on the FileTable directory and this stumbles:

 Operation: NotifyChangeDirectory Result: NOT SUPPORTED

because file tables do not support this functionality (see here , in the section File System Functionality Supported in FileTables , Directory change notifications: No ).

If IIS did not do this, the files from the virtual directory would most likely have been perfectly rendered.

Tracking directories in IIS may be disabled (see KB911272 ). For .NET Framework applications, starting from version 4.5, this can be done via web.config

 <httpRuntime fcnMode="Disabled"/>

however, it seems that this setting affects the application, but not the virtual directories.

There is also a global setting, which, it seems, should also affect virtual directories (I did not check this option).

In the registry branch

 HKLM\Software\Microsoft\ASP.NET

or (for 32-bit applications on x64 system)

 HKLM\SOFTWARE\Wow6432Node\Microsoft\ASP.NET

You need to create a FCNMode parameter of type DWORD and set its value to 1 (you may need to restart the system). However, this will affect all applications of all sites operating in IIS, which may be undesirable.

If the global configuration does not solve the problem (or it is not an appropriate option, because there is still a benefit in tracking), then an alternative way is to create a simple controller that would give the files. Something like (simplistic) such:

 public class ImagesController : Controller { [HttpGet] [OutputCache(Duration = 1800, Location = OutputCacheLocation.Any, VaryByParam = "fileName")] [Route("/images/{fileName}")] public ActionResult GetImage(string fileName) { string ext = Path.GetExtension(fileName).ToLower(); string dirName = @"\\sql-server-name\mssqlserver\MyData\MyFiles"; string filePath = Path.Combine(dirName, fileName); string contentType = null; switch (ext) { case ".jpeg": case ".jpg": contentType = "image/jpeg"; break; case ".png": contentType = "image/png"; break; ... default: contentType = "application/octet-stream"; break; } return File(filePath, contentType); } }
  • one
    Amendments. Instead of {fileName} it would be worth taking {*filePath} , and the list of mime-types would be better not to hard-code, but pull it out of the config ... - Pavel Mayorov
  • @PavelMayorov, I understand correctly * - is it to give files from nested directories too? Here, in addition, probably, Server.MapPath() will work if the alias was configured with mklink. - i-one
  • Yes, this is for nested directories. And about the config, I said garbage, pull out a list of mime types from there - it will be a bunch of code, more than the size of a switch operator :) - Pavel Mayorov
  • @PavelMayorov, and I like the idea of ​​pulling mime-types from the config, this is convenient. You can at the start of the application to download pairs in the Dictionary, and then just refer to it. Let, however, the refinement and determination of expediency be left to the discretion of the applicator. - i-one
  • @ i-one thank you very much, I will try! - Pupkin