Login issues - SQL 2000

rocky_lotus_newbie
rocky_lotus_newbie used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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'

Author

Commented:
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.

Commented:
>>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?
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
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.

Commented:
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/

Commented:
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.

Author

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

Commented:
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)?

Author

Commented:
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.

Commented:
>>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..

Commented:
>>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..
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.

Author

Commented:
Thanks 25112 for assisting.

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial