Failed SQL Server logins

| 0 comments | 10,266 views
Categories:

VN:F [1.9.17_1161]
Rating: 5.0/5 (3 votes cast)

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>

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;

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;

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, 5.0 out of 5 based on 3 ratings

Metric definition

Metric 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.
Enter the T-SQL query that will collect data:
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;
Note: This T-SQL reads the last hour of data from the current log file. If your log file rolls over more frequently than every hour, you'll need to update the SQL statement to match that frequency. For example, if the file rolls over every 30 minutes, you'd change hh, -1 to mi -30.
Select instances to collect from:
Select all
Choose databases to collect from:
Specify databases and type
master
Set collection frequency:
Collect data every hour
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 turned off
Metric collection is:
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 thresholds
Default threshold values:
Set the thresholds as follows:
High:1
Medium:Disabled
Low:Disabled
Raise an alert when the threshold is passed for:
Enter 1 collection
This alert is:
Enabled
VN:F [1.9.17_1161]
Rating: 5.0/5 (3 votes cast)