Access Denied when inserting file into Sql Server 2012 FileTable using File.CreateFile in ASP.NET website

Question

I have an ASP.NET website. I want to upload a file to the website and save it into a FileTable in SqlServer 2012.

Once uploaded, my website code has a Stream object which I would like to save using

System.IO.File.Create()

The FileTable is accessible in Windows Explorer when I go to

\\ComputerName\FileStremShareName\FileStreamDirectoryName\FileTableName

I can manually create files in this folder using Windows Explorer. I can also use SQL to insert files into the File table. In both cases, Windows Explorer and SQL Server show the files as expected.

However, If I try and use System.IO.File.CreateFile to create files in the path, E.g.

File.Create(\\\\ComputerName\FileStremShareName\FileStreamDirectoryName\FileTableName\myfile.jpg)

I get the message "Access to path '[path]' is denied"

I assume this is because my IIS Application Pool Identity does not have access to read/write to this location.

Normally, I would go into NTFS permissions and assign Read/Write access to the Identity account but I am unable to do this because the underlying folder is hidden from me.

I have considered converting my Stream object into a byte array and using SQL to insert it into the FileTable but this seems inefficient because I have to convert the stream into a byte array first for it to then be passed to SQL. This seems like I am parsing my file data twice to save it once.

Is it possible to write to the File Table using File.Create or similar within an ASP.NET website. If so, how do I do this?

Answer 1

This is a permissions problem. However, the permissions are not granted through NTFS but through SQL Server.

The Application Pool Identity does not have any permissions on your database by default so this has to be changed.

1. Add a Login to SQL Server for the Application Pool Identity you are using for your website. E.g. "IIS APPPool\MyAppPool"

   USE [master]
   GO
   CREATE LOGIN [IIS APPPOOL\myapppoolname] FROM WINDOWS WITH DEFAULT_DATABASE=[MyDatabase]
   GO
   

2. Add a User to your database that this login will use

   USE [MyDatabase]
   CREATE USER [MyUserName] FOR LOGIN [IIS APPPool\myapppoolname]
   

3. Grant the user relevant permissions on your database

   use [MyDatabase]
   GRANT INSERT TO [MyUserName]
   GRANT SELECT TO [MyUserName]
   GRANT UPDATE TO [MyUserName]
   

I'm not sure if this is the complete set of permissions required but I found it was sufficient for me to be able to save a new file.