Auditing Failed Logins in SQL Server

By:   |   Comments (12)   |   Related: > Auditing and Compliance


Problem

An auditor has taken a look at our SQL Servers and has told us that we need to audit login failures to the SQL Servers themselves. How do we do this?

Solution

SQL Server permits the auditing of both login successes and failures, depending on your need. The way to turn this auditing on is by using SQL Server Management Studio. Connect to the SQL Server in Object Explorer and then right-click on the SQL Server and choose the Properties option from the pop-up menu. You should see the server properties like so:

Server Properties

You'll want to click on the Security page (highlighted in the previous image) which will bring you to where you can set the login auditing:

Security Page

There are four options available:

  • None - Neither successful nor failed logins will be audited.
  • Failed logins only - Failed logins will be audited, but successful logins will be ignored.
  • Successful logins only - Successful logins will be audited, but failed logins will be ignored.
  • Both failed and successful logins - Login will be audited regardless of success and failure.

When you've made your choice, click the OK button. The setting for login auditing is actually a registry entry which differs based on SQL Server version and SQL Server instance name. SQL Server only reads this setting when the SQL Server service starts up. Therefore, for this setting to take effect, you'll need to restart the SQL Server service. Because it is a registry setting, the only easy way to set how to audit is through the GUI. There isn't a way using T-SQL without using one of the extended stored procedures that hits the registry directly.

A word about the various settings. At the very least, you should be auditing failed logins on production systems. There may be cases where it is necessary to audit successful logins, but realize that auditing successful logins will likely generate a lot of events to sift through. On extremely sensitive systems it is important to have this level of control. However, on less critical systems, you may be better served to only audit failed logins so you don't get overwhelmed with the number of events. If you have a log management solution, by all means audit both.

Once you've turned on auditing, the next step is to be able to find the results of said auditing. The first place where the audit information is recorded is the SQL Server log. For instance, an audit success by the "sa" account and an audit failure are shown in the following excerpt:

SQL Server log - auditing

If you want to view the contents of the SQL Server log by using T-SQL, there's an undocumented (but well known) extended stored procedure called xp_readerrorlog. You can use it to dump the results of the error log to a recordset by:

EXEC xp_readerrorlog;

To read further back, you can give this extended stored procedure an integer parameter which corresponds to the order of the SQL Server log. 0 represents the current log, with each number after that referring to the next one back. So to see the contents of the 3rd log (including the current one) you would pass a parameter of 2 (counting up from 0 - 0, 1, 2 would be the third), you would execute:

EXEC xp_readerrorlog 2;

To search the current error log and only return failed logins you can use the following command.  The first parameter specifies the error log (0=current), the second parameter specifies the type of log (1=SQL Error Log) and the third parameter specifies the message to search for.

EXEC sp_readerrorlog 0, 1, 'Login failed'

If you have log management software, another good place to look is the Application event log for the operating system. You can view this by using the Computer Management tool. If you expand System Tools and then Event Viewer, you'll see the Application event log like so:

Computer Management - App Event Log

If you look in this event log, you'll be looking for events with a source of MSSQLSERVER or MSSQL$<Instance Name>. For instance, the following shows the audit success and failure events we looked at before in the SQL Server log:

App Event Log - SQL Server Login Events

Successful logins for SQL Server 2005 and 2008 will have an event ID of 18454 and failed logins will have an event ID of 18456. SQL Server 2000 uses the same event ID for both, making it impossible to determine of the event signifies a success or failure without looking at the event details. As a result, I would recommend only auditing failures to eliminate the confusion. Once you have determined the correct events, you can look at the event log details to see what login was attempted. For instance, here is the failed login attempt:

Event - SQL Server Failed Login

and here is the successful login attempt:

Event - SQL Server Login Success

The details of what account was attempted is stored within the description of the event log. Therefore, if you need to automate the retrieval of audit successes or failures, make sure the application can retrieve the details and parse the text accordingly.  

Next Steps
  • As mentioned auditing failed logins should be a normal process for your production systems
  • Audit successful logins only when the data is necessary otherwise you will have a bloated error log
  • Take a look at this tip on how to cycle your error logs


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Sunday, December 17, 2017 - 9:54:10 AM - dan kauffman Back To Top (74186)

