SQL Server 2000 - Login issue

Question

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 either locally/ remotely.

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? Can I ask them to renter the credentials in all the places they use? 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.

Answer 1

Do you see any records in the ERROR.LOG?

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

So more exactly what error message do you get for these login failures?

From your description, my guess is that they are not able to connect to the database they want to connect to. In any case, if you dropped the logins and recreated them, you have caused some damage, since the logins no longer match the database users. (Since you talk about passwords, I assume that you have SQL logins.) You need to use sp_change_users_login to fix this. Once you have done this, attempt to connect from OSQL with:

osql -S server -U user -P pwd -d db

to see that these logins are able to connect to the databases.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

One more simple guess... Is your server configured in Mixed mode authentication?

---

Mark as ANSWER if I helped you today :-)

Answer 4

Thanks Erland. The user(s) that's associated with the login(s) are not orphaned. And I am able to connect to SQL using SSMS/ Enterprise Manager either locally/ remotely with the credentials as that of the logins used by the webservers/ filenet. 

I had tried using OSQL utility and I am able to get the results as desired.

Answer 5

Hi Uri,

I don't see any login failure messages written in either the sql server logs/ event viewer logs. Thanks.

Answer 6

Yes it is, I guess I wouldn't have been able to connect to SQL using SSMS/ Enterprise Manager had it not been configured as mixed authentication mode.

Answer 7

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.