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