Solved

Cant login into application with II7

Posted on 2011-02-15
10
451 Views
Last Modified: 2012-06-22
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..
0
Comment
Question by:QuinnDester
  • 6
  • 2
  • 2
10 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 34899038
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"?
0
 
LVL 3

Author Comment

by:QuinnDester
ID: 34899074
onlineodr is the website account name, and it is set to passthrough access, not to use any authentication
0
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 34899245
Sure. But what about the database? Accesses to the DB will be made under the account the site is running as, unless you are using impersonation. When you create a website with defaults, ASPNET would be the user trying to access the DB, so you would have to allow that user access to the DB. Here, you have changed the user to onlineodr, so that user will need DB authorization.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 3

Author Comment

by:QuinnDester
ID: 34899474
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.
0
 
LVL 3

Author Comment

by:QuinnDester
ID: 34899508
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.
0
 
LVL 3

Author Comment

by:QuinnDester
ID: 34900052
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.
0
 
LVL 33

Expert Comment

by:Todd Gerbert
ID: 34900056
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?
0
 
LVL 3

Author Comment

by:QuinnDester
ID: 34900945
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
0
 
LVL 33

Expert Comment

by:Todd Gerbert
ID: 34901145
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.
0
 
LVL 3

Author Comment

by:QuinnDester
ID: 34902659
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
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This tutorial will walk an individual through locating and launching the BEUtility application and how to execute it on the appropriate database. Log onto the server running the Backup Exec database. In a larger environment, this would generally be …
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question