Configure Integrated Security Using IIS Application Pool Accounts for SQL Server 2008

Question

I have the following configuration for the website:

• Two web interfaces (e.g. machine names: WFE1 and WFE2) 1 SQ
• One SQL Server database cluster (for example, machine name: DBCluster)

All machines are in the same domain (for example, MyDomain) running Windows 2008 R2 Enterprise and SQL Server 2008 R2.

I am deploying a web application using an application pool identifier. I named the pool MyWebApp, which translates to the name, [IIS APPPOOL \ MyWebApp]. When I try to add this user to SQL Server, I get the error message:

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

script I used to create an account in SQL Server:

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

I assume the problem occurs because the IIS account is a local account that does not appear in the SQL Server field.

Can someone shed light on how this problem can be solved? Is using a domain account my only option or can I get application pool accounts?

Answer 1

I assume the problem occurs because the IIS account is a local account that does not appear in the SQL Server field.

This is definitely a problem. The IIS AppPool account exists only on the web server. If you could add this account to SQL Server, you would enable IIS AppPool running on the same computer as SQL Server. (I suspect that he could still fail.)

The safest solution would probably be to do as you say - create an account in the domain, grant this account the appropriate permissions for the database, and start AppPool using the credentials of this account.

However, if you still want to do this, you need to authorize the computer running AppPool, i.e. DOMAINNAME\ComputerName$ (note the $ at the end).

See this article for more information (in particular, the "Network Access" section).

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