Building sound SQL Server audit strategy is as important as establishing database security in your organization. Database security has always been pitched battle against the database usability and accessibility on the other side. Establishing database security is a complex and paradox endeavor asking to follow various security technologies and practices. The less vulnerable database, the more it is difficult to use and access it, and the question is, how to build the balance between the two?
Regardless of how many security techniques will be implemented in an enterprise, tracking activities, data flow, and recognition of potential threats are unavoidable. SQL Server data breaches are still prominent, and risk assessment priority across multiple industries. In this article, we are going to introduce the most critical events to be audited in SQL.
SQL Server Audit as a native solution
SQL Server Audit as native Microsoft solution is used to track activities in SQL Server and databases providing great opportunities but also requires deeper knowledge to effectively use it. To configure it, the parent component SQL Server Audit for both server and database level has to be instantiated first.
Starting with SQL Server 2012 and onwards (applies to all editions), the component can be created via both SQL Server Management Studio and T-SQL. For this article, we are going to demonstrate how to create it via SQL Server Management Studio:
- Connect to the SQL Server via SQL Server Management Studio tool
- Expand the Security container on the SQL Server instance
- Right-click on Audits container and choose New Audit
In Create Audit dialog, you will be prompted to define:
- Unique audit name
- Delay queue time which is a frequency on which the audit file will be processed to pick up the audit data
- Action that will be performed on the inability to process Audit file
-
Audit destination – audit data can be stored in:
- Flat audit file
- Security Log
- Application Log
Security Log is more secure with a strict access option than Application Log since Application Log can be overwritten due to size limitations and may cause the data loss.
A flat file is the most recommended approach with the ability to secure auditing with no data loss and configure the file rollover in SQL Server Audit specification.
Once the SQL Server Audit is created, it is in a disabled state. Enable it to start collecting audit log records to the specified file or log, as shown below:
Server audit specification
Server level specification is used to collect audit trail on activities and changes that are being performed on the SQL Server level, and it is available to have only one server audit specification per SQL Server Audit. Server level operations are organized in the form of pre-defined groups of operations that are known as Audit Action Groups.
To create server audit specification, head to Security node on SQL Server level, right-click on Server Audit Specifications, and choose New Server Audit Specification:
The next step requires to assign it a meaningful name and choose the action types to be tracked. SQL Server provides a plethora of different groups and types to be audited, as shown in below screenshot:
When the specification is created, it does not start auditing immediately and to put auditing to motion you need to enable it first:
Database audit specification
Database audit specification is collecting database-level audit events. It is also is dependent on SQL Server Audit, and there can be only one database audit specification per audit. Unlike the server audit, database audit specification besides Action groups also allows us to define auditing more granularly via audit events and track only specific operations when issued.
Database level auditing is supported for all editions starting with SQL Server 2016, while for lower versions down to 2012, it is available only in SQL Server Enterprise edition.
To create database audit specification, browse for Database Audit Specifications in the Security node on a database level and from the context menu choose New Database Audit Specification:
The database-level audit specification allows a more granular auditing approach but also requires more configuration complexity, so in cases when a single action is being audited, you will be tasked to provide the class, schema name, and object name that auditing action is applied to. In addition to that, specifying database users or roles to be audited using this action:
Again, after the specification is created, it has to be enabled manually from the context menu of the specification:
Creating auditing strategy
Auditing all activity against SQL Server seems pretty simple and tempting, but the biggest challenge that many organizations face is meeting the compliance without putting extra performance overhead while auditing. So, the less auditing, the less data will be captured and stored – the lesser resources will be allocated throughout the course. It is essential to choose wisely what events to audit and how, so let’s take a look at the most critical events that are most likely part of any SQL Server audit strategy.
Failed logins
One of the most critical events is to track failed login attempts that are common in various scenarios, mistyping username and password, expired password, but it is also an indicator of the potential malicious access attempt. So, there’s nothing more important than having this information handy at the very moment whenever it happens and raise alert to the security team.
Role member changes
Access controls changes are important for both compliance and security, and having information whenever a login is created, altered, or deleted from a fixed server or database role helps to sustain security policies and understand privileges throughout the SQL Server environment.
Principals changes
This event also puts on understanding access controls in SQL Server and databases; it is raised whenever a principal, such as a user, is changed. It is important to track this event to easily expand the context of any privileges abuse, or trail potential failed attacks.
Database structure changes
Even though this particular event can generate a plethora of audit records, it is commonly requested to track whenever database objects are created, altered, or dropped.
Self-auditing
Organizations are required to make sure building auditing strategy that ensures tracking activity against the auditing implementations in their work environments, highlight actions performed by users with no strict access, and decreasing the vulnerability of it.
Privileged users are the most intensive auditing group to keep tracking due to inadvertent activity that may occur during the day-to-day routine and break compliance. In addition to that, unlimited access has always been a ticking bomb that DBAs can activate whenever malicious actions are performed. And for these reasons, self-auditing is arguably the most important event from the list since all compliance regulations require to ensure the tamper-evident SQL Server audit.
Out-of-the-box SQL Server audit solution
Building a sustainable and maintainable audit solution is a top priority for organizations that hold sensitive data in their SQL Server infrastructure. Native auditing solutions provide capabilities to meet compliance requirements, but maintainability of such auditing implementation can turn to chaos as it brings several different concerns such as:
- Non-contextual data logging
- Performance degradation
- Hidden costs
As we’ve demonstrated in this article, SQL Server Audit as a native solution is not supported across all SQL Server editions and versions, it requires to build a dozen of specifications in cases with a huge amount of SQL Server and databases in an organization, and it may lead to allocating the unreasonable amount of time creating and maintaining it.
ApexSQL Audit is the compliance-ready auditing solution for SQL Servers and databases that is capable of auditing almost 200 different SQL Server operations. With this solution, it is easy to:
-
Avoid time-consuming configuration and maintenance
This is a true challenge with a native solution due to the limitations of having a single child audit per SQL Audit parent, the single auditing technology is not applicable for all SQL Servers and editions, and finding the proper solution is an exhausting job to do.
ApexSQL Audit configuration is done simply and easily with selecting appropriate operations, applications, and logins to be tracked and audited for a SQL Server instance. Regardless of SQL Server editions, versions, the number of SQL Servers, the configuration can be easily re-played across multiple servers and databases:
- Easily identify and audit sensitive data
- Schedule and run the audited reports
- Get e-mail alerts on any specific alert on data access or change
- Ensure tamper-evident auditing with secure data keeping and archiving
Conclusion
ApexSQL Audit helps enterprises to proactively control risk, can automate time-consuming reporting and processes tied with compliance regulations, enhance security, and track sensitive data access while keeping SQL Server safe and operational. To broadly understand the benefits and product features, see this Top features you need in a SQL Server auditing solution article.
June 26, 2020
