SQL Servers and databases are probably the most critical IT parts of an enterprise. The most important and confidential data are stored there and even a potential issue with that data can be business critical. In situations where significant preventive/protective actions are applied, such measures will not be of any help in determining the cause of an incident when it occurs. For that purpose, establishing the SQL Server continuous auditing and especially being informed, timely of security configuration changes have been of the utmost importance. Let us learn about Security Auditing.
SQL Server security auditing with ApexSQL Audit ensures the early or immediate detection of any SQL Server security changes allowing the DBAs to prevent it to cause an unwanted security breach or even worse potential data loss, interruption or hindered server operations. Being informed, timely about any security issue or even about the potential issue is not important only when immediate actions are required, but also for continuous auditing purposes.
In order to fulfill not only basic auditing requirements and to ensure the granular and precise auditing, ApexSQL Audit is featured with two types of auditing filters: Simple and Advanced.
Even though it is named “Simple” for its ease of use, this is quite a comprehensive filter which allows a high level of granularity in filtering. In situation when SQL Server security auditing is required, the following security related events can be specified for auditing at the server and database level.
Simple filter
Server level security related events:
Additionally, there is an easy option to include or exclude specific login from auditing using the Logins filter when such requirement exists (excluding the trusted users for example)
Database level security related events:
ApexSQL Audit’s simple filter allows specifying SQL Server security auditing requirement for each security operation independently, but what’s more ApexSQL Audit allows specifying filtering conditions for all individual SQL Server auditing operation on both, server and database levels.
With a few simple mouse clicks, the desired SQL Server security auditing can be set according to specific security requirements, and Simple filter is the fastest way for configuring auditing of any important auditing requirements.
Advanced filter
Even though it is highly granular, the simple filter still doesn’t allows meeting some fine tuning requirements imposed by some advanced users or specific auditing requirements, so ApexSQL Audit also has a highly granular filter that can meet even the most demanding SQL Server auditing/compliance requirements and auditing precision. The Advanced filter utilizes a logical set of operators that can be selected for defining the auditing filter condition.
The image below displays all data fields available in the advanced auditing filter:
Depending on the data field selected is, is not, contain and does not contain operators will be available for filtering. By using the advanced filter, user can create the auditing rule that will ensure any level of auditing precision required
The advance filter is actually interpreted as the standard logical expression in the background where indentation represents the bracket and grouped conditions are treated as enclosed between the brackets. There is no limitation in the number or depth of filtering rules used.
The Text data filter will additionally allow meeting some very specific requirements as it is capable to parse the actual T-SQL of for audited event and then to collect or filter out the audited event depending on the used condition
In the example above, the filtering condition will audit and log only the alter login and create login events which doesn’t fulfill the company rule that Enforce password policy and Enforce password expiration must be enabled for these events, so DBA can be timely notified and take necessary steps to fix that.
As already stated, it is very important and usually required to be informed timely about any SQL Server security auditing issue or event. In order to meet such requirement, ApexSQL Audit provides the capable alerting system that consists of:
- Built-in system alerts
- User defined data alerts for alerting on captured events
- Custom script alerts for alerting on any value that can be retrieved via SQL script
ApexSQL Audit utilizes the true real time alerting since alerting engine will intercept and process the audited event, according to defined alerting condition even before the information is stored in the central repository database, allowing user instant notification when alert is raised.
Some highlights of the ApexSQL Audit alerting system:
- Fully customizable alert name and notification options (user definable text of the alert name, subject and body text)
- Alerting engine utilize the same advanced filter engine used for the auditing filter which allows very precise alerting conditions to be created
- Allows alert email notification with ability to define individual recipient email address for each alerting condition created
- Unlimited number of created alerts
- Editing of built-in system alerts to adjust them according to individual needs
- Editing, removing, disabling or deleting of user created alert
- Alert history viewer with unlimited number of stored alerts
- Detail information in alert body about the event
All collected and stored SQL Server security auditing events should be presented in a human readable form for any internal purpose or on request of an auditor. Besides being able to ensure reporting ability within the standard GUI, ApexSQL Audit features the Web based reporting ability. Web reports allows full reporting ability from any computer within the local network using the standard web browser. Both, the GUI, and Web-based reports are equally capable and equipped. Regardless of whether user decides to use the GUI or web reports, there are two types of reporting –common reports and custom reports.
Common reports consist of 11 predefined basic reports including the comprehensive filtering abilities, which ensures the quick and easy preview of collected events but also the precise reporting on required audited events
The security related reports are Security configuration history, Logon activity history, Permission changes and Unauthorized access
For those who wish more from reporting, ApexSQL Audit has built-in custom reports feature that grants the full freedom in creating and organizing of reporting on collected during the SQL Server auditing. Again, the advanced filter form is used here as well (it is consistent through all parts of application) making any customization in ApexSQL Audit custom reports or any other part of application to be easy and consistent. Custom reports allow creating the precise filtering condition that can be saved and reused whenever it is needed
There is no limitation in how many reports user can create and save, which allows creating the general overview, thematic or very precise reports that matches very specific requirements in just a few mouse clicks
With ApexSQL Audit, SQL Server security auditing, but also any SQL Server auditing and compliance has never been more reliable, easier and simple. There’s quite a bit more to ApexSQL Audit than what’s outlined here, but this easily show how ApexSQL Audit is capable of meeting most of auditing concerns directly out of the box.
ApexSQL Audit shares other ApexSQL solutions easiness of installation, setup and administration. ApexSQL Audit is focused on ensuring requirements for easy and comprehensive auditing and compliance for SQL Server. The most impressive feature of ApexSQL Audit is the fact that it is a lightweight, it uses a central repository database with the built-in tamper proofing mechanism and unprecedented ability to achieve high precision auditing, alerting, and reporting.
Reference: Pinal Dave (https://blog.sqlauthority.com)
