Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Automate collection and saving of failed logins for SQL Server


By:   |   Read Comments (3)   |   Related Tips: More > Auditing and Compliance

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

I re-cycle my SQL Server log every night using sp_cycle_errorlog. However, before I do, I would like to capture all of the failed logins recorded. I have auditing turned on for failed logins, but I want to make sure that I capture those events into a table so I can report on. How can I do this?

Solution

There is an undocumented extended stored procedure called xp_readerrorlog which will do the trick. How to use it for SQL Server 2005/2008 was covered in a previous tip. We can use xp_readerrorlog, in combination with a temporary table to extract the login failure information before cycling the error log. First, we need to create the permanent table which will hold our results:

CREATE TABLE dbo.FailedLogin ( 
   EventID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, 
   EventDate DATETIME NOT NULL, 
   LoginName sysname NOT NULL, 
   ClientName NVARCHAR(255) NULL 
); 
GO 

Then, using the information on xp_readerrorlog, we'll need to specify the first 3 parameters. The values we'll use are:

  • Parameter 1: 0 - Signifies the current log file
  • Parameter 2: 1 - Tells SQL Server we want the SQL Server error log, not the SQL Server Agent log.
  • Parameter 3: 'Login Failed' - Tells SQL Server we're looking for log entries with this string in them.

The extended stored procedure will return three columns which we must account for. They are:

  • LogDate  - When the event occurred.
  • ProcessInfo - What generated the event.
  • Text - The logged event itself.

To handle the incoming information, we'll put the information into a temporary table. We'll query from the temporary table and insert the results into our FailedLogin table that we've previously created. That query will look a bit convoluted because we'll need to do string manipulation to get just the login name and the client name. Note that while I'm using the parameter for xp_readerrorlog which should filter for Login Failed, I'm still going to qualify the SELECT from the temporary table to be sure. Putting it all together, we get the following results:

CREATE TABLE #ErrorLog ( 
  LogDate DATETIME, 
  ProcessInfo NVARCHAR(255), 
  LogText NVARCHAR(MAX) 
); 
GO 

INSERT INTO #ErrorLog ( 
   [LogDate], 
   [ProcessInfo], 
   [LogText] 
)  
EXEC xp_readerrorlog 0, 1, 'Login Failed'; 

