Previously we’ve discussed failed logins, how they can indicate unauthorized SQL Server access attempts (Audit failed SQL Server logins – distributed queries, brute force attacks, and SQL injections), and using native tools to audit the failed logins and identify potential attack attempts (Audit failed SQL Server logins – using native tools to investigate failed logins).
In this article, we’ll describe how ApexSQL Audit, a tool for auditing SQL Server instances, can be used to investigate failed SQL Server logins and what its advantages over native tools are.
ApexSQL Audit is SQL Server auditing and compliance tool that provides a wide range of possibilities for auditing access, changes, and security on SQL Server instances, databases, and objects. It can audit executed queries, warnings encountered on tables, stored procedures, functions, and views. All audited information is saved in a centralized auditing repository, and provided for reporting and analysis via centralized web-based reporting module.
Besides showing the reports in a web browser, you can export them into several common formats, such as PDF, Word or Excel.
ApexSQL Audit can recognize, capture, and parse (in order to add additional value to captured information) more than 230 SQL Server operations. It provides many built-in common reports divided into several groups, and a custom report designer. Failed logins are reported in the Unauthorized access report.
The Unauthorized access common report
Similar to native auditing tools, ApexSQL Audit must be installed and set up for auditing before the failed logins occur, in order to capture them. To enable auditing of failed logins, check the Security options for the audited SQL Server instance using the ApexSQL Audit GUI
For auditing of failed SQL Server logins, we’ll focus on the Unauthorized access report in the Security reports group
The Unauthorized access report consists of the Optional parameters section and the report grid
In order to narrow down the records shown, the following parameters can be used:
- Date range – specify a time frame when unauthorized access attempts occurred
- Server – limit the report to the specific SQL Server instance as ApexSQL Audit can audit multiple instances
- Login – specify the login you are particularly interested in to be investigated
- Client host – specify the client host used to access audited SQL Server instances
- Application – specify the application used to access audited SQL Server instances
Comparing to native SQL Server login auditing, the Unauthorized access report provides additional information:
- The application name used for an unauthorized access attempt. For example: “.Net SqlClient Data Provider”, “Microsoft SQL Server Management Studio”, or “ApexSQL Log”
-
The name of the client host (the computer hosting the application used for an unauthorized access attempt) that failed to access the audited SQL Server instance. For example, native SQL Server auditing will show:
[CLIENT:<local machine>] or [CLIENT: 192.168.1.108]
While ApexSQL Audit report will provide additional information:
LENOVO [CLIENT:<local machine>] or WORKSTATION_DEP1 [CLIENT: 192.168.1.108]
This particular addition to the failed logins report can, for instance, help investigate failed logins if remote computers used to access SQL Server were using dynamic IP addresses at the time.
In addition, ApexSQL Audit adds value to the auditing process with:
- A tamper-evident auditing information repository – a SQL Server database that can be archived and used for later reference or analysis. On the other hand, both SQL Server and Windows native logs are limited to the finite number of files, they get recycled, and are difficult to save automatically for later use and analysis
- One centralized repository for multiple audited SQL Server instances, with automatic transfers and loading into the centralized repository, that provides quick overview of all unauthorized login attempts via a web-based reporting module – this way it’s easy to spot attack attempts patterns, and determine attackers focus
- An out-of-the box solution for a number of compliance requirements (HIPAA, SOX, GLBA, etc.)
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
October 10, 2013
