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

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
\ComputerNameFileStremShareNameFileStreamDirectoryNameFileTableName

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(\\ComputerNameFileStremShareNameFileStreamDirectoryNameFileTableNamemyfile.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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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 APPPoolMyAppPool”
    USE [master]
    GO
    CREATE LOGIN [IIS APPPOOLmyapppoolname] 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 APPPoolmyapppoolname]
    
  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.

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

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


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x