Failed Login

Question

Failed Login

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
April 17, 2008 at 3:18 pm

#379990

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

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

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

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

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

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

I have tried that. Still the same error

Thanks

Andrew

sunshine-587009 SSCrazy Eights Points: 8981 More actions · Answer 3

sunshine-587009

SSCrazy Eights

Points: 8981

April 17, 2008 at 3:52 pm

#804044

Is the user a local admin on the box?

¤ §unshine ¤

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 4

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

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 5

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

Andrew

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 6

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

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 7

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.

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 8

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 9

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.