Failed Login

  • Hey Everyone

    I have a SQL 2005 box, the service starts using a domain account. I have noticed errors in the SQL logs for some time now. The error message is as follows:

    Login Failed for user 'DomainName\UserName'. CLIENT: [ ]

    Error: 18456, Severity: 14, State: 16

    All of my servers use this account for the SQL service to run under. None of the other boxes are having this issue. The service is currently running, and I have stopped and restarted the service without any login failure. The box is running fine, it is just logging this message a couple times per second.

    Can anyone give me an avenue to follow? Of a direction to go in resolving this issue.

    Thanks

    Andrew SQLDBA

  • AndrewSQLDBA (4/17/2008)


    Hey Everyone

    I have a SQL 2005 box, the service starts using a domain account. I have noticed errors in the SQL logs for some time now. The error message is as follows:

    Login Failed for user 'DomainName\UserName'. CLIENT: [ ]

    Error: 18456, Severity: 14, State: 16

    All of my servers use this account for the SQL service to run under. None of the other boxes are having this issue. The service is currently running, and I have stopped and restarted the service without any login failure. The box is running fine, it is just logging this message a couple times per second.

    Can anyone give me an avenue to follow? Of a direction to go in resolving this issue.

    Thanks

    Andrew SQLDBA

    Hello Andrew,

    Open the SQL Service and retype the password and then re-start the service. This should take care.

    Thanks


    Lucky

  • I have tried that. Still the same error

    Thanks

    Andrew

  • Is the user a local admin on the box?

    ¤ §unshine ¤

  • AndrewSQLDBA (4/17/2008)


    I have tried that. Still the same error

    Thanks

    Andrew

    This is the explanation

    # re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005

    State=16 means that the incoming user does not have permissions to log into the target database. So for example say you create a user FOO and set FOO's default database to master, but FOO does not have permissions to log into master.

    This can also happen if for example the default database for user FOO is not online (for example the database is marked suspect).

    So to check on this theory, try logging the user into some other database and then try using the USE DATABASE command to switch to the target database, you will get a better error message as to the root cause of the failure.

    and this can be viewed at

    http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

    Hope this helps in further troubleshooting....

    Thanks


    Lucky

  • The user is in the domain Admin group, so yes, they are also a member of the local admin group

    Andrew

  • The account that I am using to start the service is a domain account. I cannot log in as that account. Plus, it is set as a non-human account. This is done so that no one in particular knows that password. I am authenticating the user via an Active Directory account.

    I have checked everything that I can think of on this.

    Thanks

    Andrew SQLDBA

  • Are you sure that nothing else is trying to log in using this account? I had the same problem the other day, same cause as Lucky posted above. You can use profiler to get additional information on what process is attempting the login, use the audit failed login event with all columns on - you should be able to get the application name at least. I believe the error log gives you the ip address.

  • Just a thought - but have you checked the SQL Agent service?

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Just because it is a domain admin does not mean it is a local admin or SA on your database server. Since the service starts, it is unlikely that it is not, but make sure the domain admin group is in the local administrators group and then make sure local administrators have sysadmin permissions on the SQL server.

    With that out of the way, check the local event viewer for other services failing to start. If that does not yield anything, start profiler and pull back all of the data columns on failed logins - you should be able to get the application name this way. It is likely that you have another service (SQL Agent, Reporting Services, SSIS, etc.) on this server that is failing because of a bad password in the service configuration.

    Also, if all of your SQL servers use this login, make sure you do not have an issue with a linked server or a maintenance plan.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply