none
Good Old Event ID: 18456 - Can't figure this one out!

    Question

  • Hi everyone,

     

    I've got a clean SQL Server 2005 Enterprise Edition installation, with a domain account configured as the service account.  My application log is now flooded with the following error message:

     

    Event Type: Failure Audit
    Event Source: MSSQLSERVER
    Event Category: (4)
    Event ID: 18456
    Date:  5/25/2007
    Time:  1:57:00 AM
    User:  TNG\TNG-SQL_Service
    Computer: TNG-MOSSDB01
    Description:
    Login failed for user 'TNG\TNG-SQL_Service'. [CLIENT: <local machine>]

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
    Data:
    0000: 18 48 00 00 0e 00 00 00   .H......
    0008: 0d 00 00 00 54 00 4e 00   ....T.N.
    0010: 47 00 2d 00 4d 00 4f 00   G.-.M.O.
    0018: 53 00 53 00 44 00 42 00   S.S.D.B.
    0020: 30 00 31 00 00 00 07 00   0.1.....
    0028: 00 00 6d 00 61 00 73 00   ..m.a.s.
    0030: 74 00 65 00 72 00 00 00   t.e.r...

    And when I open up the SQL logs, I'm getting two errors - one is essentially the same as the above, and the other is:

     

    Date  5/25/2007 1:51:00 AM
    Log  SQL Server (Current - 5/25/2007 1:51:00 AM)

    Source  Logon

    Message
    Error: 18456, Severity: 14, State: 16.

     

    Now I've seen all kinds of posts about granting rights to master and such - and I have, but these errors are not going away.  I've gone so far as to create new service accounts and even switch over to the local system account - and the error still occurs. Does anyone have any insight into other routes / approaches I can take with this?

     

    Thank you,

    Chris 

    Friday, May 25, 2007 6:01 AM

Answers

  •   You can try the following statements to see if there is anything unexpected:

     

    SELECT name, principal_id, type_desc, is_disabled, default_database_name, default_language_name

         FROM sys.server_principals

        WHERE name = 'login_name'

    go

     

    SELECT name, database_id, user_access_desc, state_desc, is_in_standby

        FROM sys.databases

        WHERE name = 'default_db_name'

    go

     

    SELECT suser_name(role_principal_id), suser_name(member_principal_id)

        FROM sys.server_role_members

        WHERE member_principal_id = suser_id( 'login_name' )

    go

     

    SELECT class_desc, suser_name(grantee_principal_id), permission_name, state_desc

        FROM sys.server_permissions

        WHERE grantee_principal_id = suser_id( 'login_name' )

    Go

     

    Pay attention to the default database, if the login is disabled and if it has at least CONNECT SERVER permission.

     

      I hope this helps,

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Sunday, May 27, 2007 2:49 AM

All replies

  • From BOL  topic Troubleshooting: Login failed for user 'x'

     

    Additional Error Information

    To increase security, the error message that is returned to the client deliberately hides the nature of the authentication error. However, in the SQL Server error log, a corresponding error contains an error state that maps to an authentication failure condition. Compare the error state to the following list to determine the reason for the login failure.

    State Description

    2

    User ID is not valid.

    5

    User ID is not valid.

    6

    An attempt was made to use a Windows login name with SQL Server Authentication.

    7

    Login is disabled, and the password is incorrect.

    8

    The password is incorrect.

    9

    Password is not valid.

    11

    Login is valid, but server access failed.

    12

    Login is valid login, but server access failed.

    18

    Password must be changed.

    Other error states exist and signify an unexpected internal processing error.

     
    You may need to contact Support for this.
     
    HTH,
     
    -Steven Gott
    SDE/T
    SQL Server
    Friday, May 25, 2007 3:53 PM
  • Hi Steve,

     

    Do you by chance have any information on State = 16?

     

    Thanks,
    Chris

    Friday, May 25, 2007 5:10 PM
  • It is related to not being able to establish a database session.

     

    Check the default_database for your account and make sure it is master.

     

    How are you changing the service account?  Please make sure you use the tools provided in sql server because usinging windows scm will not do the whole job.

     

    HTH,

     

    -Steven Gott

    SDE/T

    SQL Server

    Friday, May 25, 2007 11:11 PM
  • Steve,

     

    The default_database for the account is master, and the service accounts were set through the SQL Server Configuration Manager.  I went back into it and reset all of them just in case - but still getting the error.  Just doesn't make any sense.  In fact, I'm also seeing the same error for another account - the service account I use with SharePoint (residing on another server).  Same exact errors in the Event Log for the Server & the SQL Logs.

     

    Are there any statements I can run against the master to see if maybe an entry is missing?  I'm drawing at straws with this.

     

    Thanks,
    Chris

    Saturday, May 26, 2007 4:38 PM
  •   You can try the following statements to see if there is anything unexpected:

     

    SELECT name, principal_id, type_desc, is_disabled, default_database_name, default_language_name

         FROM sys.server_principals

        WHERE name = 'login_name'

    go

     

    SELECT name, database_id, user_access_desc, state_desc, is_in_standby

        FROM sys.databases

        WHERE name = 'default_db_name'

    go

     

    SELECT suser_name(role_principal_id), suser_name(member_principal_id)

        FROM sys.server_role_members

        WHERE member_principal_id = suser_id( 'login_name' )

    go

     

    SELECT class_desc, suser_name(grantee_principal_id), permission_name, state_desc

        FROM sys.server_permissions

        WHERE grantee_principal_id = suser_id( 'login_name' )

    Go

     

    Pay attention to the default database, if the login is disabled and if it has at least CONNECT SERVER permission.

     

      I hope this helps,

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Sunday, May 27, 2007 2:49 AM
  • Chris,

     

    You can try disable all the jobs, then one-by-one enable them. You will be able to identify which job causes the error. There are cases where the password does not refresh inside of the job itself. Once you disable it, then enable it again, that resolve your problem. Microsoft has not yet fix their treeview control, that can be an issue with not refreshing.

     

    Felix

     

    Wednesday, May 07, 2008 6:26 PM

  • This worked for me:
    http://www.pmtoolbox.com/failure-audit-message-in-sql-server-event-id-18456-every-minute
    "

    I did a SQL Profiler trace to see where it was coming from and discovered the cause was a SQL Server Agent job called SharedServices_DB_Job_DeleteExpiredSessions that was running every minute.  The reason for the failure was that I did not have a SharedServices_DB on that server.  I did once - but my test server gets changed around a fair bit and this was a remnant that didn't get cleaned up. Not sure if it would normally get removed and if I did something bad which left it hanging around. 

    This could also happen with Microsoft Office Server 2007 even if Project isn't installed as it relates to the Shared Services Provider.  To disable the job you can go to SQL Management Studio, and connect to your database engine, then expand SQL Server Agent - select the Disable option. 

    "
    • Proposed as answer by HelpMEwithMOSS Tuesday, December 04, 2012 4:32 PM
    Monday, May 12, 2008 7:11 PM
  •  

    We just migrated our MOSS 07 databases from our single box (advanced) install to a backend sql server box.

     

    The SharedServices_DB_Job_DeleteExpiredSessions job did not get created on the new sql box, but was still running on the old box. How do I recreate this job on the new sql box, as I understand it is necessary for session clean up?

    • Proposed as answer by CFR Friday, March 06, 2009 2:37 PM
    Thursday, November 06, 2008 9:03 PM
  • Thanks so much for this very detailed and accurate explanation, this was exactly my problem as well and I was able to remove two SQL Server Agent jobs that were failing due to removed databases.

    Cheers,

    Bill

    Tuesday, June 08, 2010 3:28 PM