Introduction

This is a review of a tool we evaluated as a possible solution for auditing our environment.

The Environment

Most companies that have regulations such as HIPAA/Sarbanes Oxley compliance need some kind of audit tool that monitors activity on critical production servers.

The environment I work on comprises of around 10 critical servers that need to be audited.

We have had a tool in place for some time now we are faced with a situation where the pricing of the tool has gone up and also with usage we found some significant bugs/problems. We also considered SQL Server’s built in audit mechanism, unfortunately most of our environments are on Standard Edition, not Enterprise and this presented a significant investment in terms of $. We decided to look into various 3^rd party tools first instead of upgrading to Enterprise. This is one of the tools we are looking into and evaluated.

Set up

The first good thing I observed was the availability of free trial and download of the tool. Free downloads make it easy for a customer to evaluate the tool hands on as opposed to demos or long exchange of communication with the vendor. The second good thing was that Idera offers support  as they would for a vendor who has purchased the tool. I had some questions and called their technical support, and got prompt and quick answers to my questions. They also extended the trial period in order for me to evaluate the tool in greater detail.

The tool needs to be installed on the server that has the instance(s) needs to be audited. The installation was fairly simple, it does need an account who is sysadmin on the database and can also write to a folder on the server. It creates two agents to monitor the activity, and it is important to see if these agents are started up before opening the tool. It also creates 3 traces for the configuration I selected, which is to record activity on select logins and record just what user did, without data capture.

Features

The interface is very intuitive and feature rich – with two main tabs, administration and reports.

Administration:

Administration includes configuring the audit, and also setting up alerts as necessary.

Configuring the audit:

On administration tab you can add the server to be monitored and you are given options for monitoring as below

The list above is only server level monitoring (database level monitoring is set up from a different tab). Once the server is registered it appears as below and databases can be added as desired for auditing.

It is possible to filter audit by logins, events at server level, database level and object level. It is also possible to configure the audit to capture data (although since it goes through a trace that would be significant performance and space overhead) or only the specified event.

Configuring Alerts:

In addition to configuring audit you can also configure alerts for various events as below. These alerts are also configurable at server ,database,object and host level, can include filters such as filtering by application name, login name etc,  and severity set to severe/high/medium/low. Each situation can be configured to generate an email or an event log entry as well.

Audit Reports:

There is a huge and very detailed list of reports that are possible. All reports can be printed or saved in a variety of formats. Since the reporting is SSRS driven it is also possible to schedule reports, although I did not see that as part of the tool. The RDLs could be copied elsewhere and used similar to any other SSRS Report. Writing custom reports is also possible since the schema is very easy to understand.

Sample Report is as below.

Concerns:

1 Auditing of data may pose significant overhead since it is trace based. Our requirement is not a data audit, more around access and commands run so it worked fine for us.Generally speaking data audit is separate from audit of access/commands – which is why Microsoft has Audit and CDC as two separate features. I would not recommend using such tools to do data audit.

2 Our auditors have concerns that database that stores audit results is being managed by the same DBA team that is being audited – so there is a risk of data being tampered with. To control this the tool has a repository integrity check – it matches the audited data with an internally stored hash – as below and shows errors if the data has been manually tampered with. It is also possible to enable auditing on the audited database to see who tampered data if necessary.

Summary:

The most impressive features of the tool are the fact that it is lightweight, does not use triggers or other mechanisms that write to the audited databases, and also has tamper proofing built in. The reporting is also very comprehensive for most requirements. I would highly recommend this tool for organisations with similar requirements. A summarised list of features as below.

Following is a summarized detailed list of features.

Link for Download:

http://www.idera.com/Products/SQL-Server/SQL-compliance-manager/