Database auditing has become essential as the compliance regulations requirements and fines have amplified. It is important to address the questions not only on who and when accessed the data in your system or how the data has been changed but also how to prevent data breaches from inappropriate actions performed by different users.
Answering all the questions contextually to auditors during data supervising is significant for regular compliance audits. Therefore, it is important to have adequate data logging and protection arranged via systems that automatically track the activities in the business environment.
As diverse regulatory guidelines ask for proper requirements, accordingly, the same audit logs are differently valued depending on it. Auditors need reports that demonstrate relevant information that is easy to understand, investigate, and work with.
Native database auditing solutions and costs
There are various techniques to audit SQL Server databases to log audit trail. Nonetheless, depending on organization needs, native auditing often does not find a way to prevail as an adequate solution due to:
-
Performance degradation
Audited systems resources are often heavily consumed due to native auditing appliances, as the activity in production servers rolls over frequently the auditing mechanisms are almost doubling the workload of audited systems and resulting in increased CPU, memory, and I/O usage and response time
-
Compliance issues
Spoon-feeding the auditors with reports they need is a challenge. Auditing information is not captured in a format that can be easily used and there is no complete detailed information provided in the audit trail due to native solutions are built around database performance tuning and debugging.
Native database auditing logs lack security controls. The sensitive data might be stored twice, in database records and auditing log. Privileged users with the level of access they have can easily hurt data integrity, disable auditing in order to cover the tracks of any malicious activity on a database
-
Hidden costs
Due to a lack of functionalities to extract reports, security controls, alerting, configuring on multiple SQL Server instances, native tools are time-consuming because it requires to manually maintain all the customization and changes performed across all SQL Servers in the environment
In order to meet regulatory requirements, many native audit solutions are collecting entire database traffic which does not only mean inefficiency but also massive amounts of data to be stored and process. The data storage is another concern and cost to be timely considered especially since regulatory guidelines do require data retention logging, therefore more storage has to be allocated in order to make sure database auditing logs not to get rolled over due to insufficient space.
Out-of-the-box database auditing solution
At first sight, native database auditing solutions might seem like a good deal, but detailed investigation shows that it requires a huge amount of time for configuration and maintenance also as additional costs for resource allocation as was as lack of quality of life features and functionalities needed for an average database auditing job.
The easiest way to avoid hidden costs of native solutions is to leverage on an out-of-the-box auditing solution, ApexSQL Audit. ApexSQL Audit is built to easily configure, manage, and maintain auditing and also help to be compliant with multiple regulations at once as the database auditing requirements are extending over time.
This powerful tool is capable of capturing almost 200 SQL Server events, organized in several operations types, structure and data changes, access to data and objects, also as security operations. Audited data is kept in a tamper-evident central repository database, and complete configuration and reporting are done via easy to use and understand graphical interface. Let’s take a closer look at the general features that this tool makes a turn-key database auditing solution:
Ease of use
Complete ApexSQL Audit application management is performed via the application interface that helps to easily set up auditing configuration, run and schedule reports, manage alerts which enables hands-free auditing once the initial configuration is set up.
ApexSQL Audit demonstrates the importance of having a well-designed UX experience with a bunch of options and feature sorted in easy to understand and follow manner. In the main application ribbon, all the configurations are easily maintainable via different tabs grouped based on action types
Configuration and auditing features
ApexSQL Audit provides reliable, low/minimal performance impact auditing while leverages on different auditing mechanisms:
- SQL Traces – applicable on SQL Server versions 2005 and higher
- Extended Events – applicable on SQL 2012 version and higher
- SQL Audit – applicable on SQL 2017 version and higher
Configuration for multiple SQL Instances and databases is done in just a couple of clicks time as there are number of pre-defined configuration templates organized around several well-known compliance regulations that can be combined in order to meet multiple regulations at once
Also, auditing specification can be easily customized to meet very specific requirements and needs by manually filtering per SQL Server logins, applications, operations, database objects while simple selecting checkboxes to include or exclude certain objects in the filters.
For unlimited auditing criteria appliances, advanced filters is a stellar feature that allows user to specifically set up filters not only for auditing configurations, but also for reports and alerts
Reporting
Configuring database auditing and collecting data is one thing, but reporting is a key feature of a substantial auditing solution. Reports in ApexSQL Audit allow users to extract the data into several formats. The information is retrieved based on a filter criteria that can be easily customized or one of the pre-defined reporting templates can be used instead
The reporting information can be directly pulled into the application interface on demand to preview the data, or can be put to schedule to automatically run reporting jobs and forward reports via an e-mail to predefined recepients
Alerting
One of solid database auditing features is an alerting mechanism whenever a critical event occurs that can negatively impact production and raise the alarm to respective departments in an organization to take further action.
ApexSQL Audit brings several system built-in alerts that are focused on system information that can impact auditing tasks and bring user’s attention in time to resolve any issues like low disk space, connection issues, and similar. Custom alerts can be defined to raise an alert with e-mail notification whenever important or risk event occurs in SQL traffic.
Security and separation of duties
Privileged users with unlimited access to databases and audit logs without ability to trace their activities can result in compliance liabilities. Therefore, it is important to protect the audit and configuration data from any inadvertent or malicious actions performed by the users with administrative access.
All audit and configuration data is stored in a tamper-evident central repository that can be accessed and managed through the application interface. Security controls in ApexSQL Audit are introduced via
-
Application level security
While it is possible to access central repository from any workstation in the network via remote GUI interface, there are 3 different roles Administrator, Power User and Reader that are applicable for any domain user or group. Each application role defines certain level of access to auditing and configuration data
-
Tamper-evident measures
ApexSQL Audit ensures that each data entry in the central repository database has a unique hash while relying on SHA-256 algorithm which enables us to always be aware of potential tampering, and help investigating any tampering event occurrence. More details can be found on this tamper-evident design features article
Maintenance and data retention
Data retention requirements vary from compliance regulatory and in some cases may last up to a few years and having the ability to keep data retained and well organized gives much fewer headaches when it comes to data-keeping.
Considering the fact that in some cases data has to be kept for a longer time, the amount of data to be stored might be of high concern. ApexSQL Audit helps to keep the data well-organized via Archive schedule feature that creates an archive based on time frame or database size and can be automatically stored on any accessible storage in the network. As a bonus maintenance features, ApexSQL Audit also introduces database split and purge as powerful tools to delete or split databases to easier work on tight disk space when needed.
Summary
While native database auditing solutions are interesting due to cost-free solution characteristic they are only adding to workload while maintaining it without the ability to easily run and generate reports that are meaningful to auditors and have complete information on audit details on who, when, how accessed data. While the regulatory requirements are changing along with the DBAs responsibility growth in an enterprise organization, without a 3rd party database auditing solution it is almost impossible to keep auditing reliable, well-organized, secured, and retained.
May 6, 2020
