Login failed for user <.....> Error : 18456

Here is the MSSQL ERRORLOG file :
Everything looks perfect, BUT at the end, I cannot Login when I need to start the SSMS database Engine.....What went wrong ?

2010-04-13 12:29:09.90 Server      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
      Jul  9 2008 14:43:34
      Copyright (c) 1988-2008 Microsoft Corporation
      Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

2010-04-13 12:29:09.90 Server      Error: 17054, Severity: 16, State: 1.
2010-04-13 12:29:09.90 Server      The current event was not reported to the Windows Events log. Operating system error = 1502(The event log file is full.). You may need to clear the Windows Events log if it is full.
2010-04-13 12:29:09.90 Server      (c) 2005 Microsoft Corporation.
2010-04-13 12:29:09.90 Server      All rights reserved.
2010-04-13 12:29:09.90 Server      Server process ID is 3560.
2010-04-13 12:29:09.90 Server      Authentication mode is WINDOWS-ONLY.
2010-04-13 12:29:09.90 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL4BI\MSSQL\Log\ERRORLOG'.
2010-04-13 12:29:09.90 Server      This instance of SQL Server last reported using a process ID of 4108 at 4/13/2010 12:28:16 PM (local) 4/13/2010 7:28:16 PM (UTC). This is an informational message only; no user action is required.
2010-04-13 12:29:09.90 Server      Registry startup parameters:
       -d C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL4BI\MSSQL\DATA\master.mdf
       -e C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL4BI\MSSQL\Log\ERRORLOG
       -l C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL4BI\MSSQL\DATA\mastlog.ldf
2010-04-13 12:29:09.92 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2010-04-13 12:29:09.92 Server      Detected 2 CPUs. This is an informational message; no user action is required.
2010-04-13 12:29:10.20 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2010-04-13 12:29:10.29 Server      Node configuration: node 0: CPU mask: 0x00000003 Active CPU mask: 0x00000003. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2010-04-13 12:29:10.32 spid6s      Starting up database 'master'.
2010-04-13 12:29:10.43 spid6s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2010-04-13 12:29:10.49 spid6s      Resource governor reconfiguration succeeded.
2010-04-13 12:29:10.49 spid6s      SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2010-04-13 12:29:10.49 spid6s      SQL Server Audit has started the audits. This is an informational message. No user action is required.
2010-04-13 12:29:10.51 spid6s      FILESTREAM: effective level = 0, configured level = 2, file system access share name = 'MSSQL4BI'.
2010-04-13 12:29:10.54 spid6s      SQL Trace ID 1 was started by login "sa".
2010-04-13 12:29:10.54 spid6s      Starting up database 'mssqlsystemresource'.
2010-04-13 12:29:10.59 spid6s      The resource database build version is 10.00.1600. This is an informational message only. No user action is required.
2010-04-13 12:29:10.74 spid10s     Starting up database 'model'.
2010-04-13 12:29:10.76 spid6s      Server name is 'PIERRE-F2B17BDA\MSSQL4BI'. This is an informational message only. No user action is required.
2010-04-13 12:29:11.04 spid10s     Clearing tempdb database.
2010-04-13 12:29:11.12 Server      A self-generated certificate was successfully loaded for encryption.
2010-04-13 12:29:11.12 Server      Server is listening on [ 'any' <ipv4> 2835].
2010-04-13 12:29:11.12 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQL4BI ].
2010-04-13 12:29:11.12 Server      Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$MSSQL4BI\sql\query ].
2010-04-13 12:29:11.13 Server      Server is listening on [ 127.0.0.1 <ipv4> 2261].
2010-04-13 12:29:11.13 Server      Dedicated admin connection support was established for listening locally on port 2261.
2010-04-13 12:29:11.15 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
2010-04-13 12:29:11.15 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.
2010-04-13 12:29:11.46 spid10s     Starting up database 'tempdb'.
2010-04-13 12:29:11.51 spid14s     The Service Broker protocol transport is disabled or not configured.
2010-04-13 12:29:11.51 spid14s     The Database Mirroring protocol transport is disabled or not configured.
2010-04-13 12:29:11.53 spid14s     Service Broker manager has started.
2010-04-13 12:29:21.57 spid6s      Starting up database 'msdb'.
2010-04-13 12:29:21.71 spid6s      Recovery is complete. This is an informational message only. No user action is required.
2010-04-13 12:29:31.17 spid51      Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
2010-04-13 12:29:31.17 spid51      FILESTREAM: effective level = 0, configured level = 2, file system access share name = 'MSSQL4BI'.
2010-04-13 12:29:31.17 spid51      Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2010-04-13 12:29:31.17 spid51      FILESTREAM: effective level = 0, configured level = 2, file system access share name = 'MSSQL4BI'.
2010-04-13 12:29:31.17 spid51      Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
2010-04-13 12:29:31.17 spid51      FILESTREAM: effective level = 0, configured level = 2, file system access share name = 'MSSQL4BI'.
2010-04-13 12:29:31.90 spid51      Attempting to load library 'xpsqlbot.dll' into memory. This is an informational message only. No user action is required.
2010-04-13 12:29:31.90 spid51      Using 'xpsqlbot.dll' version '2007.100.1600' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
2010-04-13 12:29:32.13 spid51      Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
2010-04-13 12:29:32.23 spid51      Using 'xpstar.dll' version '2007.100.1600' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2010-04-13 12:29:32.34 spid51      Attempting to load library 'xplog70.dll' into memory. This is an informational message only. No user action is required.
2010-04-13 12:29:32.37 spid51      Using 'xplog70.dll' version '2007.100.1600' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
2010-04-13 13:29:44.65 Logon       Error: 18456, Severity: 14, State: 11.
2010-04-13 13:29:44.65 Logon       Login failed for user 'PIERRE-F2B17BDA\Pierre_2_2'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]
2010-04-13 13:50:30.38 Logon       Error: 18456, Severity: 14, State: 11.
2010-04-13 13:50:30.38 Logon       Login failed for user 'PIERRE-F2B17BDA\Pierre_2_2'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]
pfraysse99Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gothamiteCommented:
It says your windows event log is full. Try clearing it out and try again


