Tips for More Effective SQL Server Audits

by Jun 7, 2021

SQL server audits are an important function for database administrators (DBAs) and database teams. How they go about this activity can have a considerable impact on the reliability of the information generated and the impact of the audit on normal database operation.

Why Audit SQL Servers?

Conducting SQL Server audits provides information regarding how SQL Server databases are being used. They can be done for multiple specific reasons that include:

  • Assisting with preventive measures;
  • Complying with legal or technical requirements;
  • Observing patterns over time to identify unusual behavior;
  • Tracking overall database activity;
  • Ensuring business rules and practices are being followed.

The importance of auditing SQL Server databases can vary depending on the type of business under review. Organizations in the healthcare or financial services fields have strict regulatory guidelines that need to be followed to avoid penalties and fines. 

Any business that stores personally identifiable information (PII) or other sensitive data needs to ensure that only authorized personnel are accessing this data.

In these cases, audits need to be conducted regularly and reports have to be retained to be used as evidence to provide to external auditors. Other SQL Server environments may not need to worry about audits as much due to the type of data they store in their databases.

Types of Audits

Different types of audit methods can be used to provide the kind of information of interest to an organization and its database team. 

  • Transactional audits focus on Create, Read, Update, and Delete (CRUD) operations performed against database tables. This type of audit is concerned with how the data manipulation language (DML) functionality is used in SQL Server instances.
  • Schema-based audits look at the object operations and changes performed by SQL’s data definition language (DDL). These audits show when tables are added, removed, or modified. 
  • Configuration-based audits look at how the server and applications are configured to identify gaps or inconsistencies that need to be addressed. 
  • Statistical audits are used to observe database activity to detect patterns and identify unusual activity. These types of audits can help isolate security issues that need to be resolved to protect enterprise data resources. 
  • Business audits indicate how well systems are implementing and following rules for a specific company or industry. 

Often there are multiple questions to be answered that demand information gleaned from several different types of audits. 

Tips for Auditing SQL Server Databases

There are many ways to perform SQL Server audits. Here are some tips that can improve the effectiveness of audit activities:

Using triggers intelligently

Audits predicated on triggering for specific events can be very valuable for understanding what is going on with a particular SQL Server instance. Alerts can be generated to inform the technical staff of issues that need to be addressed. Triggers work best for audits concerned with configurations and units of work. They are more effective in smaller environments with a heavy read-to-write percentage.

Carefully selecting the tables to be audited

There is often more than one table that can be used to provide the necessary audit information. In some cases, one of the tables will be substantially smaller and enable audit activity to be completed more promptly than if alternatives are used.

Auditing Big Data

Big data can present challenges to audit effectively. Resource constraints may make it impossible to retain all audit results or audit all system activity. Approaches such as performing statistical audits on subsets of data or randomly auditing database activity can be used to get around these constraints. 

Storing audit records

Adequate space needs to be allocated to retain audit records. This can be in the audited database or a second database used exclusively to store the audit information. While it might seem simpler to use a secondary database, this method does introduce another attack vector for hackers to exploit.

Be aware of auditing overhead

Audits have a cost in system overhead that in some cases may exceed the audit’s value to an organization. It may not be possible or warranted to audit everything in your SQL Server environment. Tradeoffs may be required to reconcile the cost versus value of audit initiatives.

Simplifying and Improving SQL Server Audits

Third-party tools can add functionality not available with native SQL Server features. 

SQL Compliance Manager provides a dedicated platform from which to audit SQL Server instances. The tool helps organizations answer the question of who has access to sensitive data, how they obtained that access, and what they are doing with the information. 

Flexible alerts can be configured to inform the right individuals when suspicious activity is detected. 

SQL Compliance Manager offers teams pre-defined and customizable audit templates that can be used to verify compliance with many regulatory standards as well as internal business requirements. 

Its lightweight agent audits systems with a minimal amount of overhead. The tool also contains predefined compliance reports that can be used to provide evidence to external auditors. 

Audit results are stored in an immutable repository to ensure that no internal or external sources can tamper with them.

An informative webcast is available that illustrates tips and tricks for auditing SQL Server databases. The video presentation begins with an in-depth discussion that covers the tips in this post as well as additional information designed to help database teams perform audits efficiently. 

The webcast concludes with a look at how SQL Compliance Manager helps teams implement a viable SQL Server audit strategy. It’s recommended viewing for any database team member responsible for auditing enterprise SQL Server instances.

Try SQL Compliance Manager for free!