30 July 2013

Failed SQL Server logins

by Redgate Monitor Team

31,047 0 Auditing, Compliance

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 Redgate Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, Redgate 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:

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>

1 2	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>

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:

EXEC sys.xp_readerrorlog 0, 1, N'Login', N'failed', @starttime, @endtime;

1	EXEC sys.xp_readerrorlog 0, 1, N'Login', N'failed', @starttime, @endtime;

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

EXEC sys.xp_ReadErrorLog 0, 1, N'DBCC', N'TRACEON', @starttime, @endtime;[

1	EXEC sys.xp_ReadErrorLog 0, 1, N'DBCC', N'TRACEON', @starttime, @endtime;[

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.

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


		
			
			
Transact-SQL
			
			DECLARE @endtime DATETIME;
SET @endtime = GETDATE();

DECLARE @starttime DATETIME;
SET @starttime = DATEADD(hh, -1, @endtime);

IF OBJECT_ID('tempdb..#LogEntries') IS NOT NULL
    DROP TABLE #LogEntries;

CREATE TABLE #LogEntries
    (
      LogDate DATETIME ,
      ProcessInfo VARCHAR(1000) ,
      LogMessage TEXT
    );

INSERT  INTO #LogEntries
        EXEC sys.xp_readerrorlog 0, 1, N'Login', N'failed', @starttime, @endtime;
    

SELECT  COUNT(*)
FROM    #LogEntries;

DROP TABLE #LogEntries;
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
				
						DECLARE @endtime DATETIME;
SET @endtime = GETDATE();
 
DECLARE @starttime DATETIME;
SET @starttime = DATEADD(hh, -1, @endtime);
 
IF OBJECT_ID('tempdb..#LogEntries') IS NOT NULL
    DROP TABLE #LogEntries;
 
CREATE TABLE #LogEntries
    (
      LogDate DATETIME ,
      ProcessInfo VARCHAR(1000) ,
      LogMessage TEXT
    );
 
INSERT  INTO #LogEntries
        EXEC sys.xp_readerrorlog 0, 1, N'Login', N'failed', @starttime, @endtime;
    
 
SELECT  COUNT(*)
FROM    #LogEntries;
 
DROP TABLE #LogEntries;

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:	0
Medium:
Low:

Raise an alert when the threshold is passed for

1 collection

Alert is

Enabled

31,047 Rate

Click to rate this post!

[Total: 6 Average: 4.2]

Failed SQL Server logins

Metric definition

Name

Description

The T-SQL query that will collect data

Instances to collect from

Databases to collect from

Collection frequency

Use collected or calculated values

Metric collection

Alert definition

Alert name

Description

Raise an alert when the metric value goes

Default threshold values

Raise an alert when the threshold is passed for

Alert is

What are custom metrics?

What is Redgate Monitor?

Featured custom metrics

Total database file size

Number of active backups and restores

Full backup to odd path

Find metrics