WHAT IS THE COST IN TERMS MACHINE AND SERVER RESOURCES EATUP UP BY 24/7 "WAR-DIALING"

(CPU/MEM/SQL SERVER INTERNALS ETC.)

I am curious about the resources tied up and overall performance impact of 10s of war dialers attempt to loging to SQL, RDC, FTP.  I am not sure the questions to ask specifically. For instance I grab a large pool of connections and allow web server to keep them idling.  Do the hacker bots hold connecton the instant when the authenetication is attempted, or longer?

 

What resources are associated w/ each failed attempt? So HDD if it is writing the the log, some Network.. If 3 attempts are made each and every does that translate in to KB of RAM? Cycles of CPU?

 

What I am really asking is, aside from the chance of guessing password what other "costs" are there? 

Does it make sense to  have the event log to script firewall rules blocking repeated IPs? 

 

Interestingly, 2x more attempts made on SQL:1433 than  to FTP :21 and 5x more than the remoe login attempts via RDC:3389. 

Really appreciate knowledge of these stats, how I might monitor  perofrmance to find results myself, and if it is worth blocking IPs, or if that will be as costly to our server.

 

THANKS IN ADVANCE.

 

Wednesday, November 22, 2017 - 3:42:58 AM - hendra Back To Top (70078)

My sql server log was more since i opened as public. There were unauthorized user tried to login sa.

How to prevent it ?

Thank you

 

 

Sunday, April 23, 2017 - 11:49:21 AM - MichaelBuivY Back To Top (55112)
https://www.youtube.com/watch?v=0LtqsQsY3wg

Friday, November 13, 2015 - 12:18:25 AM - udhayaganesh Pachiyappan Back To Top (39061)

Hi,

 

Nice post this. Let me know how to know identify the user is pretening to access the 'X' database.

 

Thanks

Udhai

Wednesday, February 5, 2014 - 7:37:43 PM - David Shink Back To Top (29353)

                      Brian:

                      I am a SQL Server newbie.  I was given the task of turning on login failure auditing.  I didn't want to admit I didn't know how.  I checked books on SQL Server 2005, & 2005 R2, but found nothing.  Your post gave me exactly what I needed to accomplish the task.  Most important of all, I learned something.

                        David

Tuesday, January 22, 2013 - 7:20:30 AM - Thomas Mucha Back To Top (21614)

Thanks for all the info on this topic. I was wondering if tyhere is a system table (in SQL 2000) and/or dmv(s) in 2005+ that hold these settings? I'm being audited and it would help if I could run a query rather than take lots of screenshots.

 

Thank you,

Tom

Sunday, December 12, 2010 - 3:36:35 PM - ALZDBA Back To Top (10450)

Apparently the link to simple talk nolonger works.

The new link is in my script

 

Here is the script I use to track failed login info using event notifications (2005/2008R2)

/*

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

-- This implementation user SQLServer Service Brocker with Event Notifications

*/

USE YourMonitoring db;

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/

they reorganized their site. this is the new url http://www.simple-talk.com/sql/t-sql-programming/logon-triggers/
*/


-- Enable Service Broker for DDBAServerPing database if it's the case
IF EXISTS( SELECT *
            FROM sys.databases
            WHERE [name]=N'DDBAServerPing'
            AND is_broker_enabled = 0      )
Begin
    print 'SSB enabled';
    ALTER DATABASE DDBAServerPing 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'DDBAServerPing'
            AND is_trustworthy_on = 0      )
Begin
    print 'trustworthy switched to ON';
    ALTER DATABASE DDBAServerPing SET TRUSTWORTHY ON;
END


-- Create a queue
CREATE QUEUE Q_FailedLogon_Tracker_Queue;

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


-- Create a route
CREATE ROUTE R_FailedLogon_Tracker_Route
    WITH SERVICE_NAME = N'S_FailedLogon_Tracker_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_Tracker_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_Tracker_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)')
            ;    
           
        --Select @program_name = case when upper(@program_name) like 'DATABASEMAIL - %'
        --                            then substring(@program_name, 1, charindex('<',@program_name,1) - 1 )
        --                            else @program_name
        --                            end