INSERT INTO dbo.FailedLogin 
(EventDate, LoginName, ClientName) 
SELECT LogDate,  
  SUBSTRING(LogText, CHARINDEX('''' , LogText) + 1, CHARINDEX('''', LogText, CHARINDEX('''' , LogText) + 1) - CHARINDEX('''' , LogText) - 1) AS LoginName, 
  SUBSTRING(LogText, CHARINDEX('[CLIENT', LogText) + 9, CHARINDEX(']', LogText) - CHARINDEX('[CLIENT', LogText) - 9)  AS ClientName 
FROM #ErrorLog 
WHERE ProcessInfo = 'Logon' 
  AND CHARINDEX('Login failed', LogText) > 0; 

DROP TABLE #ErrorLog; 
GO 

And then if we query our FailedLogin table, we'll see all the failed logins recorded since the error log was previously cycled. An example is shown below:

Output from FailedLogin table

Therefore, since we can extract the information correctly out of the SQL Server log, we can use the query above and put it as a job step immediately prior to cycling the error log. Then it's a simple matter of querying FailedLogin for any reporting which we need to do.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, May 28, 2009 - 1:24:50 PM - ALZDBA Back To Top

 Nice solution for everyone using sql 7.0 or sql2000.

As from sql2005, IMO it is better to use SQLServer events.

It is as easy as this and it gives you more info !

/*

DBA_FailedConnectionTracker: Maintain an overview of which connections are made to this SQLServer instance

-- This implementation user SQLServer Service Brocker with Event Notifications

*/

USE DDBAaudit;

if object_id('dbo.T_DBA_FailedConnectionTracker') is null
begin
    print 'Table [T_DBA_FailedConnectionTracker] Created';
    CREATE TABLE [dbo].[T_DBA_FailedConnectionTracker](
        [host_name] [varchar](128) NOT NULL,
        [program_name] [varchar](128) NOT NULL,
        [nt_domain] [varchar](128) NOT NULL,
        [nt_user_name] [varchar](128) NOT NULL,
        [login_name] [varchar](128) NOT NULL,
        [original_login_name] [varchar](128) NOT NULL,
        [client_net_address] [varchar](48) NOT NULL,
        [Database_Name] [varchar](128) not null,
        [tsRegistration] datetime NOT NULL default getdate(),
        [FailedLoginData] XML
            ) ;
    Create clustered index clX_DBA_FailedConnectionTracker on [dbo].[T_DBA_FailedConnectionTracker] ([tsRegistration]);
    Create index X_DBA_FailedConnectionTracker on [dbo].[T_DBA_FailedConnectionTracker] ([login_name], [program_name]);
        
end



/*
source using SSB : http://www.simple-talk.com/sql/sql-server-2005/logon-triggers/
*/


-- Enable Service Broker for DDBAaudit database if it's the case
IF EXISTS( SELECT *
            FROM sys.databases
            WHERE [name]=N'DDBAaudit'
            AND is_broker_enabled = 0      )
Begin
    print 'SSB enabled';
    ALTER DATABASE DDBAaudit SET ENABLE_BROKER;
END

-- We will access from the activated stored procedure a view that is
-- located in a different database
-- the sys.dm_exec_sessions dynamic management view
-- The security context of the stored procedure would not allow us to do so
-- unless we set the the TRUSTWORTHY option to ON.
-- Why and another method you can find here:
-- http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx

IF EXISTS( SELECT *
            FROM sys.databases
            WHERE [name]=N'DDBAaudit'
            AND is_trustworthy_on = 0      )
Begin
    print 'trustworthy switched to ON';
    ALTER DATABASE DDBAaudit SET TRUSTWORTHY ON;
END


-- Create a queue
CREATE QUEUE Q_FailedLogon_Triggers_Queue;

-- Create a service
CREATE SERVICE S_FailedLogon_Triggers_Service
    ON QUEUE Q_FailedLogon_Triggers_Queue([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);


-- Create a route
CREATE ROUTE R_FailedLogon_Triggers_Route
    WITH SERVICE_NAME = N'S_FailedLogon_Triggers_Service'
        , ADDRESS = N'LOCAL';
go

/* current user get ownership of EVENT NOTIFICATION, so switch to 'sa' */
EXEC AS LOGIN = 'sa';
go
-- Create the event notification at the server level for the AUDIT_LOGIN event
CREATE EVENT NOTIFICATION N_Failed_Login_Notification
    ON SERVER FOR AUDIT_LOGIN_FAILED
        TO SERVICE 'S_FailedLogon_Triggers_Service', 'current database';

go
/* Switch back to original user */
REVERT;
GO

-- Create the stored procedure that will handle the events
-- First set the options required to work with the XML data type
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

CREATE PROCEDURE dbo.spc_DBA_FailedConnectionTracker
AS
BEGIN
SET NOCOUNT ON;
-- Use an endless loop to receive messages
WHILE (1 = 1)   
BEGIN
      DECLARE @messageBody VARBINARY(MAX);     
      DECLARE @messageTypeName NVARCHAR(256);          
      WAITFOR (
                 RECEIVE TOP(1)                   
                 @messageTypeName = message_type_name,                   
                 @messageBody = message_body                   
                 FROM Q_FailedLogon_Triggers_Queue                
                 ), TIMEOUT 500 
    -- If there is no message, exit      
    IF @@ROWCOUNT = 0        
    BEGIN         
        BREAK ;        
    END ;  
    -- If the message type is EventNotification do the actual work         
    IF (@messageTypeName = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification')
    BEGIN
        DECLARE @XML XML,
            @host_name varchar(128) ,
            @program_name varchar(128) ,
            @nt_domain varchar(128) ,
            @nt_user_name varchar(128) ,
            @login_name varchar(128) ,
            @original_login_name varchar(128) ,
            @client_net_address varchar(48) ,
            @Database_Name varchar(128) ,
            @ts_logon datetime,
            @SPID VARCHAR(5);   
            
        SELECT @XML=CONVERT(XML,@messageBody)
            ,@host_name = ''
            ,@program_name = ''
            ,@nt_domain = ''
            ,@nt_user_name = ''
            ,@login_name = ''
            ,@original_login_name = ''
            ,@client_net_address =''
            ,@SPID ='';   
     
        -- Get the SPID and the Login name using the value method        
        SELECT @SPID = @XML.value('(/EVENT_INSTANCE/SPID)[1]', 'VARCHAR(5)')         
            , @ts_logon = @XML.value('(/EVENT_INSTANCE/StartTime)[1]', 'NVARCHAR(128)')
            , @host_name = @XML.value('(/EVENT_INSTANCE/HostName)[1]', 'NVARCHAR(128)')
            , @program_name = @XML.value('(/EVENT_INSTANCE/ApplicationName)[1]', 'NVARCHAR(128)')
            , @nt_domain = @XML.value('(/EVENT_INSTANCE/NTDomainName)[1]', 'NVARCHAR(128)')
            , @nt_user_name = @XML.value('(/EVENT_INSTANCE/NTUserName)[1]', 'NVARCHAR(128)')
            , @original_login_name = @XML.value('(/EVENT_INSTANCE/SessionLoginName)[1]', 'NVARCHAR(128)')
            , @login_name = @XML.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(128)')
            , @Database_Name = @XML.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(128)')
            ;     
            


            INSERT INTO [dbo].[T_DBA_FailedConnectionTracker]
                           ([host_name], [program_name], [nt_domain], [nt_user_name], [login_name], [original_login_name], [client_net_address], [Database_Name], [tsRegistration], [FailedLoginData] )
                    values ( @host_name, @program_name, @nt_domain, @nt_user_name, @login_name, @original_login_name, ISNULL(@client_net_address,''), ISNULL(@Database_Name,''), @ts_logon, @XML);

        /*
        Select *
        from dbo.T_DBA_FailedConnectionTracker
        order by [tsRegistration] desc
        */
        
    END;
  END;
END;

GO
if object_id('dbo.spc_DBA_FailedConnectionTracker') is not null
begin
    -- Link the stored procedure to the Q_FailedLogon_Triggers_Queue
    ALTER QUEUE Q_FailedLogon_Triggers_Queue    
    WITH STATUS=ON
        ,   ACTIVATION ( STATUS=ON
                        , PROCEDURE_NAME = dbo.spc_DBA_FailedConnectionTracker
                        , MAX_QUEUE_READERS = 1
                        , EXECUTE AS SELF) ;

end
ELSE
BEGIN
    RAISERROR ('DBA Message: SSB Queue FailedConnectionTracker NOT Activated !!! ', 1,1 ) WITH log

END
/*
The information posted to the S_FailedLogon_Triggers_Service, about the AUDIT_LOGIN event, has the following structure:

<EVENT_INSTANCE>
  <EventType>AUDIT_LOGIN_FAILED</EventType>
  <PostTime>2009-02-05T13:21:12.640</PostTime>
  <SPID>56</SPID>
  <TextData>Login failed for user 'ikke_test'. [CLIENT: &lt;local machine&gt;]</TextData>
  <DatabaseID>1</DatabaseID>
  <NTUserName />
  <NTDomainName />
  <HostName>WSAA098002</HostName>
  <ClientProcessID>5456</ClientProcessID>
  <ApplicationName>Microsoft Data Access Components</ApplicationName>
  <LoginName>ikke_test</LoginName>
  <StartTime>2009-02-05T13:21:12.637</StartTime>
  <EventSubClass>1</EventSubClass>
  <Success>0</Success>
  <ServerName>Mytestserver\SQL2005DE</ServerName>
  <Error>18456</Error>
  <DatabaseName>master</DatabaseName>
  <RequestID>0</RequestID>
  <EventSequence>16981</EventSequence>
  <IsSystem />
  <SessionLoginName>ikke_test</SessionLoginName>
</EVENT_INSTANCE>

event_type is obviously AUDIT_LOGIN.
post_time contains the time when the event is posted to the service.
spid represents the ID of the session for the event.
text_data contains a semicolon-delimited list of all set options.
binary_data contains the session level settings, including ANSI nulls, ANSI padding, cursor close on commit, null concatenation, and quoted identifiers.
database_id is the ID of the default database or the ID of the database used by the USE database statement if any.
nt_user_name represents the Windows user name.
nt_domain_name represents the Windows domain to which the user belongs.
host_name contains the name of the computer on which the client is running.
client_process_id is the ID assigned by the host computer to the process where the client application is running.
application_name is the name of the client application.
login_name is the name of the login used.
start_time represents the time that the event started.
event_subclass indicates if the connection is pooled or non-pooled with values 1 for non-pooled and 2 for pooled.
success element indicates if the authentication succeeded and has the values 1 for success and 0 for failure. For the AUDIT_LOGIN event it will always contain value 1.
integer_data represents the network packet size.
server_name represents the name of the instance of SQL Server on which the event occurred.
database_name is the name of the database
login_sid contains the security identification number (SID) of the logged-in user.
request_id represents the ID of the request.
event_sequence represents the sequence of the event within the request.
is_system indicates if the event occurred for a user or a system process. In this case it would always be a user process.
*/

 


Wednesday, May 27, 2009 - 3:54:14 PM - K. Brian Kelley Back To Top

It depends...

 Some of our servers require it nightly because of the amount of information they log. Opening a single log would be painful otherwise. Of course, you can increase the # of logs being retained. On those servers we've done just that.

 


Wednesday, May 27, 2009 - 1:27:17 PM - jerryhung Back To Top

I think cycling the error log DAILY is a bit excessive (I do it for 2 months or monthly)

There are so much info we can get from the error log and I like to read them in as well :)


Learn more about SQL Server tools