IIS 7 has a nifty new feature whereby you can set the identity of the application pool to ApplicationPoolIdentity. It then creates a virtual user with the same name as the name of the application pool, i.e. if you named your application pool Bozo, it will create a virtual user called Bozo, to which you can assign rights.
To allow the virtual user access to SQL Server, do the following:
- In SQL Server Management Studio, open the Security folder, right-click the Logins folder, and click New Login...
- Enter IIS AppPool\{apppoolname}, replacing {apppoolname} with the name of your application pool - DO NOT USE THE SEARCH FUNCTIONALITY
- Assign the necessary database level privledges and click Ok
This has been tested on SQL Server 2005, I would imagine it works in SQL2008 too.
