THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Damian Widera

Review of LepideAuditor Suite for SQL Server


Being a MVP gives me the chance to freely use and test some fancy tools and this time I would like to share my thoughts about one of such.  

SQL Server Auditing is an important procedure to know the answers to questions such as who made a particular change, when, and from where was this change made. Unidentified changes can influence the whole IT environment, especially when other crucial servers are interconnected with SQL Server.

LepideAuditor Suite audits every aspect of SQL Server. It provides a common platform to audit the server components such as Active Directory, Group Policy Objects, Exchange Server, SharePoint, and SQL Server. You can simultaneously audit multiple instances of any server component.

System Requirements

You can audit SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, and SQL Server 2014 using this software. The minimum system requirements to install this Lepide product are listed herein below.

·         Dual Core or higher Processor

·         Minimum 4 GB RAM

·         Free space on hard disk

o   Minimum 1 GB

o   Recommended 2 GB

·         Any of the following 32 bit or 64 bit Windows Operating Systems.

o   Windows XP

o   Windows 7

o   Windows 8

o   Windows 8.1

o   Windows Server 2003

o   Windows Server 2003 R2

o   Windows Server 2008

o   Windows Server 2008 R2

o   Windows Server 2012

o   Windows Server 2012 R2

·         .NET Framework 4.0 or later

Prerequisites

You can save the auditing logs to the same or different SQL Server. The following prerequisite software should be installed on the machine where SQL Server is installed.

·         Microsoft System CLR Types for SQL Server 2012

·         Microsoft SQL Server 2012 Management Objects Setup

·         .NET Framework 4.0

Installation

The installation of LepideAuditor Suite is easy and quick. You just have to download the setup file, execute it, and follow the onscreen steps. The setup files of Web Console and App Server comes with the downloaded zip file. You can install the Web Console to access the audit reports from anywhere in the network using a browser.

LepideAuditor Suite sends real-time notifications in LepideAuditor App on your Android or Apple device through the default Lepide App Server. If you want to set up a custom App Server to send the notifications to App, then its installer file is available in the downloaded setup.

Configuring the Software

Once installed, you have to add the SQL Server that has to be audited. Before adding, please make sure to install the above prerequisite on the server machine. At the welcome screen, you have to provide the login credentials of the local system or domain administrator to run the software service.

Welcome Screen of LepideAuditor Suite

 

You can select an administrative user account and provide its password to run this service. Once configured the following dialog box will appear, where you have to select the component to be audited.

Component Selection

You have to select “SQL Server” and click “OK”. The following wizard will appear.

Wizard to add a SQL Server option

Two options are available here – Express Configuration and Advanced Configuration. The former is the way to add SQL Server with minimum configuration, where the latter lets you configure every aspect of SQL Server auditing. Click “Next” to start adding the SQL Server.

Details of SQL Server

Enter the details of SQL Server. You can also click “Browse” button to select the SQL Server. Click “Test Connection” button to test the connection to the target SQL Server. You can select whether to add server with agent or without agent. An agent will be installed on the machine where SQL Server is installed in agent-based auditing, whereas no agent will be installed if you select “Without Agent” for agentless auditing. Click “Next” once you are done. The next step lets you configure the health monitoring of SQL Server.

Health Monitoring Settings

You have to provide the name or IP Address and login credentials of the administrator of the computer where SQL Server is installed. Click “Next”.

Audit Settings

Here, you have to select what you want to audit from the following options.

1.       Audit Everything: Everything on SQL Server will be audited.

2.       Audit Server: Only the server except its databases will be audited.

3.       Audit Server with selected objects: Server with the selected databases will be audited. The available databases will be listed when you will select this option. You can select the databases to be audited.

Audit Server objects with the selected databases

Once you have configured what to audit, you can click “Next”.

Object Settings

Here, you can select the desired server and database objects along with their operations to be audited. You can check the objects that has to be audited. Uncheck the others to exclude them from auditing. Click the operation cell for an object to select the object operations to be audited.

Operations of an object

You can check the objects that have to be monitored. Uncheck to exclude them from auditing. Once you have selected the objects and their operations to be audited, you can click “Next”.

User Settings

The available options are – All Users and Selected Users. You can select the latter to select the users to be audited.

Once you have made your selection, click “Next” to proceed. The database settings appear onscreen.

Database Settings

You have to provide the SQL Server details and create a new database or select an existing database to store the auditing logs. There are small buttons to save the SQL Server as default for Auditor Suite or load the settings from an already saved default SQL Server.

Click “Next” to proceed. The next steps lets you enable the archiving of logs and schedule the archiving.

Archive Settings

Enter the SQL Server details, select a database, and configure the schedule. The software will automatically archive the logs as per the provided schedule.

Click “Finish” to add the SQL Server. A message box will appear onscreen to restart the software. Once restarted, you will notice a new tab for the added SQL Server in “Radar” tab. “Settings” tab will display the settings to configure the listing of SQL Server, whereas “Audit Reports” tab will show the audit reports for SQL Server.

Glimpses of Configuration Changes

Radar tab shows the summarized graph reports of all changes being made in SQL Server.

SQL Server Tab

