Login Failed for user?

Question

I am developing a web app that connects to a SQL 2000 database. Everything works perfectly on my database (which is actually SQL 2008) but when I try to migrate it onto another server (that's actually running SQL 2000) I get some strange errors.

I'm getting Login Failed for the username that the web app uses, so I did my normal troubleshooting steps...

I reset the password to what it should be, made sure the user was mapped to the database it's trying to connect to. I connected to the database through Enterprise manager using the user name and password and was able to run queries. I reset the SQL server.

I'm fresh out of ideas other than there might be a place in my app that the password is for some reason getting changed. Is there anyway to see what password the SQL server is seeing? I just want to narrow down my search a little.

Either that or does anyone have any other suggestions on how I might be able to fix this?

EDIT: Also, the web app CAN talk to the database, it hits the database to get login credentials and it can login with no problems. The error is coming up later in the app when I try to get more information from the database, like parameters for a report or an export location.

Thanks in advance!

Answer 1

Possibly your SQL server isn't set to allow remote connections?

EDIT: or your firewall doesnt have the right ports opened?

EDIT2:

If your web script is on the same server as the SQL server, the only thing that i can think of is that you have specified an incorrect password, of if you referenced the old server by name (even though it was localhost) and you have not updated it. If the web script is on a different server, check your firewall ports and ensure the sql server is set to allow remote connectioins.

EDIT3:

Appologies, i didn't see your update before i posted the last edit (EDIT2). Thomas is right, give that a go.

Answer 2

First, the problem is not that the login failed for a user. From your description, the login succeeded. However, you stated that you later got an exception when trying to access certain objects. This sounds like an authorization/permissions issue with the database user to which the login is associated and the objects it's trying to access. Have you tried connecting to the database using Enterprise Manager and the same credentials used by the site and executing the identical query as the web application?

Answer 3

Not sure how you're doing your migration but you may want to make sure your sql user is not getting orphaned:

From - http://www.fileformat.info/tip/microsoft/sql_orphan_user.htm

First, make sure that this is the problem. This will lists the orphaned users:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

Answer 4

I found the problem!

It was actually some lingering queries I had in the app. I started populating some down downs differently and the queries were never removed, as soon as I took those out the errors stopped popping up.

Still it's strange that this would not effect the app on my machine but on this other machine would cause all kinds of havok.

Thank you all for your help and suggestions, it really helped narrow down the problem. Thomas gets the accepted answer though because his suggestion pointed me in the right direction.