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?

Avigation

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.

Avigation

Hello Paul. It's been some time since you've provided this answer, but do you know if this works when using a partially contained database??? – Keil

Not sure what you mean by partially contained database. – Avigation

I'm talking about this learn.microsoft.com/en-us/sql/relational-databases/databases/… – Keil

I've not tried that scenario. I was using SqlServer 2012 at the time. I think it will depend on where you are doing your user authentication, in the Database or the Instance of Sql Server. Let me know how you get on? – Avigation

Well, in order to save files through the File API (using the file share managed by sql server), I had to create a global login mapped into a db user. When I have time, I'll try using the OpenSqlFilestream with a local user to see if it works... – Keil

Answer 2

The accpted answer did not work for me. The answer in this link did.

My IIS server was not on the same server as the sql database, so I had to make sure that the application pool user for IIS existed as a windows user on the sql server, that they had the same password and that there is a sql windows user for the account.

Tortious

Answer 3

I had the same issue. The following answer works for me.

1. Create a new account on both servers with the same password.
2. Configure that account to run your IIS app
3. Create a login in SQL Server for that account with the appropriate rights.

Binford