November 9, 2012 at 14:24

Microsoft SQL Server Audit Software Overview - NetWrix SQL Server Change Reporter 2.5

SQL Server is a complex system in which changes are constantly occurring, be it server configuration or database contents. Tracking changes is difficult, especially when changes relate to finely tuned delegation rules for environments that are managed by multiple administrators and operators.
In RuNet, we were able to find the following SQL Server audit materials:



Habr also does not lag behind - there is a good post on tracking changes in SQL Server here.

However, the question of writing an up-to-date and complete SQL Server audit guide remains open (we have plans to write such a guide - something like this and this ).

In the meantime, in this post we will look at SQL Server audit capabilities using our NetWrix SQL Server Change Reporter program. The program is available in two versions: basic free and extended commercial. First, we consider what is in the basic version of the program, and then dwell on the advanced version in more detail.
Moreover, we recently released a new version of our SQL Server change audit solution -NetWrix SQL Server Change Reporter 2.5 . In the new version, we added support for auditing database contents, a feature that users have been waiting for for a long time.
Interested parties are welcome to cat.


Let's start with why the program is needed and what it does.
Its key functions :
  • Changes are made to instances, databases, users, roles, logins, schemas, permissions, tables, stored procedures and other SQL Server objects; clear and understandable reports are generated based on these changes
  • All changes made to the contents of the database are recorded; based on this information, reports are built (and can be automatically sent by e-mail)
  • Ensures compliance with the requirements of standards in the field of information security (a wide library of standard reports that can be provided to auditors)
  • In the early stages, unauthorized and undesirable changes are recorded that can lead to a “crash” of the server and database
  • Display in reports of who made the change, where, when and from which workstation
  • Ability to create reports on demand (ad-hoc reports)
  • Storage of collected audit data and creation of reports based on these data for any period of time


Why is the full-fledged SQL Server change tracking system insufficient?



The audit subsystem was introduced for the first time in SQL Server 2008 and has not changed much since then (we checked, see the video review of audit capabilities in SQL Server 2012 ). Meanwhile, it is important for database administrators to quickly respond to changes in the configuration and contents of the databases. When using only a regular audit system, which simply allows you to receive large amounts of event data, this possibility is absent. Agree that the DBA does not have much time to manually track changes, and then deal with them. Similar problems, of course, are solved by writing scripts (for example, 1 , 2), but the information obtained as a result of their work, to put it mildly, does not allow to quickly understand what has changed - you need to spend additional time on its interpretation. For small organizations, it is quite possible that this option, although it requires a lot of time from the administrator, is still acceptable. However, with the increase in the number of SQL servers and changes in databases, the need for automation of the process of notification of changes increases.
So, NetWrix SQL Server Change Reporter was created to simplify the process of tracking changes in SQL Server. The program uses NetWrix AuditAssurance technology, which consists in using event log data and information obtained from SQL Server configuration snapshots. Thus, the completeness of the display of the changes made and the visibility of the information presented are achieved. And now more about the program itself.

How does the program work?


You are probably already familiar with the general principle of our programs (see reviews 1 , 2 , 3 ).
We briefly outline the following points:
  1. The work of the product is based on the task scheduler, in which a scheduled task is created specifically for the product, which initiates the collection of audit data once a day (or more often). Upon completion of data collection, the product sends a report to the specified email addresses and uploads the data to the SQL database (the latter is available only in the commercial version);
  2. In addition to email reports, the commercial version of the product also contains a library of reports designed for specific audit tasks, based on SQL Reporting Services. Work with the library of reports can be carried out either directly from the console or from the web portal through a standard browser;
  3. Reports with information on already collected audit data are available at any time, and the data collection itself, if necessary, can be manually initiated.


The program works with versions of SQL Server from 2000 to 2012 (yes, we also support the new version of SQL Server). To collect audit data, the service account used to run the program must have system administrator rights on the target server.
NetWrix SQL Server Change Reporter 2.5 is installed on any computer in the domain in which the observed instances of SQL Server are located. The program installation process is quite simple, so we will not describe it.
After the program is installed, you must configure it. Please note that the configuration interfaces of the basic and advanced versions of the program are different.