0
pfraysse99Author Commented:

Done . Other idea ?
What about SPN registration : Error 0x54b, state 3.  How do you that ?
How do you install Configuration Option using the RECONFIGURE utility ?
( I don't know if this is the root cause of the Login failure, but  it may be worth trying ...)
Thx .

0
gothamiteCommented:
have you restarted the SQL Server Service since you cleared the event log? Try that.

RECONFIGURE is a command you run after using sp_configure to set server-wide SQL settings.
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

pfraysse99Author Commented:
OK, I have gone further in my debugging :  Error: 18456, Severity: 14, State: 11.
 means  "Valid login but server access failure".  So I need to UN-LOCK the user's access to server PIERRE-F2B17BDA.  How do we do that ? with sp_configure ? Thx.
0
gothamiteCommented:
Are you connecting from a remote machine? Is this a new server install? Sql2005/2008 disable all remote connections by default. You have to explicitly enable this. Right-click on the server in SSMS and click properties > connections >allow remote connections
0
pfraysse99Author Commented:
"allow remore connection"  already active.
Here is the sequence of events:
1)Desktop power turned off accidently
2) Reboot, SSMS  find "master" and others corrupted
3) Restored  all master and others mdf databases
4) SSMS cannot login to Database Engine ( SSAS & SSIS are OK)
5) Create a new sa userid
6) 4) SSMS cannot login to Database Engine ( SSAS & SSIS are OK) wth Error: 18456, Severity: 14, State: 11. (Valid login but server access failure".)
7) ???
 
0
pfraysse99Author Commented:
Problem solved.
1) Un-Install ALL instances of current server
2) Un-install current server
3) Re-Install SQL Server 2008 with  NEW Server/ NEW Instance.
4) Activate Configuration manager and verify that all services are "Log On" identically
4) Initiate SSMS, when Connect to  service, make sure to "browse" to the newly created Server Name
5) Done !
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.