--        /* 'UABEPRD\EXECBTS' geef message GUID als application name mee !! register this user only once */
--        Update    dbo.T_DBA_FailedConnectionTracker
--            set [tsLastUpdate] = case when [tsLastUpdate] < @ts_logon then @ts_logon
--                                    else [tsLastUpdate]
--                                 end
--        Where [host_name] = @host_name
--            and case when upper([login_name]) like 'UABEPRD\EXECBTS%' then @program_name
--                    else [program_name]
--                    end = @program_name
--            -- and [program_name] = @program_name
--            and [nt_domain] = @nt_domain
--            and [nt_user_name] = @nt_user_name
--            and [login_name] = @login_name
--            and [original_login_name] = @original_login_name
--            and [client_net_address] = ISNULL(@client_net_address,'')
--            and [Database_Name] = ISNULL(@Database_Name,'')
--
--        if @@rowcount = 0
--        begin
            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);

--        end

        /*
        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_Tracker_Queue
    ALTER QUEUE Q_FailedLogon_Tracker_Queue   
    WITH STATUS=ON
        ,   ACTIVATION ( STATUS=ON
                        , PROCEDURE_NAME = dbo.spc_DBA_FailedConnectionTracker
                        , MAX_QUEUE_READERS = 4
                        , 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_Tracker_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>WS20098002</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>WS20098002\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.
*/


/*

GO

-- clean up only if needed

/* In case of Problems first drop the EVENT NOTIFICATION subscription */
--    DROP EVENT NOTIFICATION N_Failed_Login_Notification ON SERVER


--    ALTER QUEUE Q_FailedLogon_Tracker_Queue WITH STATUS=OFF
--    DROP EVENT NOTIFICATION N_Failed_Login_Notification ON SERVER
--    DROP ROUTE R_FailedLogon_Tracker_Route
--    DROP SERVICE S_FailedLogon_Tracker_Service
--    DROP QUEUE Q_FailedLogon_Tracker_Queue;
--    DROP PROCEDURE dbo.spc_DBA_FailedConnectionTracker
--

Sunday, December 12, 2010 - 2:15:11 PM - wwphx Back To Top (10449)

Kenneth, I don't think you can get that information directly as you're dealing with, first, access to the server, and then, once authenticated, permissions to the database.  I think the SQL Server log is not recording information at the second level.  I've never thought SQL Server did an adequate amount of logging.

Unfortunately the URL posted by ALZDBA is no longer working, but take a look at the MSSQLTips url that Grobido posted, you might get something to work out of that.  Otherwise you'll probably be looking at third-party tools, and I have no recommendation in that area.

Sunday, December 12, 2010 - 8:58:06 AM - Kenneth Winders Back To Top (10448)

I read K. Brian Kelley's article on auditing failed login attempts in SQL Server and it has some useful information. The issue I'm trying to resolve is that I have logged failed login attempts, but am having trouble determining which database the user was attempting to access during the login. Is there a trace, parameter, log setting or something that will help me find this piece of information? Thanks.

Wednesday, May 6, 2009 - 9:04:16 AM - wwphx Back To Top (3336)

I have always kept all of my instances set to Audit All, then use a Perl script that runs at 23:59 that brings over all log entries for the day, filtering out successful logins.  Makes failed logins stand out like a sore thumb.


Thursday, April 23, 2009 - 5:30:59 AM - grobido Back To Top (3237)

There is also another tip written by K. Biran Kelley about this as well.  You can find it here: http://www.mssqltips.com/tip.asp?tip=1627

 

Wednesday, April 22, 2009 - 11:24:17 PM - ALZDBA Back To Top (3235)

That is indeed the way we used it for years. It can also simply be picked up by monitoring solutions (e.g. hpovow) because they sniff the windows eventlogs.

Now, with sql2005(sp2)/2008 we also use sqlserver audit events to log failed logins into an audit table to gather as much as possible info regarding the login attempt.

cfr http://www.simple-talk.com/sql/sql-server-2005/logon-triggers/














get free sql tips
agree to terms