In addition to the default tab, you can create multiple custom views. “Radar” tab for SQL Server lets you keep a check on the most critical changes such as database modification trend, table modification trend, user modification trend, top 10 failed logins, top administrators, resource utilization, all changes trend, and LiveFeed updates.

Audit Reports

You can switch to “Audit Reports” tab to view 50+ predefined reports that highlights every change made in the configuration of your SQL Servers.

Audit Report

Here, you can view the audit report in both text and graph views.

Graph Report

You can apply date range, working hours, and keyword filters to these reports. In addition, the columns can be grouped by and you can search for a particular event.

Filtered table

These reports can be saved on the disk and scheduled to be delivered automatically at predefined intervals.

Create Schedule

Alerts, Updates, and Notifications

You can configure real-time alerts that can be delivered to the provided email addresses, displayed at LiveFeed widget of Radar Tab, and sent to the LepideAuditor App installed on your Android or Apple device.

Create Alert

These alerts can be used to show the live updates in LiveFeed widget in the Radar tab of SQL Server.

LiveFeed Widget

You can also receive the notifications in LepideAuditor App. This app is available in both Google Play Store and Apple App Store.

App Notifications of SQL Server

Search in Notifications

Options in App

Share Notifications

In addition, you can install LepideAuditor Suite Web Console to host a report server using which you can let the selected users view the selected audit reports in a Web browser from anywhere in the network.

All Server Object Modification Report in Web Console

The best part is that LepideAuditor Suite also has a dedicated report on Console Auditing that shows the changes made to the configuration of the software itself.

Conclusion

After viewing the working of LepideAuditor Suite, I recommend you all to install this software for auditing any or multiple SQL Servers in the environment. It audits and tracks every change being made to the configuration of SQL Server. The vast set of predefined audit reports along with the options to customize, save, filter, and email the reports periodically make the task easier. Real-time alerts in email, LiveFeed and LepideAuditor App keeps you notified about the critical changes.

Product page link- http://www.lepide.com/lepideauditor/sql-server.html

Download trial - http://www.lepide.com/lepideauditor/download.html

 

Cheers

Damian 

 

Published Thursday, December 10, 2015 1:41 AM by Damian
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

denialparl@gmail.com said:

Looks a great application.

Thanks for sharing detail information.

Will give a try soon as we are considering to have an auditing tool which can capture the changes of SQL server on regular basis. Alert feature and live feed console of this tool seems very useful.

December 10, 2015 12:30 AM
 

Damian said:

Denialparl - thanks, let me know. Would be great if you could test it, too!

December 10, 2015 2:26 AM
 

Jeff said:

"Share with Facebook"

I suppose I should expect that in everything these days, but an auditing application?

December 10, 2015 9:30 AM
 

Damian said:

Jeff, I think that this is just a regular sharing window. But yes, good catch indeed :)

December 10, 2015 9:44 AM
 

jeff_yao said:

When I see so many GUIs, I usually give up. Operation based on GUIs is for anything but operation efficiency and scalability.

Do you think you can create a CLI (Command Line Interface) tool? These days, even Microsoft starts to part away with primary GUI tool (with Nano server as an example) but instead provides PowerShell cmdlets for administration purpose.

(No, I am not saying GUI needs to be totally abandoned, but it should be as little as possible).

I talked about my idea in my blog long ago

http://www.sqlservercentral.com/blogs/jeffrey_yao/2012/09/17/why-i-dislike-third-party-database-monitoring-tools/

Just my $.02.

December 10, 2015 12:44 PM
 

Damian said:

jeff_yao - I agree with your opinion that command line tools are important for DBAs or in general every server administrator should have or what is most probable - he/she already has a favourite command line tool. However I still see a value added by this graphic tool. I use both command line tools & GUI every day and for some type of work I like to have a graphical overview because it is better to see a green colour saying "it is all right".

December 11, 2015 3:36 AM
 

Greg Linwood said:

I am the architect of a 3rd party monitoring tool (SQLBenchmarkPro.com) as well as an employer of SQL DBAs, provider of remote DBA services and an ex SQL MVP

I think there is no need to minimise either GUI or CLI - why not provide as much as possible of both?

GUI provides organisation of information and ease of accessibility for a large user base, CLI allows users with tech skills to do advanced things but this is a smaller audience

LepideAuditor Suite looks like an interesting tool which I hope finds a market for its developer, thanks for sharing Damian as I hadn't heard of it before

December 11, 2015 4:03 AM
 

Damian said:

Greg, I know you as MVP and I really appreciate your comment. I agree - there should be a possibility to have both GUI and CLI and use them as needed

December 11, 2015 4:23 AM
 

jeff_yao said:

@GregLinwood. GUI is best to be used to present the *final* results, IMHO. But if GUI is used as the primary interface to operate the 3rd party tools, it is not proper/ideal for large, scale-out operations. Just like I use GUI to create a sql job on one server, but if I need to create similar jobs across multiple servers, I will never use GUI for the real deployment.

Although I agree with the statement of "why not provide as much as possible of both", but in real world, most of efforts I see (from 3rd party vendors) are dedicated to GUI instead of CLI toolsets. On the other hand, to me, it may be a bad decision not to invest more on CLI toolsets/APIs than GUI .

December 11, 2015 5:51 PM

Leave a Comment

(required) 
(required) 
Submit
Privacy Statement