Link to home
Start Free TrialLog in
Avatar of QuinnDester
QuinnDesterFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Cant login into application with II7

I have an application built in VS2005 C# for updating a table in my MS SQL 2005 database, the appliaction works fine on our old 32bit  and running in Debug mode on the new server

I have the website set to run in 32bit mode

However when i publish the site i get the following error

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'IIS APPPOOL\onlineodr'.

The site also gives this error when i try and retrieve the password which doesnt require a login
The login uses the inbult member page object that comes in the VS 2005 tools

IIS7 is completely new to me, and nearly every problem i have encountered to date moving to this server has been IIS7 related if this one is i cant figure out what it is..
Avatar of kaufmed
kaufmed
Flag of United States of America image

It sounds like the application pool user is trying to authenticate with your Sql Server DB, by way of Windows Authentication. Have you granted access to your DB to the user "onlineodr"?
Avatar of QuinnDester

ASKER

onlineodr is the website account name, and it is set to passthrough access, not to use any authentication
ASKER CERTIFIED SOLUTION
Avatar of kaufmed
kaufmed
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
the DB is accessed using its own conection string and user that is held in the web config file

I think i am missing something in what your trying to explain to me.
the  onlineodr is  the application pool  identity, the db is accessed from the application using connection string held in the web config, the application pool is configured to passthrough without authentication and will not be trying to access the DB.
I think it finaly dawned on me what you were trying to explain, i changed the application pool identity to use LocalSystem rather than ApplicationPoolIdentity and now its working fine..

Thank you, i didnt quite get what you were saying but it made me ask the right questions of google and i came across the answer here.

http://www.gotknowhow.com/articles/fix-login-failed-for-user-iis-apppool-aspnet-v4-error-iis7

though this talks about .net 4 i think this is another quirk of IIS7 that we will have to learn to live with.
The SQL database requires a login.  You can either 1) pass a user ID and password in the connection string (SQL authentication), or 2) use whatever user the code is currently running as to log into the SQL server (Windows authentication, on the SQL server).

The error message you posted would indicate that #2 is the case here, and since your web site code is running as the application pool's identity (i.e. IIS APPPOOL\onlineodr) that's the username it's trying to log into the SQL server with.

Your connection string might be wrong - does it include User ID=someValidSqlLogin with an appropriate password, and does not have Integrated Security?
the conection string is correct, i think the issue is with the application pool getting access to other applications on  the system before it can even attempt to use the connection string to access the database... this is going beyond my understanding a bit so not sure
You've already got your answer, but just for the sake of discussion...

You can log into a SQL database by usin a username and password that is setup in SQL Server, and pertains to nothing but SQL (SQL authentication); you can also log into a SQL database by automatically using your current Windows username (Windows authentication).

Imagine you've got a regular Windows application that uses this connection string: Data Source=TheSqlServer;Initial Catalog=Northwinds;Integrated Security=Yes.  When I log into my computer I use the username "tgerbert", and when I run this application and it uses that connection string it's going to try and log me into the SQL server using my Windows username, which is "tgerbert."  That Windows username needs to be listed in SQL server and given permission to use the database.

Alternatively, if it had the connection Data Source=TheSqlServer;Initial Catalog=Northwinds;User ID=dbuser;Password=secret then it doesn't matter what username I log into the computer with, the application will always log into the SQL server using the SQL-only username "dbuser" - SQL usernames are entirely indpendant of and unrelated to Windows usernames, and are setup in SQL server itself.

You're describing the first scenario - except that instead of a Windows application your program is run on the web server.  Since your website runs as the Windows user "IIS APPPOOL\blahblah", that's what it tries to log into SQL server with.  If you added that Windows user to the SQL server it would've worked.  When you changed it to LocalSystem your website now runs as the Windows user "SYSTEM", which by default already has access to SQL server (which is why it works for you now).

Ideally, for the sake of security, you run your website as a user with less privileges than the LocalSystem account, like LocalService or ApplicationPoolIdentity, and setup your SQL server to allow that user to login.
That makes perfect sense, my app uses 2 connection strings, one to authenticate the user connecting to .nets aspnetdb this has the connection string you described first, then i have a second for retrieving data, which uses the other kind of conection string with username and password..

i see the difference now and understand why i was having problems.. Thanks