How do I configure SQL Server to allow access via IIS

Question

I have a web service that stores data in a local SQL Server 2008 database. If I run the web service under my account the web service can successfully access the database. However, if I use the DefaultAppPool (IUSR) account then accessing the database from the web service fails.

How do I set security on SQL Server to allow access to a specific database via IIS?

The specific error message I am getting is: Login failed for user 'IIS APPPOOL\DefaultAppPool'

Answer 1

You have two options (obvious maybe!):

• Instead of using Windows Integrated Security use SQL Authentication instead.
• If you can't or don't want to, then you have to create a new user in SQL Server that relates to that Windows account.
• Or (third option) you can change the web service to run under an account that you know works.

Answer 2

I generally run the app pool under a domain user account, that way you control the specific user for each site on your server.

If I can't use a domain account, I'll run the site as "Network Service" - and the user that would correspond to that in SQL would be the machine account (MACHINENAME$ - replace "machinename" with your IIS server name").

If you plan to use the new IIS7 IIS users - which are not windows users - you'll have to use SQL Authentication instead of Windows authentication for your SQL database access.