3

We have a SQL Server 2012 installation with the file share feature enabled. We then have an IIS application that can write directly to the \localhost\MSSQLServer... share that the database creates.

The problem I have is that to get this to work, I have had to create a new user account and set that as the app pool identity for the application and then add this user to SQL Server and finally give that user the sysadmin role. If I give it any other server role, it doesn't get write access to the share.

Having an IIS app pool user account have unrestricted access to the entire SQL Server isn't ideal. Is there a way therefore to configure SQL Server to allow a lower-privilege role access to the file share?

David Arno
  • 81
  • 5

1 Answers1

1

In general you should be able to grant that website's app-pool user Insert/Delete/Update/Select rights on the single filetable (and what ever other parts of the DB it might need access to.

Granting their login "sysadmin" is way too far to go. When debugging in test you can try to grant the user for that login the "db_datawriter" & "db_datareader" roles or even the "db_owner" role on the database that holds the filetable

But you should be able to do something like 

USE [DatabaseName]
GRANT SELECT, DELETE, UPDATE, INSERT on TABLE [FiletableName] TO [DOMAIN/UserName]

or create a role for the application that has all of the GRANTs applied to it and then grant that new role to your user.

The FileTable table type doesn't use a special rights model, it uses normal SQL rights mechanisms.

Finally, be sure you are using SQL2012 Service Pack 1, as there is a known security bug in the RTM version. "Security Flaw Fixed in SP1"

Mark
  • 2,248
  • 12
  • 15
  • The application copies the files directly into the share, then updates the file table. Are you saying that if I grant database level db_datawriter rights to the user, then that user should be able to access the share directly? That doesn't sound right as I thought the share was managed at the SQLServer, not database, level. – David Arno Jun 25 '13 at 12:58
  • David, I'm missing something in translation here. The SQL2012 filetable feature creates a virtual share. The "table" record resides in a particular database. At the SQLServer level there are "logins", at the database there are "users". A user in a DB is connected to a single login (and often share the exact same name). If the login is an AD account, then SQL asks AD to authenticate the credentials it is given. Once the login connects, permissions to DB resources are normally granted at the database level, to the user record. you shouldn't need to grant sysadmin access at the login level. – Mark Jul 08 '13 at 18:29
  • I get incorrect syntax near 'TO' when I run this GRANT query – Rhyous Oct 26 '16 at 14:53