Configuring Integrated Security using IIS Application Pool Identity accounts for SQL Server 2008

Question

I have the following configuration for a web site:

• Two Web front-ends (e.g. machine names: WFE1 & WFE2) 1 SQ
• One SQL Server Database cluster (e.g. machine name: DBCluster)

All machines are in the same domain (e.g. MyDomain) running Windows 2008 R2 Enterprise and SQL Server 2008 R2.

I am in the process of deploying a web application that uses the Application Pool Identity. I have named the pool MyWebApp, which translates to the name, [IIS APPPOOL\MyWebApp]. When I try adding this user to SQL Server I get an error:

Windows NT user or group 'IIS APPPOOL\MyWebApp' not found. Check the name again.

The script that I have used to create the account in SQL Server is:

CREATE LOGIN [IIS APPPOOL\MyWebApp] FROM WINDOWS WITH DEFAULT_DATABASE=[MyDatabase], DEFAULT_LANGUAGE=[us_english] GO

I imagine the problem is occuring because the IIS account is a local account which is not visible to the SQL Server box.

Can someone please shed some light on how this problem can be resolved? Is using a domain account my only option or can I still get the application pool accounts to work?

Answer 1

I imagine the problem is occuring because the IIS account is a local account which is not visible to the SQL Server box.

That's exactly the problem. The IIS AppPool account only exists on the web server. If you were able to add this account to SQL Server, you would be authorizing an IIS AppPool that is running on the same machine as SQL Server. (I suspect it might still fail.)

The most secure solution would probably be to do as you say - create an account on the domain, give that account appropriate permissions on the database, and run the AppPool using that account's credentials.

However, if you'd still like to do it, you need to authorize the computer that the AppPool is running on - ie, DOMAINNAME\ComputerName$ (note the $ at the end).

Take a look at this article for more information (specifically, the section titled Accessing the Network).

I think that's a bad idea, however, because it authorizes any program running as NetworkService to access the database - not just your web applications.