Search code examples
iissql-server-2016filetable

File Table (SQL Server 2016) FILESTREAM permissions lost in IIS

This is an example of an image displayed in Chrome through a site, and just rendering a local html file. IIS lost the ability to display the image from the file system provided by SQL File representation and database representation Exception in Process Monitor An image displaying the user with access denied, showing the same user IS the application pool user, and db_owner memebership, and SELECT on FileTable permission

I am trying to store images in a FileTable that is accessible from a website. According to my understanding, the FileTable needs "SELECT" granted to the account running ApplicationPool that the website runs in.

Example of what I have:

  • IIS (6.2)
  • Application pool user : Domain\Bob.
  • SQL Server login Domain\Bob
  • Database has Domain\Bob in db_owner group. I explicitly granted Domain\Bob select on the file table also.
  • DB.dbo.FileStream_FileTable is \\DEV\FileStream_FileTable\
  • IIS has SiteA
  • SiteA has virtual directory Image targeting \\127.0.0.1\DEV\FileStream_FileTable\

If I look at the IIS authentication, IIS errors saying insufficient permission to access web.config - but there is no web.config.

On the server, running Internet Explorer, referencing an image as \\127.0.0.1\DEV\FileStream_FileTable\<...>\Image1.jpg it resolves to the jpg.

On the server, running Internet Explorer, referencing an image as HTTP://127.0.0.1/SiteA/Image/Image1.jpg, IE errors (same error as the authentication error)...

Config Error
Cannot read configuration file due to insufficient permissions

Config File
\?\UNC\127.0.0.1\dev\ItemImage\web.config

There IS no config file.

I have tried specifying the Windows user (Domain\Bob) context when accessing the virtual folder "Images" targetting \127.0.0.1\DEV\Filestream_FileTable and also not specifying the windows credential...

I do not understand why the Image is visible using the UNC path in Internet Explorer (it must authenticate my context as having select on the filestream_filetable table) but not when using the context specifying a Windows context of a user who also has select on the table, in IIS.

NOTE: As an experiment, I modified the user, in IIS, used to access the share to Administrator. Then the IIS site resolved the image.

(Virtual directory targetting the \\servername\sql_instance_filestream_handle\directory\tablename)

The Windows User Domain\Bob has full rights on database - database owner.

There is another user's question, Access to SQL Server FileTable from IIS, that has servername in file share being a potential issue if the IIS and SQL server is on the same host, which I have taken into consideration.

I have added Domain\Bob to IIS_ISURS group...

What is Domain\Bob not a member of that grants access? I can't very well make all my IIS shares accessing with Administrator accounts!

Solution

  • In some case, IIS will try to create web.config if the UNC path just return access denied error. If IIS failed to create web.config, It will also fail to access web.config with 500.19.

    So please ensure your Application pool identity is set to a domain account who have read/write permission to access the folder.

    Then please try to set Anonymous authentication->edit..->Application pool identity.