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
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.