Audit failed SQL Server logins – Part 2 – using native tools to investigate failed logins

In the previous article of the Audit failed SQL Server logins series, we described the motives and most common methods used for unauthorized SQL Server access attempts. As a response, the best way to identify such attack attempts is to audit the failed logins

There are several native methods to capture and analyze failed SQL Server logins

  • The SQL Server Management Studio login auditing feature
  • The xp_readerrorlog procedure
  • The Windows event viewer

The SQL Server Management Studio login auditing feature

SQL Server allows auditing both successful and failed logins. To enable this type of auditing, use SQL Server Management Studio. Select a SQL Server instance in the Object Explorer and choose the Properties option from the context menu

This will open the Server Properties dialog. To set the Login auditing options, select the Security page

SSMS - Server Properties dialog

There are four options available:

  • None – Neither successful nor failed logins will be audited
  • Failed logins only – The failed logins will be audited, successful will be ignored
  • Successful logins only – The successful logins will be audited, failed will be ignored
  • Both failed and successful logins – All logins will be audited

To start auditing logins, the SQL Server instance must be restarted

It is recommended to audit only failed logins on production servers, to avoid logging a large number of events

Once auditing is turned on, captured information can be viewed using the Log File Viewer. Use the Filter option to narrow down the list to the logon source only

SSMS auditing - Log File Viewer

The list can be searched, filtered using the Filter option, or exported as the CSV, text or native log file

The xp_readerrorlog procedure

To review SQL Server error log files using T-SQL, there is an undocumented xp_readerrorlog extended stored procedure. It can be used to dump the content of the error log into a recordset. To dump the current SQL Server log, use:

EXEC xp_readerrorlog;

The undocumented xp_readerrorlog procedure

To read older error logs, pass an integer parameter to the procedure which corresponds to the order of the SQL Server log. 0 represents the current log, 1 the latest log, etc. To read the 4th log, execute:

EXEC xp_readerrorlog 3;

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

EXEC xp_readerrorlog 0, 1, ‘Login failed’

Searching the current error log and returning only failed logins

As SQL Server error logs may grow very large, they are often recycled via the sp_cycle_errorlog stored procedure, or automatically upon each SQL Server instance restart. In that case, captured failed logins should be extracted from the error logs and saved before the recycling

The Windows event viewer

The failed SQL Server logins can also be read in the Windows event viewer. They are shown in the operating system Application event log. However, if SQL Server login auditing is set to the None option, there will be no records about SQL Server failed login attempts in the Application event log either

SQL Server logins can also be read in the Windows event viewer

To find failed logins in the event log, use the Filter option and look for the events with MSSQLSERVER or MSSQL$<Instance Name>in the source

The listed events can be saved/exported into several file formats: the XML, CSV, TXT, and standard Windows event log format

As described, native solutions for investigating failed SQL Server logins are available, but the question is whether logs are available. The challenge with native logs, both SQL Server and Windows, is that they get recycled and are difficult to save automatically for later use and analysis. Furthermore, these logs are easily deleted by an intruder once his attack succeeds. In the next article, we’ll describe how ApexSQL Audit, a tool for auditing SQL Server instances, can be used to investigate failed SQL Server logins and how it offers some advantages over native tools

Useful resources:
Choose an Authentication Mode
Configure Your SQL Server Attack Surface
Rainbow Hash Cracking
Cheap GPUs are rendering strong passwords useless

This article is part of a series

See previous part        See all parts         See next part

October 10, 2013