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.
Conducting SQL Server audits provides information regarding how SQL Server databases are being used. They can be done for multiple specific reasons that include:
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.
Different types of audit methods can be used to provide the kind of information of interest to an organization and its database team.
Often there are multiple questions to be answered that demand information gleaned from several different types of audits.
There are many ways to perform SQL Server audits. Here are some tips that can improve the effectiveness of audit activities:
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.
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.
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.
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.
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.
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!
Powered by IDERA