0

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!

Alocyte
  • 293
  • 4
  • 13

2 Answers2

1

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.

Jokies Ding
  • 3,374
  • 1
  • 5
  • 10
  • Thanks for your response @jokies. I have made the application pool user member of db_owner and SQL is presenting the share, therefore controls access? I have set the site virtual folder accessing the share to use the domain account, in advanced settings. – Alocyte Jun 02 '20 at 07:10
  • @Alocyte Have you set both NTFS permission and network share access for your domain account? – Jokies Ding Jun 02 '20 at 07:20
  • In computer management/ shared folders/ shares I have explicitly granted Domain\Bob full permission on the share. The SQL managed share, stored on the file system as F:\Catalyst$DEV\images\content\cc9f5495-bb4e-4c6d-a190-7ef66e6c44e5 - this I have also granted Domain\Bob read access. I know this is the folder because the file count matches my row count in the FileTable. – Alocyte Jun 02 '20 at 08:30
  • Please try process monitor and add a filter for "result=access denied". It should help you fix this.https://learn.microsoft.com/en-us/sysinternals/downloads/procmon – Jokies Ding Jun 02 '20 at 09:02
  • I amended my question to include the result of the process monitor. Again, this is the Access Denied issue - but the application pool account, the database login, and file system level access for that "user account" is allowing read. The same result occurs after I grant Domain\Bob FULL rights on the folder SQL presents as the share. – Alocyte Jun 02 '20 at 09:21
  • @Alocyte.Please double click the line "createfile". It should show the real user. – Jokies Ding Jun 02 '20 at 09:37
  • Jokies - i amended my question to include the user the Process Monitor shows is getting Access Denied - it is Domain\Bob My image includes the application pool identity (Domain\Bob) and Domain\Bob permission on the database. – Alocyte Jun 02 '20 at 11:34
0

The Windows user Domain\Bob was not a login in the DEV sql instance. Creating a sql login solved it. blush

Database level had the user, yes, but the SQL server didn't have a login.

facepalm

Community
  • 1
  • 1
Alocyte
  • 293
  • 4
  • 13
  • It is appreicated if you could accept your reply as answer. Thanks! – Jokies Ding Jun 04 '20 at 08:07
  • So please ensure your Application pool identity is set to a domain account who have read/write permission to access the folder. This, your original statement - is the answer. Thanks @JokiesDing – Alocyte Jun 05 '20 at 09:10