Failed SQL Server logins

This metric returns the number of 'login failed' error messages found in the SQL Server error log file in the last hour.

Install metric...

Metrics install automatically if you have SQL Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, SQL Monitor, you can instantly install and run this metric on your servers.

This metric returns the number of ‘login failed’ error messages found in the SQL Server error log file in the last hour.

The T-SQL for this metric uses the undocumented stored procedure: xp_readerrorlog. Once you’ve been alerted to login failures, you can find out why they occurred by running the procedure against the SQL Server error log file and searching for text similar to this:

[sql]2013-07-31 04:56:08.34 Logon Error: 18456, Severity: 14, State: 5.
2013-07-31 04:56:08.34 Logon Login failed for user ‘<user name>’. [CLIENT: <ip address>[/sql]

The error state corresponds to a specific reason for failure, for example, state 5 means the user entered an invalid userid. For details of each state, see: http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx.

You can create additional custom metrics that read different messages in the SQL Server or SQL Agent error log file by reusing an edited version of this T-SQL. You’ll need to update the parameters for the procedure:

[sql]EXEC sys.xp_readerrorlog 0, 1, N’Login’, N’failed’, @starttime, @endtime;[/sql]

For example, to be alerted when the DBCC trace flag has been enabled in the last hour, replace the procedure with:

[sql]EXEC sys.xp_ReadErrorLog 0, 1, N’DBCC’, N’TRACEON’, @starttime, @endtime;[/sql]

Note: There is a known issue with using xp_readerrorlog in SQL Server versions earlier than 2008 R2. The command may stop responding during execution and cause CPU usage to increase to 100%. For more details and links to fixes from Microsoft, go to http://support.microsoft.com/kb/973524.

Failed SQL Server logins, 4.4 out of 5 based on 5 ratings

Metric definition

Name

Failed SQL Server logins

Description

This metric returns the number of 'login failed' error messages found in the SQL Server error log file in the last hour.

The T-SQL query that will collect data

Instances to collect from

Select all

Databases to collect from

master

Collection frequency

3600

Note: If you've updated the T-SQL to read from the file more frequently, you should update the metric collection frequency to match.

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

Enabled

Alert definition

Alert name

Failed SQL Server logins

Description

This alert is raised when SQL Monitor detects one or more failed SQL Server logins in the last hour.

Raise an alert when the metric value goes

Above the defined threshholds

Default threshold values

High:1
Medium:Disabled
Low:Disabled

Raise an alert when the threshold is passed for

1 collection

Alert is

Enabled