Free version


The free version is configured through a special utility.



In which you can:
  • Activate the program
  • Specify server instances on which changes should be tracked
  • Choose data storage location
  • Determine the frequency of report delivery
  • Set email report settings (SMTP server, recipient address, etc.)
  • Make additional settings


Data collection in the basic version

NetWrix SQL Server Change Reporter automatically collects data daily at 3am. You can change the time in the Task Scheduler (the job name is NetWrix SQL Server Change Reporter). You can also start data collection manually.
When the task starts, snapshots of the SQL Server configuration are taken, and event log data is analyzed, the configuration status is compared with the previous one, and information about the changes is sent to the specified email address.
What the report looks like in the free base version



As we can see, the author of the changes and the workstation on which the change was made are not indicated. The free version includes reports on changes in users, logins, access rights, roles and database schemas. Additional functionality is available in the commercial version of the program.
In addition to receiving scheduled reports, you can also receive on-demand reports:

  1. Launch Report Viewer

  2. Specify the instance of the SQL server, the type of report and the dates of snapshots
  3. Generate - select the type of report (csv or html) and save it in the right place

This is, in short, the work of the free version. In the advanced version (which can be tested for 20 days), more functions are available.

Full version of the program



To get started, all work is done through the NetWrix Enterprise Management Console , which reminds everyone of the familiar MMC.



We configure the program:
  1. Create an observable object (objects can be grouped into folders)
  2. The New Managed Object Wizard opens - select computer collection
  3. Next, set the name of the collection of computers and specify the account under which data will be collected

  4. Configure SMTP server settings

  5. Configuring the function of receiving advanced reports
  6. Next we add instances of SQL server
  7. Specify the name of the instance of SQL server
  8. Further. At the Configure SQL Server Change Reporter Settings stage, you must specify the recipients of SQL Server configuration change reports.
  9. Please note that auditing database content changes must be enabled separately. This type of audit is supported in versions of MS SQL Server 2005 and higher.
    To configure the audit of database content, its own dialog box ( Database Content Audit ) is used, where the rules for the data to be monitored are configured
    .
    • You must select Specify .
    • Add a rule ( Add ) by which data will be collected on the audit of database changes.

      Please note that the following column types are not currently supported: text, ntext, image, binary, varbinary, Timestamp, sql_variant.

At the last stage, select the format of the report that will be sent.


A pair of confirming actions - and the program is configured.
The observed object we created appeared in the Managed Object node (in this case, Production Servers).

Work with the program

So, you have already done most of the work by setting up the program. Summary reports on changes will be sent to your email address daily. If you need to receive a report manually, without waiting for automatic data collection, then click the Run button .



After the data collection is completed, the final report will be sent to the specified email addresses. Report example.



In the program it is possible not only to receive automatic reports, but also generate reports on demand (ad-hoc Reports). ( Managed Objects -> Production Servers -> SQL Server Change Reporter - Ad-hoc reports ).



Here you select SQL Server, set the period for which you want to generate a report, and also select the type of audit (configuration or database content). By clicking on Run it is offered to select html or csv report.
Another important feature is advanced reporting.
Advanced reports allow you to receive reports created for specific tasks using predefined filters and templates. Many of these reports are required by information security regulations.
They can be viewed either in the console or in the SQL Report Manager web portal through a browser. Two types of reports are available
( All Changes and Object Changes ).



Select a report from the folders, set the parameters and click View Report. Below is a report on all changes to SQL servers.



And according to tradition, a presentation on the topic of program setup and work with it.


You can download the program on the site (registration is required).

PS On November 15, we will have a web conference where we talk about how to track SQL Server changes using the new version of NetWrix SQL Server Change Reporter. Join now .
Tags:

Also popular now: