July 26, 2010 at 6:49 am
Hi,
I am getting the below error continuously in my error log. This occurs every minute and its from the same machine. Finally I tried dropping the user but still the entry keeps on coming in the errorlog.Why does the error come even though there is no corrsponding login ? And how do I go about troubleshooting it ?
Date7/26/2010 8:38:43 AM
LogSQL Server (Current - 7/26/2010 8:38:00 AM)
SourceLogon
Message
Login failed for user 'xxxxxx'. [CLIENT: xx.xx.xx.xx]
Message
Error: 18456, Severity: 14, State: 5.
Thanks in Advance !
July 26, 2010 at 6:57 am
Client xx.xx.xx.xx is still trying to connect to your server, even though you've deleted the login it's trying to use. Find out what application is running on that client and configure it so that it uses the correct details.
John
July 26, 2010 at 7:01 am
But I thought if the corresponding login was removed the error log entry will not occur. The developer involved does not have a clue as to which application or wot is causing this issue. In that case wot should be my approach ?
July 26, 2010 at 7:16 am
You can use the Profiler tool to capture this information.
In "Event Selection" click "Show All events" and select "Audit Login Failed".
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
July 26, 2010 at 7:19 am
You need to add the login for the SQL Server instance.
Here is an article, which describes all the state of Login Failed error 18456
Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly
July 26, 2010 at 7:36 am
Sudeepta (7/26/2010)
You need to add the login for the SQL Server instance.
Only if you are happy for this user/PC/application to be connecting to your database. As Pradeep suggests, use Profiler, making sure you capture the application name.
John
July 26, 2010 at 11:10 am
This user is no longer needed in our environment. So it can be dropped. I ran profiler. The application name is given as ".net sqlclient Data provider". Could not find anything more useful from the trace file.
July 27, 2010 at 1:20 am
I think your only realistic option is to get on to the client PC and find out what it's running. I'd have thought that a developer might have some idea what applications are running on .Net Client on his/her PC.
A slightly more draconian solution is to change the port that SQL Server listens on and switch off SQL Browser. You'd need to reconfigure all the applications that connect to the database server if you did that, though.
John
July 27, 2010 at 4:06 am
The machine is an application server with lots of application and changing the port number or switching sql browser off is not possible. But yes as you said the realistic option is to get to the application server. Really appreciate the help.
Thanks !
July 28, 2010 at 6:41 am
Is it a windows or SQL login??
Have anyone changed the password??
There are chances that password might have changed for the login and some where someone might have saved the old password...
Check the error log and find the IP of that particular machine connecting..
July 28, 2010 at 6:50 am
The login has been dropped from the server. The login is an SQL login. The errorlog is shown in my first post. From that I was able to find the IP. By running profiler I was able to find the application name as ".net sqlclient Data provider".
But still not able stop the login failed entry to the errorlog.
July 28, 2010 at 6:58 am
Check syslogins..
Have u checked giving anyother name as login and try will get same error...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply