This is a review of a tool we evaluated as a possible solution for auditing our 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 3rd party tools first instead of upgrading to Enterprise. This is one of the tools we are looking into and evaluated.
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.
The interface is very intuitive and feature rich – with two main tabs, administration and reports.
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.
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.
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.
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.
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.
|Feature ||Idera’s SQL compliance manager |
|COST ||$2995/instance |
|PLATFORMS ||Supports SQL 2000, 2005, 2008, 2008 R2 |
|REPOSITORY ||Central repository provide for easy cross server audit reporting using SQL Reporting Services. The repository is stored on SQL server instance, so the user can decide how much disk space is required to support the retention policy. |
|INSTALLATION ||Easy to install, deployment and configuration. |
|CONFIGURATION ||Easy to set and understand granular audit settings. Audit Logins, Failed Logins, Admin, Security (DCL), DDL, DML, SELECT operations. |
|EVENT FILTERING ||Filtering. Using an easy to use filter wizard set up granular filter rules to filter out un-wanted events. |
|CUSTOM REPORTS ||Reporting. Run time reports within the CM console, or deploy reports to SQL Server |
|REPORTING ||Uses SSRS |
|TAMPER PROOFING ||Tamper detection, and integrity verification of events in repository. Repository has a hashing mechanism for all event data to verify the integrity of the events. The Services and Audit settings are logged, and auditing is automatically restarted even if a sysadmin attempts to stop auditing. Any changes to data are audited as well. |
|ALERTING ||Built in alerting including changes to security/logins on sql server. SMTP or Event based alert mechanism based on user configurable event rules. |
|GUI ||Easy to use and view management console. |
|AUDIT ENGINE ||Audit starts up a low level trace with basic parameters (about 2 percent over head, more if data before and after images are needed.) Capable of limiting auditing a select few SQL logins and SQL server roles. |
|RETENTION ||Event archiving, retention and archive integrity verification. User configurable and the archives can be restored on different servers to perform reporting against those servers instead of the production CM repository. The integrity of the archive can also be verified to ensure nobody has tampered with the data. |
Link for Download: