Overview of the program for auditing Microsoft SQL Server-NetWrix SQL Server Change Reporter 2.5

SQL Server is a complex system in which changes are constantly taking place, be it the configuration of the server or the contents of the databases. Tracking changes is difficult, especially in cases where the changes relate to fine-tuned delegation rules for environments that are managed by several administrators and operators.
In Runet, we were able to find the following SQL Server audit materials:

• MSDN article
• Techdays Videos
• Technet article

Habr is also not lagging behind - there is a good post on tracking changes in SQL Server.
')
However, the question of writing an up-to-date and complete audit manual for SQL Server remains open (we have plans to write such a guide - 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 will look at what is in the basic version of the program, and then we will dwell in greater detail on the extended version.
Moreover, we recently released a new version of our solution for auditing SQL Server changes - NetWrix SQL Server Change Reporter 2.5 . In the new version, we added support for auditing database content — the function that users have been waiting for.
We invite interested persons under cat.


Let's start with why the program is needed at all and what it does.
Its key functions :

• Changes made to instances, databases, users, roles, logins, schemas, permissions, tables, stored procedures and other SQL Server objects are recorded; clear and understandable reports are generated based on these changes.
• All changes made to the database contents are recorded; based on this information reports are generated (and can be automatically sent by e-mail)
• Compliance with the requirements of standards in the field of information security (a wide library of standard reports that can be provided to auditors)
• Unauthorized and undesirable changes that can lead to server crashes and databases are fixed in the early stages.
• Display in reports of who made the change, where, when and from which workstation
• Ability to create reports on request (ad-hoc reports)
• Storing the collected audit data and creating reports based on this data for any period of time

Why is there no standard SQL Server change tracking system?

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 respond quickly to changes in configuration and database contents. When using only a standard audit system, which simply allows to receive large amounts of data about events, this possibility is absent. Agree that the database administrator does not have much time to manually track the changes, and then deal with them. Such 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 time-consuming from the administrator, is still acceptable. However, with the increase in the number of SQL servers and changes in databases, there is a growing need to automate the change notification process.
So NetWrix SQL Server Change Reporter was created to simplify the process of tracking changes in SQL Server. The program uses the technology of NetWrix AuditAssurance, which is to use data from event logs 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 is 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 ).
In brief, we denote 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 the data collection, the product sends a report to the specified email addresses and loads the data into 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 developed for specific audit tasks based on SQL Reporting Services. Working with the report library can be carried out either directly from the console or from a 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 initiated manually.

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 installation process is quite simple, so we will not describe it.
After the program is installed, you need to configure it. Please note that the configuration interfaces of the basic and advanced versions of the program are different.

Free version

Configuring the free version is carried out through a special utility.



In which you can:

• Activate the program
• Specify the server instances on which changes should be monitored.
• Choose a storage location
• Determine the frequency of delivery of reports
• Set e-mail 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 3 am. The time can be changed in the Task Scheduler (the job name is NetWrix SQL Server Change Reporter). You can also start data collection manually.
When a task is run, SQL Server configuration snapshots are taken, event log data is analyzed, the configuration state is compared with the previous one, and the change information is sent to the specified email address.
What does the report look like in the free basic version



As we can see, the author of the changes and the workstation where 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 SQL server instance, report type and snapshot date
3. Generate - select the report type (csv or html) and save it to the right place.

Such is the brief version of the free version. In the extended version (which can be tested for 20 days), more features are available.

Full version of the program

To begin with, all work is done through the NetWrix Enterprise Management Console , which reminds everyone the familiar MMC.



We are setting up 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 the data will be collected
   
   
4. Configuring SMTP server settings
   
   
5. Configuring the function of receiving advanced reports
   
6. Next, we add SQL server instances.
   
7. Specify the name of the SQL server instance
   
8. Further. During the Configure SQL Server Change Reporter Settings program, you need to specify the recipients of the SQL Server configuration changes report.
   
9. 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 database contents audit, use its own dialog box ( Database Content Audit ), where rules for the data to be monitored are configured
   .
   
   • You must select Specify .
   • Add a rule ( Add ) according to which data on audit of database changes will be collected
     
     Note that the following column types are not currently supported: text, ntext, image, binary, varbinary, Timestamp, sql_variant.
   
   

At the last stage we choose the format of the report that will be sent.


A couple of confirming actions - and the program is set up.
The observable 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. The final change reports will be sent daily to your email address. 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, a final report will be sent to the specified email addresses. Sample Report



The program can not only receive automatic reports, but also generate reports on request (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 select the type of audit (configuration or database content). By clicking on Run, you are prompted to select an html or csv report.
Another important feature is advanced reports.
Advanced reports allow you to receive reports created for solving specific tasks using predefined filters and templates. Many of these reports are required by regulations on information security.
They can be viewed either in the console or on 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 on SQL servers.



And traditionally, a presentation on how to customize the program 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 tell how to track changes to SQL Server using the new version of NetWrix SQL Server Change Reporter. Join now .