SQL Server Performance Forum – Threads Archive

Setting up alert for failed logins

Hi,
I am trying to set up an alert for failed logins on sql server i.e. error #18456.I have also set up the audit level for failed logins.It seems that the alert is not being fired though the auditing is working fine and I can see the error messages in the logs.Any ideas?
TIA
Siddhartha

From BOL: Troubleshooting Alerts
If you are experiencing problems with alerts, read the solutions detailed here. An alert is not firing.
Ensure that the SQLServerAgent and EventLog services are running.
Ensure that the event appears in the Microsoft Windows NTÂ® application log.
Start the Windows NT Event Viewer. If the event is not in the log, check the log settings. On the Log menu, click Log Settings, and then in the Change Settings for Log box, select Application. If needed, set these options to the specified values. Setting Value
Maximum Log Size Minimum of 2,048 KB (2 MB)
Event Log Wrapping Overwrite Events as Needed
Note Also check the SQL Server error log; events written to the Windows 2000 or Windows NT application log are also written to the SQL Server error log. To focus the search on the cause of the problem, compare the dates and times for events between the SQL Server error log, the SQL Server Agent error log, and the Windows 2000 or Windows NT application log. Ensure that the alert is enabled.
Ensure that the history values of the alert (for example, the occurrence count and last occurred values) are changing.
Ensure that the counter value is at, above, or below the defined threshold value for a minimum of 20 seconds.
SQL Server Agent polls the performance counters at 20-second intervals. Important Using a frequency higher than 20 seconds increases the processing overhead for SQL Server. If a counter spikes for only a few seconds, which satisfies the performance condition, there is a high likelihood that SQL Server Agent will fail to see the spike; the alert will not fire. An alert is firing, but the responsible operator is not receiving notification.
Check the operator and notification information to ensure that you have entered the correct e-mail, pager, and net send addresses.
Test the e-mail, pager, and net send addresses.
Check the operator’s on-duty schedule.
Check the SQL Server Agent error log for any e-mail problems.
An alert is firing, but the notification is not timely.
The probable causes for this include: The Delay between responses setting for the alert is too high.
The alert response is complex, requiring many operator notifications.
Note Send notifications to as few operators as possible. For example, send notifications to one group e-mail address rather than notifying several individual operators. This error appears in the SQLServerAgent error log on Windows 95 or Windows 98 servers: "The common event system is being restarted after function ProduceEventsFromSS returned error 44, ‘Unable To Connect’"
This may indicate incorrect registered server information. Ensure that the registered server information for the local server is correct and that the registered login name is a member of the sysadmin fixed server role. The Windows 2000 or Windows NT application log fills rapidly with the same error.
The CPU usage is high.
The number of alert responses is high.
Because SQL Server Agent both depends on and monitors SQL Server, SQL Server Agent can become caught in an endless loop of firing the same alert. This generally occurs when SQL Server runs out of an essential global resource and an alert has been defined on this event. When the number of alerts raised exceeds the SQL Server Agent alert processing rate, a backlog is created. To eliminate an alert processing backlog Increase the amount of time in the Delay between responses setting.
Correct the global resource problem to prevent recurring alerts from using all your resources.
Configure an error so that it does not generate an alert.
Important Configuring an error to not generate an alert can be performed only within the registry. This solution should be used only as a last resort. Clear the Windows NT application log if: the backlog is not clearing, you do not want to wait for SQL Server Agent to clear the backlog, or you want an empty, unpopulated Windows NT application log.
Caution Clearing the Windows NT application log using the Clear All Events option on the Log menu deletes all events from the error log, including events unrelated to SQL Server. To configure an error to not generate an alert Start the Registry Editor.
Locate the following registry key:
HKEY_LOCAL_MACHINE
SOFTWARE
Microsoft
MSSQLServer
SQLServerAgent
NonAlertableErrors Type the error number.
The list of nonalertable errors can be a maximum of 1,024 characters, should not contain spaces, and items must be separated by commas (,). Any error number in the list that appears after the number 0 will generate an alert. For example, assume that the list consists of 1204,0,100 In this example, only error number 1204 does not generate an alert. Because error number 100 follows error number 0 in the list, it will generate an alert. Important Never remove the default nonalert-generating error, error 1204. Error 1204 defines those conditions known to lead to recursive alert generation. Removing this error will hamper attempts to resolve recursive alert generation. HTH Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Hi Luis,
I checked all the points mentioned in the article and they all are set according the suggestions above.The error message is written to the sql server and windows 2000 application log.Though there is one error message in sql server agent error log which says "[364] The Messenger service has not been started – NetSend notifications will not be sent".I doubt that this would be causing the problem.Also the last occured value for that alert says "Never Occured".

But the Messenger service is up or is down when you see application log?
Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Hi Luis,
The messenger service was turned off and I turned it on manually.I restarted the Sql Server Agent after that and the error message was gone.But the situation still remains the same.The error messages are still being written to sql server logs and windows application log but the alert is not being fired.

May refer to this threadhttp://www.mcse.ms/archive97-2003-11-118128.html for information on setup. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Try sending the alert manually using net send to the machine where you want the alert to be sent. Can you do it? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Hi Gaurav,
I am sending out alerts to an Operater.Yes I can send out emails and pager messages to that operator manually and through jobs.

Then set the alerts as specified in BOL. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Did exactly the same.Doesnt work for some reason.Can somebody please try it on their local machine and see if it works.I did a google and found that a similar problem was reported on some other website by another user but it had no solution.

Try reinstalling last services pack.
Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

]]>