asked on

MSSQL Logins fail new install SQL2000 error 18456, how can I diagnose?

Need a hand with login failure to MSSQL 2000 Windows server 2003. This is a new installation. The client is asp.net client. I created my database and gave the windows user dbo rights to my database. Receiving error 18456 . Sqlserver log does not contain state number info so I am stuck. I switched to mixed mode auth but no help.

How can I diagnose this without state number or what other means do I have to resolve?

Thanks in advance for your assistance.

Crag

Have you tried to open an instance of Query Analyzer using the same Windows account?

jamfreeagent

ASKER

I was able to login using query analyzer using the same windows account.

ASKER CERTIFIED SOLUTION

Crag

membership

This solution is only available to members.

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

Start Free Trial

jamfreeagent

ASKER

Crag,
I am unable to find "failed logins auditing" in SQL server instance properties. Is this available from Enterprise Manager in SQL Server 2000?
Thanks again...
John

Crag

Yes it should be - don't have 2000 install right now but right click the Instance in EM and select Properties.
Should be on the Security tab. I'll see if I can find a screen that jogs my memory

jamfreeagent

ASKER

At server/security tab / Audit level I see "None / Success / Failure / All" I have it set to all.

jamfreeagent

ASKER

Profiler works unfortunately. I am coming in as .NET user with a connection string of "Data Source=myServername;Initial Catalog=MyDB;User ID=myUser;Password=myPass" . myUser/myPass works OK query analyzer.

SOLUTION

Hemantgiri S. Goswami

membership

This solution is only available to members.

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

Start Free Trial

Crag

as the myUser login works I suspect that it could be a code issue within SQL Server.
I'd say that the state information is intended to show the cause of a failed login.
If no state info exists the code may have a fault that caused it to return before genrating the state value.
Does the system have the latest service pack?
Have you checked the cumulative fixes for SQL to see if this is a known issue?
Have you tried to use the same connection method to a different server?

jamfreeagent

ASKER

Well, for some reason I cannot get state information into the sqlserver log for further diagnosis. I did find my specific problem, the connect string I was using was setup for SQL user security not integrated security. I am going to move to SQL user security and be done with it. Crag, I dont know how to give you credit yet I dont think this solution should go to the knowledge base. Can you share with me how I can close this out to accomplish both things? This is my first post with experts-exchange...

Thanks again for your help.
Regards,
John

Crag

Glad to hear it's all sorted.
On the bottom of each of the comments there should be two options:

Accept Multiple solutions
Accept this solution

Just click which ever is appropriate on the comment where it applies.

jamfreeagent

ASKER

The connect string I was using was setup for SQL user security not integrated security. I could not find any means to enable extended "state" information with login failures, even tried restarting sql server after setting audit level to all. Closing this discussion. Please dont add to knowledge base, since my original question was how do I obtain state information so I can further diagnose login failures. Regards, John