asked on

Login issues - SQL 2000

I have a weird issue. There are a couple of SQL logins (on SQL 2000), I am able to connect to SQL using these logins using SSMS/ Enterprise Manager.
These logins are used by a couple of webservers and Filenet. I had run a trace and I see the logins getting failed in the trace file. I was struggling with this issue for the last 36 hours, I couldn't figure out what the issue is. I had deleted the logins and recreated them with the same ID/ password, and the issue didn't get resolved

Can you please give some inputs as to what might be the issue and how this can be resolved? If I may ask the app. teams to delete and reenter the credentials, where should I ask them to make the changes? (For ex: Connection string, IIS service etc). Please let me know. Thanks in advance.

25112

from what you are telling, the login surely has CONNECT permission.. but may not have permission for what the webservers and Filenet servers need it to do... for example, if the server have it to get some data, and if it does not have SELECT permission on the table or db_datareader, then it is a problem :(

2 ideas..
1)
run
exec sp_helpuser
in the appropriate database in SSMS/ Enterprise Manager.. what permissions do you see this login has in this database?

2)run
EXEC sp_change_users_login 'Report'

do you see the login.. then it has not been setup right. If you see the login listed from the above you may have to fix it with...

EXEC sp_change_users_login 'Update_One', 'UserName', 'LoginName'

rocky_lotus_newbie

ASKER

The logins have dbowner permissions on the databases and the users associated with the respective logins are not orphaned. I had connected with the credentials of these logins, ran some queries that are being run in the application code and able to get the desired results.

25112

>>I had run a trace and I see the logins getting failed in the trace file.

Do you get a error message or some other alert about the failure?

rocky_lotus_newbie

ASKER

The trace output says 'Login failed for user user1.'. I tried to include the event 'user error messages' that's under 'Errors and Warnings', but didn't find it in sql server 2000.I had also tried to run the trace from one of the sql server 2008 r2 servers, but didn't find this event when run against the sql 2000 server.

25112

run
exec xp_readerrorlog and see if it is captured there.. if it is not there, it is not a real error.

also please review
http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/c846d6b6-0cb1-46b7-b3fb-13a69f9695c1/

25112

also, it is possible that the application is using the wrong password!!
please check the login's password is properly inserted in the connection string.

rocky_lotus_newbie

ASKER

I had looked at the sql server logs but didn't see login failure errors written in the logs.

25112

also make sure to enable mixed mode authentication (you may be connecting with win authentication and it is good, but the server may not be allowing sql authentication)?

rocky_lotus_newbie

ASKER

the authentication mode is mixed.The weird thing is I am able to connect to sql server in question using SSMS/ Enterprise manager ( either remotely/ locally) using the same sql login credentials.

25112

>>If I may ask the app. teams to delete and reenter the credentials, where should I ask them to make the changes?

Definitely, the connection string to begin with... most likely they have entered the password wrongly..

25112

>>The weird thing is I am able to connect to sql server in question using SSMS/ Enterprise manager ( either remotely/ locally) using the same sql login credentials.

That has confirmed the login is good in every way.. so it is highly possible that the login fails only when the wrong password is given..

please put a wrong password and see what error you get..

ASKER CERTIFIED SOLUTION

rocky_lotus_newbie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rocky_lotus_newbie

ASKER

Thanks 25112 for assisting.

rocky_lotus_newbie

ASKER

I was able to resolve the issue. The collation option set on the server is Latin1_General_BIN which is case sensitive. One of the databases which's the default database for the sql logins had a different name in that the case was not specifed correctly. Once that's corrected, everything worked.