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