How to set up a SQL file share that doesn’t need sysadmin access to write to it

iis-7.5sql-server-2012

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?

Best Answer

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"

Related Solutions

IIS7.5 Domain Account Application Pool Identity for SQL Server Authentication

Yes, the domain account will be added under Custom account: under Advanced Settings -> Identity. Below information is from Understanding the Built-In User and Group Accounts in IIS 7.0

IIS 7.0 automatically adds the IIS_IUSRS membership to the worker processes token at runtime. By doing this, accounts that have been defined to run as 'application pool identities' no longer need to explicitly be part of the IIS_IUSRS group.

If you want to disable this feature and manually add accounts to the IIS_IUSRS group, disable this new feature by setting the manualGroupMembership value to 'true'. Below is an example of how this can be done to the defaultAppPool:

<applicationPools>
    <add name="DefaultAppPool">
       <processModel manualGroupMembership="true" />
    </add>
</applicationPools >

Sql-server – How to allow AppPoolService access to your SQL Server & database

Yes this should work, depending on your requirements, you could use the "NetworkService" account; but this may not be best practice in your environment.

Here is the TSQL to implement (Replace AdventureWorks with your Database name)

USE [AdventureWorks]
GO
CREATE USER [NT AUTHORITY\NETWORK SERVICE] FOR LOGIN [NT AUTHORITY\NETWORK SERVICE]
GO
USE [AdventureWorks]
GO
EXEC sp_addrolemember N'db_datawriter', N'NT AUTHORITY\NETWORK SERVICE'
GO
USE [AdventureWorks]
GO
EXEC sp_addrolemember N'db_datareader', N'NT AUTHORITY\NETWORK SERVICE'
GO

Note this will give the account read and write permission; but no permission to execute sprocs , I would recommend creating a Database role and granting the execute right to all sprocs then assigning the database user to the role.

More info: http://vyaskn.tripod.com/sql_server_security_best_practices.htm

Best Answer

Related Solutions

IIS7.5 Domain Account Application Pool Identity for SQL Server Authentication

Sql-server – How to allow AppPoolService access to your SQL Server & database

Related Topic