There are many SQL Server auditing solutions available on the market since the audit requirements are building more and more. Over time, we can see the increased number of both internal and external compliance standards which impose numerous auditing requirements for different business areas and models. More often than before, companies have to ensure they comply with two, three or even more SQL Server auditing standards at once, making it paramount to equip themselves with powerful and versatile auditing solutions that will allow many different features and ensure compliance with present requirements.
In this article, we are going to discuss several SQL Server auditing features and requirements that are important to access when searching for the best-suited SQL Server auditing solution for your environment.
Auditing features
Auditing mechanisms and capabilities
Naturally, the most important features of any SQL Server auditing solution are strong and capable auditing mechanisms the tool relies on which offer the following:
- Low/minimal performance impact
- Reliability
- Low/minimal intrusion to production servers
- Variety
In our example, let’s look at ApexSQL Audit, a SQL Server auditing tool capable of auditing almost 200 SQL Server events across numerous SQL Server instances within the domain, using different mechanisms:
- SQL Traces – available on all audited SQL Server instances starting with 2005 onwards
- Extended Events – available for SQL 2012 and higher
- SQL Audit – for SQL 2017 and higher
While SQL Traces are available on all supported SQL Server versions, they are most robust and have the highest performance impact between the 3. While this performance impact will not be shown for low or medium performance environments, it can become a thing to look more carefully in high or extreme performance environments where every bit of performance degradation can make a significant impact. Note that, in general, SQL Traces are practically minimal processing impact to the SQL Server itself, but in very large quantities, some performance impacts can be identified. Extended Events and SQL Audit technologies offer better performance since they are ‘cheaper’ to process on the SQL Server itself, but these technologies are not available on older SQL Server versions.
The second part of the “mechanisms and capabilities” section is reserved for auditing of before-after values on the table/column level. In many different compliance requirements, it is essential to audit actual values of the table field before and after the change, and to be able to identify who made the change, from where, how, and more – the essential requirement remains to see the “original” value and the “new” value on all DML operations (Insert, Update and Delete).
Again, in ApexSQL Audit, we can see how it leverages database triggers which fire on each insert, update or delete operations (based on the configuration set by the user) in order to capture values before and after the change as long as appropriate details on the executed operation:
One additional note when looking at configuration capabilities of the SQL Server auditing tool is granularity and precision. A solid tool must support both plug-and-play auditing configuration with only minimal user input requirement as well as advanced filtering capabilities which allow the highest precision and granularity filters which will allow configuring auditing in such way that some/all special cases can be included or excluded from auditing, like this:
What can (needs to) be audited
As mentioned above, it is important to be able to audit many different SQL Server events required by different compliance or internal standards. So, when choosing a SQL Server auditing solution, you should be first aware of which exact events must be audited in order to check if the proposed solution can indeed meet these requirements.
Looking at compliance standards like HIPAA, PCI, GDPR, SOX and more, here are the most required evet categories for auditing on both internal or external requirements:
- DDL operations (data changes: Insert, Update, Delete…)
- DML operations (schema changes: Create, Alter, Drop…)
- Queries (data access: Select, Select Into…)
- Security events (permission changes, login events…)
- Backup/Restore tasks (database backup and restore info)
- Execute tasks (transaction executions)
- Warnings (different SQL Server warnings)
- Errors (different SQL Server errors)
In the Audited events in ApexSQL Audit article, we can see a solid list of almost 200 SQL events that can be audited, covering all important categories and major events required by all current compliance and frequent internal auditing standards and requirements.
Data protection and safety
Now that the data is audited, it is paramount to ensure that data is safely stored and that any attempts to tamper with the data will be reported on and that the perpetrator can be identified to prevent any future malicious or inadvertent attempts to change the audited data or details.
Some of the prime features regarding data safety to look for are:
- Tamper-evident environment – see how ApexSQL Audit tackles this requirement in the ApexSQL Audit Tamper-evident design features article
- Date encryption on both repository database as well as during the data transfer from/to the central repository
- Configurable user access to the GUI and data – How to allow or deny certain user access to the ApexSQL Audit GUI is an example of how ApexSQL Audit handles user access
Reporting
Reporting on the audited data must be on the same level as gathering/auditing the data itself. A solid SQL Server auditing solution must have a plethora of built-in reports, ready to run in a couple of clicks which cover all important auditing categories mentioned above. Additionally, users must be able to create custom reports to fulfill their simple or complex requirements to submit different reports:
Furthermore, it is strongly recommended to have a scheduler within the tool features that can create predefined reports on a configured time schedule and even have them delivered via mail to specific users or teams handling auditing and reporting.
Alerting mechanisms
Continuity of auditing is one of the job essentials. With huge penalties (and paperwork as well) imposed on auditing interruptions or gaps, it is important to have strong alerting policies in the SQL Server auditing tool which can alert specific users/groups on events that can endanger the auditing itself, such are connection issues, permission issues, technical issues with production servers (like disk space) and more.
More importantly, some auditing requirements make it mandatory to alert DBAs or engineering departments on important SQL Server events that can compromise schema or data and endanger the whole system. With this in mind, it is very important to have both built-in alerts which will notify/alert on events that can endanger auditing operation, as well as ability to create custom alerts which will raise an alarm immediately on specific/security events such are unauthorized login attempts, deletion of specific schema, structure or data, access to ‘confidential’ data by unexpected logins, connections from unauthorized hosts and more.
In the How to create a custom alert with an email notification article, we can see how ApexSQL Audit solves these concerns by offering several critical built-in alerts as well as allowing users to place an unlimited number of highly-precise alerts which will alert specific users/groups on any unexpected or critical events regarding auditing and security
General features
Ease of use
Often a neglected concept, ease of use is an overall important feature for all auditors because in most cases, there are no specific “auditing engineer” positions where a single person or a whole team will be dedicated and work on the position which is focused only on auditing – in numerous cases, DBAs or engineers in similar positions are those which must implement and maintain auditing solution of choice.
With this in mind and all other daily tasks their position in the company dictates they must take care of, it is very important for the SQL Server auditing solution to be simple, easy to learn and use, have excessive and complete documentation and guides and transparent roadmap for future development. While having many useful features and options is always good, having a rave-party of options, icons and commands can heavily diminish usability of the tool and require harsh time investments to complete even simplest of jobs – so, having a well-organized GUI with plethora of options sorted in a well-oriented manner can make your life much easier.
Repository maintenance
Auditing jobs, in most cases, result in vast quantities of audited data. This means that SQL Server auditing solution must come with good and easy to use maintenance mechanisms which will allow users to operate with the repository and data within to ensure that there is no hindering to the auditing job, that storing and working with the historical data is easy, and that data that is no longer needed can safely and quickly be removed and deleted
Check out these two handy features for repository maintenance in ApexSQL Audit:
Bonus features
Support plan and assistance
More often than not, help or assistance of dedicated support specialists may be required or even paramount in critical situations. While this is not technically a feature for SQL Server auditing solution, it is very important to ensure that the solution of your choice has a solid team behind it with capable support engineers and developers behind which can jump in and assist on short notice with strong expertise and knowledge, especially in production-critical events.
Proof of concept
With the lack of auditing specialist position in many modern companies, personnel in charge of auditing has a tough job of getting to know the leading solution on the market in short and steep time frames. While researching online documentation can give a solid overall insight into the solution itself, achieving a strong proof of concept is an entirely different experience. With this in mind, it is recommended to trial out the solution in development or even production environment and see how the tool behaves as well as all the fruits (including rotten ones) of its continuous labor. The advantage here is given to the solutions which:
- Are easy to install and configure
- Have at least 14-day trial (with the possibility to extend the duration when needed)
- Have no limitations on any of the tool features during the trial length
Final words
As we have seen above, when choosing the best possible SQL Server auditing solution, it is important to pay attention to the following:
- Auditing mechanisms
- Which events and what information on the events can the tool capture
- Data protection and safety
- Reporting (custom and built-in)
- Alerting capabilities
- Ease of use and learning curve
- Repository and data maintenance
- Tool support and future
- Proof of concept essentials
While some of the above categories may not be mandatory for specific environments, the recommendation is to always plan for the future, since auditing requirements and compliance standards are getting more strict and greater in numbers each year, so plan for the future in addition to the current requirements. Be sure to pick the tool which is best suited for your exact environment which also has a solid plan for future features you need or may need in the future based on the auditing requirements projections, predictions, and trends.
February 4, 2020
