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


Auditing Failed Logins in SQL Server

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

Quickly Resolve Performance Problems for IIS, .NET and SQL Server       >>>   Get Started


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?


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

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     

Wednesday, November 22, 2017 - 3:42:58 AM - hendra Back To Top
Tip Comments Pending Approval

Wednesday, November 22, 2017 - 3:39:22 AM - hendra Back To Top
Tip Comments Pending Approval

Sunday, April 23, 2017 - 11:49:21 AM - MichaelBuivY Back To Top

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



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




Wednesday, February 05, 2014 - 7:37:43 PM - David Shink Back To Top


                      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.


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

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,


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

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
    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]);

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
            FROM sys.databases
            WHERE [name]=N'DDBAServerPing'
            AND is_broker_enabled = 0      )
    print 'SSB enabled';

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

            FROM sys.databases
            WHERE [name]=N'DDBAServerPing'
            AND is_trustworthy_on = 0      )
    print 'trustworthy switched to ON';

-- 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';

/* current user get ownership of EVENT NOTIFICATION, so switch to 'sa' */
-- Create the event notification at the server level for the AUDIT_LOGIN event
        TO SERVICE 'S_FailedLogon_Tracker_Service', 'current database';

/* Switch back to original user */

-- Create the stored procedure that will handle the events
-- First set the options required to work with the XML data type

CREATE PROCEDURE dbo.spc_DBA_FailedConnectionTracker
-- Use an endless loop to receive messages
WHILE (1 = 1)   
      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       
        BREAK ;       
    END ; 
    -- If the message type is EventNotification do the actual work        
    IF (@messageTypeName = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification')
            @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

if object_id('dbo.spc_DBA_FailedConnectionTracker') is not null
    -- Link the stored procedure to the Q_FailedLogon_Tracker_Queue
    ALTER QUEUE Q_FailedLogon_Tracker_Queue   
                        , PROCEDURE_NAME = dbo.spc_DBA_FailedConnectionTracker
                        , MAX_QUEUE_READERS = 4
                        , EXECUTE AS SELF) ;

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

The information posted to the S_FailedLogon_Tracker_Service, about the AUDIT_LOGIN event, has the following structure:

  <TextData>Login failed for user 'ikke_test'. [CLIENT: &lt;local machine&gt;]</TextData>
  <NTUserName />
  <NTDomainName />
  <ApplicationName>Microsoft Data Access Components</ApplicationName>
  <IsSystem />

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.



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

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

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 06, 2009 - 9:04:16 AM - wwphx Back To Top

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

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

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/

Learn more about SQL Server tools