0

I have an ASP.net MVC web app which uses Windows Authentication and that part is working well. I introduced a SQL Server database to this web app but I'm having problems accessing this database.

I performed the following actions:

  1. Create database called "MyDatabase" in SQL Server Express.
  2. Registered my website to use IIS rather than IIS Express.
  3. Created an AppPool in IIS with Integrated Security and called it "WebAppAppPool"
  4. Associated "WebAppAppPool" to my website.
  5. Added "WebAppAppPool" to SQL Server using "IIS APPPOOL\WebAppAppPool"
  6. Set the User Mapping in SSMS to grant access to "WebAppAppPool" and selected db_datareader and db_datawriter to "MyDatabase" database.

And yet I'm still getting this error:

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

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 "MyDatabase" requested by the login. The login failed.
Login failed for user 'IIS APPPOOL\WebAppAppPool'.

Things I've tried:

  1. I've tried to make the "IIS APPPOOL\WebAppAppPool" user to be the "owner" of the database but no difference but still fails with the same error.

  2. I've deleted the AppPool user and recreated it but same error.

  3. I've created a another asp.net MVC web app but this time using "user authentication" instead of "Windows Authentication", associated the same app pool to the website, created a new database (i.e. MyDatabase0), assigned "IIS APPPOOL\WebAppAppPool" to it and granted db_datareader and db_datawriter and this worked as immediately. All the membership tables were created successfully.

  4. I've checked the 'identity.Name' and it is set to the username I've logged in as for the website i.e. MyMachine\Me and the Identity.AuthenticationType is set to negotiate. The impersonationLevel is set to 'impersonate'

  5. I've tried changing the 'Identity' under the 'Process Model' in 'IIS' to LocalSystem, LocalNetwork and NetworkService instead of using the ApplicationPoolIdentity and it made no difference. All of them generated the same error described above except for the credential name matching whatever the identity was set to.

Can you think of anything else that could be causing this problem? Am I missing something in terms of what needs to be set/done in order for this to work?

Can you even use the AppPool when using Windows Authentication?

Thanks

Improve this question
2
  • What connection string you are using? Commented Mar 6, 2018 at 0:34
  • @PankajKapare Apologies for the delay. I'm using a standard connection string using integrated security i.e. Data Source=mymachine\sqlexpress2012;Initial Catalog=mydb;Integrated Security=true Commented Mar 8, 2018 at 8:58

1 Answer 1

Reset to default
2

Since your connecting string is using "Integrated Security=true", it is picking application pool's identity (in this case IIS APPPOOL\WebAppAppPool) to connect to database. There are two ways to fix it.

  1. Use different identity on application pool which has database access and no change is required in connection string.

  2. Use connection string in following format and no changes required in application pool's identity.

    Server=myServerAddress;Database=myDataBase;User Id=myUsername; Password=myPassword;

Based on your requirement you can choose one of above option.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I already added 'IIS APPPool\WebAppAppPool to SQL Server and gave it Read/Write access. I also tried changing the identity used by the AppPool to the different ones available and ensure each were added to SQL Server with Read/Write access and while the error remained the same i.e. 'Cannot open database ...' and I could see the AppPool identity change being reflected in the error. As mentioned, the strange thing is that if I use the same AppPool with 'Single User Login' web app instead of Windows Authentication. It works immediately.
Regarding using the Username and password in the connection string, I'd rather not use it if possible and would prefer having a solution using the AppPool and Windows Authentication. Could it be that I'm not on a domain and I'm testing this from a laptop that's not part of a domain and I'm using a local user? I can access the web site ok, just not the database as I get this error.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.