?
Solved

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

Posted on 2007-10-15
12
Medium Priority
?
273 Views
Last Modified: 2013-11-07
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.
0
Comment
Question by:jamfreeagent
  • 6
  • 5
12 Comments
 
LVL 5

Expert Comment

by:Crag
ID: 20080670
Have you tried to open an instance of Query Analyzer using the same Windows account?
0
 

Author Comment

by:jamfreeagent
ID: 20080804
I was able to login using query analyzer using the same windows account.
0
 
LVL 5

Accepted Solution

by:
Crag earned 900 total points
ID: 20080827
You might need to resort to running the Profiler to try and capture the session as it connects to see why it failed.
I presume that you already have failed logins auditing enabled in the SQL Server Instance properties?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:jamfreeagent
ID: 20081016
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
0
 
LVL 5

Expert Comment

by:Crag
ID: 20081029
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
0
 

Author Comment

by:jamfreeagent
ID: 20081059
At server/security tab / Audit level  I see "None / Success / Failure / All" I have it set to all.
0
 

Author Comment

by:jamfreeagent
ID: 20081244
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.
0
 
LVL 7

Assisted Solution

by:Hemantgiri S. Goswami
Hemantgiri S. Goswami earned 600 total points
ID: 20083770
Hi,
Suggest to refer Refer http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

Hemantgiri S. Goswami | MS SQL Server MVP
0
 
LVL 5

Expert Comment

by:Crag
ID: 20085260
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?
0
 

Author Comment

by:jamfreeagent
ID: 20086361
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
0
 
LVL 5

Expert Comment

by:Crag
ID: 20086641
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.
0
 

Author Comment

by:jamfreeagent
ID: 20089859
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
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question