How to allow AppPoolService access to your SQL Server & database?

Question

I have an IIS 7 server(2008 ) and ms sql server 2005 on the same machine. I want my application pool that was created to be able to access ms sql server 2005.

How can I do this

1. Set your app pool to a account (I think it is done - in the application pool I have created a new pool).
2. Give that account, access to you SQL Server & database

Answer 1

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