where can I find the user in this IIS error 'Login failed for user 'IIS APPOOL\Web2'

Question

I encounter the following error:

Cannot open database "testbase" requested by the login. The login failed.
Login failed for user 'IIS APPPOOL\Web2'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot open database "testbase" requested by the login. The login failed.
Login failed for user 'IIS APPPOOL\Web2'.

So, where can I give this user Web2 permission? (By the way, the server do not have such user Web2 but there is a folder called Web2 located at the wwwroot folder.)

I search for answers but all failed as follows:

[1] Add the user IUSR to the folder and give it read permission.

[2] http://www.codekicks.com/2008/11/cannot-open-database-northwind.html

[3] http://blog.sqlauthority.com/2009/08/20/sql-server-fix-error-cannot-open-database-requested-by-the-login-the-login-failed-login-failed-for-user-nt-authoritynetwork-service/

Solution

You need to add a user to your SQL Server with the username "IIS APPPOOL\Web2" and give that user access to the database. Note that you will not see this username in search but you might see it change to underlined if you click "check names".

The user does not really exist on the machine except for managing the app pool's permissions.

Alternatively, you could set the application pool to run as a different user.

For more information, see http://blogs.iis.net/webdevelopertips/archive/2009/10/02/tip-98-did-you-know-the-default-application-pool-identity-in-iis-7-5-windows-7-changed-from-networkservice-to-apppoolidentity.aspx and http://learn.iis.net/page.aspx/624/application-pool-identities/

---

Here is an example of the SQL Server Logins list:

Here is an example of a database's Logins list:

Here are the database user's details: (Note that both boxes have the same name in them. The second box must match a login name from the SQL Server Logins list.)

---

You can also try using code instead of the GUI:

USE [master]
GO
CREATE LOGIN [IIS APPPOOL\Web2] FROM WINDOWS WITH DEFAULT_DATABASE=[testbase]
GO

USE [testbase]
GO
CREATE USER [IIS APPPOOL\Web2] FOR LOGIN [IIS APPPOOL\Web2]
GO
EXEC sp_addrolemember N'db_owner', N'IIS APPPOOL\Web2'
GO

I assigned owner permissions but your application may be able to get away with lower permissions. It is usually considered good practice to give the lowest permissions you can get away with.

Comments

[Jack]

I tried your method by "check names" but it really could not find that user. However, I notice that by giving the application pool's Identity as LocalSystem, I am able to get rid of the error. So, I was wondering if this is the "recommended" way to solve the problem?

[Jack]

Yes, I definitely wish to avoid using LocalSystem but I just could not find the IIS APPPOOL\Web2 user even though I used the check names function.

[Moshe Katz]

@Jack If you look at what I said again, you'll see that I wrote "you might see it." That also means you might not. I have added screenshots of my users to the question.

[Jack]

I appreciate your patience in spending time to help me answer my question but no matter how much I tried, I keep on getting lots of errors. I was wondering if you know of any website that shows tutorial on how to proper setup an IIS and MSSQL so that both IIS and MSSQL can communicate with each other correctly?

[Jack]

+1 At last I did it. Actually, I think the problem is that you just need to "don't care" about the check names button and don't bother about the ... button. Just manually enter the IIS APPPOOL\Web2 straight to the User name and Login name field will do. Spend so much time and realize that I just need to manually type the username and ignore the ... button. Sigh!