4

I have a connection string that looks like this:

Server=.;Database=mydbname;Trusted_Connection=True;

This works fine when I run/debug my application using Visual Studio (IIS Express) but after deployment over IIS-8 it throws me an exception while opening connection that:

Login failed for user 'IIS APPPOOL\mysite'.

I am confused why this is happening why IIS is adding its user in the middle when the database doesn't require one, and why this is not happening with IIS Express?

Improve this question
5
  • IIS express works with your user. IIS works on application pool user. Add to sql express permissions to access DB for IIS APPPOOL\mysite. Commented Dec 21, 2013 at 21:20
  • Here you go: technologycrowds.com/2013/03/… Commented Dec 21, 2013 at 21:22
  • I am using SQL server 2012 Enterprise and can you please explain a bit how can i add this permission to sql? and do i need to change this everytime i make some changes to app pool? Commented Dec 21, 2013 at 21:22
  • Also, make Trusted_Connection=False because setting Trusted_Connection=true in the connection string will override the SQL authentication values with the IIS Identity user profile. Commented Dec 21, 2013 at 21:23
  • @Ani tried the solution in the link still having the error but has changed now it says Login failed for user 'NT AUTHORITY\LOCAL SERVICE'. Commented Dec 21, 2013 at 21:28

3 Answers 3

Reset to default
8

You're using Trusted_Connection=True and thus the user ApplicationPoolIdentity or IIS APPPOOL\mysite is supplied to SQL server.

Option 1. Give permissions to IIS APPPOOL\mysite in SQL server.

Option 2. Use User Id=User_Id;Password=****** instead of Trusted_Connection=True

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

2 Comments

but my SQL Server is set to windows authentication mode and doesnt not have a user/pass .. should i create one?
Option 2 works for me in that way: User=User_Id;Password=******;Trusted_Connection=False
4

This is occurring because you have asked for the connection to be made using a trusted connection, which means that the operating system user that the code is being executed as will be the one connecting to the database.

In the case of IIS, the operating system user is usually defined in the IIS Application Pool unless you have specified impersonation to be used in web.config.

By default (in order to prevent security issues), the default identity used for IIS Application Pools has a very limited capability. Depending on your configuration (where SQL server is in relation to IIS (same machine, different machines), you may need to either specify a different identity for the app pool and/or you may need to grant authority to access SQL Server to the app pool identity.

This Microsoft article has additional information on how to configure ASP.Net impersonation.

We always configure our web applications to impersonate a domain user so that we can access databases and other resources on remote machines without having to affect the IIS application pool settings.

Improve this answer

1 Comment

+1 letting the app pool run under a domain service account is probably the right way to go
0

Having Trusted_Connection=true in the connection string will override the SQL authentication values with the IIS Identity user profile.

Set Trusted_Connection=false

Improve this answer

Comments

Your Answer

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