SQL Server's FileTable and ASP.NET: permissions for user

Question

I'm building an app which needs to save files to filetable through the file share. I've already managed to get it working, but I'm curious about the solution required.

In my case, I'm using a partially contained database (which has local users) and I thought that I could simply create a new user based in the existing ASP.NET identity and grant some permissions to it.

My first attempt looked something like this:

use [clip]
go
create user [IIS APPPool\Test]

GRANT INSERT on object::ImagensEditor TO [IIS APPPOOL\Test] 
GRANT SELECT on object::ImagensEditor TO [IIS APPPOOL\Test] 
GRANT UPDATE on object::ImagensEditor TO [IIS APPPOOL\Test] 

Unfortunately, this didn't work and I kept getting the "famous" access denied error (UnauthorizedAccessException: Access to [path] is denied). The only way I've managed to get things working was by creating a global SQL Server login for my site's app pool. IN other words, I had to replace the create user with something that looked like this:

use master
go

CREATE login [IIS APPPOOL\Test] from windows with default_database=[clip]
GO

use [clip]
CREATE user [IIS APPPOOL\clipfrontoffice] for login [IIS APPPOOL\Test]
go

And then everything started working (no more access denied errors).

Now, I haven't found any docs mentioning that I can't use a contained user based on a windows account/identity for this type of scenario.

So, did I do anything wrong? Or in this case, we do really need to create a global login?

Thanks

Luis

Embowel

Can you post here the exact text of ""famous" access denied error" please? Is it OS error rather than SQL Server error? Server error usually state "the SELECT/INSERT/UPDATE permission is denied" so I suppose you've got an OS error – Alviani

I assume you mean "access denied" when saving the file through the .NET app and not an error related to SQL Server? – Monda

Hello guys. Access denied when I try to use the file API to save the file in the SQL Server FileTable's share... – Embowel

I think create user [IIS APPPool\Test] creates user, but do not associate it with login – Cynthiacynthie

Hello Mikhail. Yes, option 1 was trying to create a user for a contained database (in this case, I should be able to just create a user without the login)... – Embowel

Answer 1

This is expected functionality as you need to use Integrated Security to use filestreams (and therefore need a windows login with permissions to be able to write to the underlying filesystem).

You can't use a user created in option 1 as that only creates a SQL User which windows cannot use to access and write to the filesystem. By creating the global SQL user and a login associated with it, the app pool can login to SQL Server as the windows login and write to the filesystem correctly.

Cheddar

Yep, if you think that the shared folder will start at instance level, it makes sense...though the docs could probably be updated to make this clear... – Embowel