NETWRIX SQL SERVER CHANGE REPORTER ADMINISTRATOR’S GUIDE Product Version: 2.6

January 2013

NetWrix SQL Server Change Reporter Administrator’s Guide

Table of Contents 1. INTRODUCTION ............................................................................................................................................................ 4 1.1 KEY FEATURES.................................................................................................................................................................... 4 1.2 LICENSING ......................................................................................................................................................................... 5 1.3 HOW IT WORKS ................................................................................................................................................................. 6 2. GETTING STARTED........................................................................................................................................................ 7 2.1 SYSTEM REQUIREMENTS ...................................................................................................................................................... 7 2.1.1 Hardware Requirements .................................................................................................................................. 7 2.1.2 Software Requirements ................................................................................................................................... 7 2.1.3 Target SQL Server Requirements ..................................................................................................................... 7 2.1.4 Rights and Permissions .................................................................................................................................... 7 2.1.5 SQL Server Required for Advanced Reporting .................................................................................................. 7 2.2 INSTALLING NETWRIX SQL SERVER CHANGE REPORTER ............................................................................................................. 8 3. WORKING WITH THE FREEWARE EDITION ................................................................................................................... 9 3.1 CONFIGURING BASIC SETTINGS .............................................................................................................................................. 9 3.2 DATA COLLECTION AND REPORTING ..................................................................................................................................... 12 3.2.1 Running Data Collection Task ........................................................................................................................ 12 3.2.2 Running On-Demand Reports ........................................................................................................................ 13 4. WORKING WITH THE ENTERPRISE EDITION ................................................................................................................ 15 4.1 CREATING NEW MANAGED OBJECT...................................................................................................................................... 16 4.2 MODIFYING MANAGED OBJECT SETTINGS ............................................................................................................................. 26 4.2.1 Editing Managed Computers List ................................................................................................................... 26 4.2.2 Modifying Feature Settings ........................................................................................................................... 27 4.3 CONFIGURING GLOBAL SETTINGS ......................................................................................................................................... 28 4.3.1 Configuring Task Schedule ............................................................................................................................. 29 4.3.2 Configuring E-mail Settings ........................................................................................................................... 30 4.3.3 Configuring Repository Settings .................................................................................................................... 31 4.3.4 Configuring Advanced Reporting Settings ..................................................................................................... 32 4.4 DATA COLLECTION AND REPORTING ..................................................................................................................................... 34 4.4.1 Running Data Collection Task ........................................................................................................................ 34 4.4.2 Viewing Task Session Results ......................................................................................................................... 35 4.4.3 Viewing Scheduled Reports............................................................................................................................ 36 4.4.4 Running Ad-hoc Reports ................................................................................................................................ 37 4.5 ADVANCED REPORTING...................................................................................................................................................... 39 4.5.1 Configuring Default Advanced Reporting Settings ........................................................................................ 39 4.5.2 Modifying Advanced Reporting Settings ....................................................................................................... 43 4.5.3 Viewing Advanced Reports via Web Browser ................................................................................................ 44 5. ADDITIONAL CONFIGURATION ................................................................................................................................... 45 5.1 EDITING REPORT DELIVERY SCHEDULE .................................................................................................................................. 45 5.2 INCLUDING AND EXCLUDING DATA FROM REPORTS ................................................................................................................. 49 5.3 IMPORTING CHANGES THAT OCCURRED BETWEEN TWO SNAPSHOTS .......................................................................................... 50 A APPENDIX: MONITORED OBJECT TYPES AND ATTRIBUTES ......................................................................................... 51 B APPENDIX: MONITORED DATA TYPES ........................................................................................................................ 59 C APPENDIX: RELATED DOCUMENTATION .................................................................................................................... 60

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 2 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide Legal Notice The information in this publication is furnished for information use only, and does not constitute a commitment from NetWrix Corporation of any features or functions discussed. NetWrix Corporation assumes no responsibility or liability for the accuracy of the information presented, which is subject to change without notice. NetWrix is a registered trademark of NetWrix Corporation. The NetWrix logo and all other NetWrix product or service names and slogans are registered trademarks or trademarks of NetWrix Corporation. Active Directory is a trademark of Microsoft Corporation. All other trademarks and registered trademarks are property of their respective owners.

Disclaimers This document may contain information regarding the use and installation of non-NetWrix products. Please note that this information is provided as a courtesy to assist you. While NetWrix tries to ensure that this information accurately reflects the information provided by the supplier, please refer to the materials provided with any non-NetWrix product and contact the supplier for confirmation. NetWrix Corporation assumes no responsibility or liability for incorrect or incomplete information provided about non-NetWrix products. © 2013 NetWrix Corporation. All rights reserved. www.netwrix.com

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 3 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

1. INTRODUCTION SQL Server is a complex system that includes many different types of objects with almost daily changes to the server configuration and database content. It is very hard to track changes and enforce fine-grained delegation rules for environments managed by multiple database administrators and operators. A hot topic nowadays is compliance with government and industry regulations. It adds challenges to all types of IT infrastructures, especially SQL databases that store business-critical data and support business applications. NetWrix SQL Server Change Reporter is an easy-to-use auditing solution that reports on changes made to your database content and SQL Server configuration, including databases, security settings, server instances, users, roles, logins, schema changes and many other objects - no change will pass behind the scenes, no matter who made it and how. You just setup this tool once and start getting daily summary reports about all changes grouped by server name. You can also use the Advanced Reporting feature with optional custom reports available for ordering from NetWrix.

1.1 Key Features The SQL Server Change Reporter helps you carry out the following auditing and reporting tasks:  Detect and report on changes made to SQL Server instances, databases, users, roles, logins, schemas, credentials, tables, stored procedures, functions, and many other objects.  Detect and report on all changes made to the database content.  Ensure compliance with regulatory and security requirements such as GLBA, SOX, HIPAA, and PCI through consistent auditing and reporting.  Detect at early stages all unauthorized and unwanted changes that can lead to server and database downtime.  Get to know who made what change when, where and from which workstation.  Generate on-demand web-based reports.  Audit database backup and restore operations.  Create custom reports (they can also be ordered from NetWrix).  Store collected audit data and perform historical reporting for any period of time. NetWrix SQL Server Change Reporter is available in two Editions: Freeware and Enterprise.

Note: Some of the features are only available in the Enterpise Edition. For details refer to Section 1.2 Licensing.

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 4 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

1.2 Licensing NetWrix SQL Server Change Reporter is available in two Editions: Freeware and Enterprise. The table below outlines the difference between them.

Table 1:

The NetWrix SQL Server Change Reporter Editions

Feature

The Freeware Edition

The Enterprise Edition

Long-term archiving of audit data

No

Any period of time

Reporting on who made the changes and when

No

Yes

Database content audit

No

Yes

Changes made to database users, logins, credentials, roles and schemas only.

Changes made to server instances, databases, users, roles, logins, schema changes, tables, stored procedures, functions, and many other objects

Database operations tracking

No

Backup and restore

Single installation that handles multiple server collections, each with its individual settings

No

Yes

Integrated interface for all NetWrix products, which provides centralized configuration and settings management

No

Yes

Advanced reports based on MS SQL Server Reporting Services, with filtering, grouping and sorting

No

Yes

Advanced Reports can be viewed directly from NetWrix Management Console

No

Yes

Daily report with recent changes

Multiple predefined reports

No

Yes. Created manually or ordered from NetWrix

Database changes tracking

Predefined reports Custom reports

Technical Support

Licensing

Support Forum, Knowledge Base

Free of charge

Full range of options (phone, email, submission of support tickets, Support Forum, Knowledge Base) Per server; please request a quote

The Freeware Edition can be used by companies and individuals for an unlimited period of time, at no charge. The Enterprise Edition can be evaluated free of charge for 20 days.

Note:

You are eligible for free technical support during the evaluation period of all NetWrix products. If you encounter any problem or would like to get assistance with installation, configuration or implementation of NetWrix SQL Server Change Reporter, please contact our support specialists.

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 5 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

1.3 How It Works NetWrix SQL Server Change Reporter collection and reporting workflow is as follows: 1. The user runs the program and sets parameters for automated data collection and reporting on: 

SQL Server Configuration Audit - in the Freeware Edition.



SQL Server Configuration and/or Database Content Audit – in the Enterprise Edition.

2. A dedicated scheduled task which is launched periodically (every day, at 3 AM by default; it can also be launched manually when needed) collects configuration snapshots and/or audit data, and sends emails with change summaries to the specified recipients. 3. In the Enterprise Edition, if the Advanced Reports feature is enabled and configured, the task will also enable saving information about changes into a specified Microsoft SQL Server database (you can use the Database Importer tool to import data on demand, for details refer to Section 5.3 Importing Changes That Occurred Between Two Snapshots). You can run and view reports in the Report Manager through a web browser or via NetWrix Management Console. 4. Additionally, you can generate and view on-demand reports through NetWrix Management Console or via the Report Viewer utility. The following figure illustrates the NetWrix SQL Server Change Reporter workflow: Figure 1:

The NetWrix SQL Server Change Reporter Workflow Chart

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 6 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

2. GETTING STARTED This section describes the necessary prerequisites for the NetWrix SQL Server Change Reporter installation.

2.1 System Requirements 2.1.1 Hardware Requirements Before installing NetWrix SQL Server Change Reporter, make sure that your system meets the following hardware requirements: Table 2: Component

The NetWrix SQL Server Change Reporter Hardware Requirements Minimum

Recommended

Processor

Intel or AMD 32 bit, 2GHz

Intel or AMD 64 bit, 3GHz

Memory

512MB RAM

2GB RAM

Disk

50MB physical disk space for the installation

20GB. An approximate formula is 50 bytes per every configuration object for each server.

2.1.2 Software Requirements A computer for NetWrix SQL Server Change Reporter installation must run the operating system Windows XP SP3 or later and should preferably belong to a managed or trusted domain. This computer must have the following system components installed: 

.NET Framework 2.0 or later



Windows Installer version 3.1 or later



For the Enterprise Edition, Microsoft Management Console 3.0

2.1.3 Target SQL Server Requirements NetWrix SQL Server Change Reporter monitors Microsoft SQL Server versions 2000, 2005, 2008, 2008 R2 or 2012.

2.1.4 Rights and Permissions For data collection and reports generation, NetWrix SQL Server Change Reporter uses the account under which the scheduled task is run. This account must be assigned the system administrator role on the target server.

Procedure 1.

To assign the sysadmin role to an account

1.

Launch Microsoft SQL Server Management Studio and connect to the required target server.

2.

In Microsoft SQL Server Management Studio, navigate to the Object Explorer and expand Logins under the Security node.

3.

Right-click the required user and select Properties from the drop-down menu.

4.

In the Login Properties dialog, open the Server Roles page and select sysadmin on the right. Click OK.

NetWrix SQL Server Change Reporter will automatically set up all required auditing properties on your SQL Server instances during the first program launch.

2.1.5 SQL Server Required for Advanced Reporting To use the SSRS-based reports in the Enterprise Edition, you need to have Microsoft SQL Server 2005, 2008 or 2008 R2 with Reporting Services configured in your environment. Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 7 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide You can automatically install an instance of SQL Server 2005 Express Edition with Advanced Services via NetWrix Management Console, for details refer to Section 4.5 Advanced Reporting For your convenience, we have provided instructions on the manual installation of SQL Server with Reporting Services required for NetWrix SQL Server Change Reporter. For a step-by-step procedure, refer to the following technical article: Installing Microsoft SQL Server and Configuring the Reporting Services. For proper performance of the Advanced Reporting functionality, the account used by users to view reports, as well as the account for running the program scheduled task, must be assigned the Content Manager role for the SSRS Home folder.

Procedure 2.

To assign the Content Manager role to an account

1.

Start the Report Manager, open the Properties tab of the Home folder and click New Role Assignment (the path can slightly vary depending on your SQL Server version).

2.

Specify the necessary group or user account in the following format: domain\user. The account should be in the same domain or in a trusted domain.

3.

Select Content Manager and click OK.

2.2 Installing NetWrix SQL Server Change Reporter To install NetWrix SQL Server Change Reporter, run the setup wizard on any computer in the domain where the monitored SQL Servers are located. If the selected computer is running Windows Server 2008, make sure the User Account Control (UAC) is turned off.

Procedure 3.

To install NetWrix SQL Server Change Reporter

1.

Download NetWrix SQL Server Change Reporter.

2.

Run the setup package called sqlcrfree_setup.msi (for the Freeware Edition) or sqlcrfull_setup.msi (for the Enterprise Edition).

3.

Follow the instructions of the wizard. When prompted, accept the license agreement and specify the installation folder.

4.

On the last step, click Finish to complete the installation procedure. For the Enterprise Edition, select the “Launch Enterprise Edition” option before clicking Finish: Figure 2:

The NetWrix SQL Server Change Reporter Installation Wizard

Note: NetWrix SQL Server Change Reporter runs as a scheduled task, therefore it is not necessary to keep the program open once it has been configured. Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 8 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

3. WORKING WITH

THE

FREEWARE EDITION

To start using the audit and reporting functionality, you need to configure the basic product settings. In the Freeware Edition, this is performed via the configuration utility.

3.1 Configuring Basic Settings To launch NetWrix SQL Server Change Reporter, navigate to Start  All programs  NetWrix Freeware  SQL Server Change Reporter. The configuration utility dialog will open: Figure 3:

NetWrix SQL Server Change Reporter Configuration Dialog

The following configuration settings are available in this dialog for the Freeware Edition: 1. 2. 3. 4.

Enable SQL Server Change Reporter. The check box enables data collection and reporting; it is selected by default. List of servers to check for changes. To specify a path to the SQL Server instance you want to monitor, click Add. Store data to. Specify the data storage path in the text box. All snapshots made by NetWrix SQL Server Change Reporter will be stored in this folder. Report delivery schedule. By default, audit data is collected and delivered daily at 3.00 AM. After saving the initial product configuration, you can change the delivery time. Click Change to edit the schedule. On the displayed dialog, click New: Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 9 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide Figure 4:

NetWrix SQL Server Change Reporter Schedule Dialog

To set additional schedule options, click Advanced. In the Advanced Schedule Options dialog, set parameters for a new task: Figure 5:

Advanced Schedule Options

5.

Email report delivery settings. Populate the following fields: a) Send SQL Server reports to. Enter an email address to which the reports on the SQL Server changes will be delivered (multiple addresses should be separated by commas). b) SMTP Server/Port. Define the SMTP server name and port number. c) From address. Enter an email address from which reports will be sent. To check the correctness of the email address, click Verify. The system will send a test message to the specified address and will inform you if any problems are detected. 6. Additional product configuration. To set additional parameters for working with your mail server, click Advanced. The Advanced Options dialog will open:

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 10 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide Figure 6:

Advanced Options

7. Click Apply to save your configuration. On clicking Apply, you will be prompted to enter credentials for data collection and report generation: Figure 7:

Scheduled Task Credentials

Specify the account under which the scheduled task (named NetWrix SQL Server Change Reporter) will collect your SQL Servers changes data and send emails with summary reports to the specified recipients.

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 11 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

3.2 Data Collection and Reporting This section provides information on data collection tasks and explains how to generate reports via the Report Viewer tool.

3.2.1 Running Data Collection Task By default, NetWrix SQL Server Change Reporter automatically performs data collection daily at 3:00 AM. However, you can manually launch the data collection task or modify its properties, such as schedule, account and others, via the Task Scheduler. The task name is NetWrix SQL Server Change Reporter. Each time the task is running, the audit data is collected together with the SQL Server snapshots and the change summary report is sent by email. After the first data collection, an email notification is sent to confirm that the initial analysis has been completed: Figure 8:

The Initial Analysis Summary Report

Then, every time a scheduled data collection task is running, the change summary report on changes occurred since the last data collection will be sent to the specified recipient(s):

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 12 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide Figure 9:

The Change Summary Report

3.2.2 Running On-Demand Reports To generate the on-demand reports, use the Report Viewer tool available from the Start menu. Reports will return data on changes made between two selected snapshots.

Note:

Procedure 4.

The scheduled task must run at least 2 times before the on-demand reports become available.

To run an on-demand report

1. Launch the Report Viewer from the Start menu: Figure 10:

NetWrix SQL Server Change Reporter Viewer

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 13 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide 2. In the NetWrix SQL Server Change Reporter Viewer dialog, select a SQL Server instance and dates of snapshots. In the Freeware Edition, only SQL Server Configuration Audit contents can be reported via the Report Viewer. 3. Click the Generate button and select the format in which you to want to get the report: select either “HTML file” or “CSV file”. 4. In the Save as dialog, specify the location where the report will be saved. By default, the report file is saved in the user’s Documents folder.

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 14 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

4. WORKING WITH THE ENTERPRISE EDITION In the full-featured Enterprise Edition all operations are performed via NetWrix Management Console (implemented as an MMC snap-in) which enables flexible configuration and management capabilities. With NetWrix Management Console, you can: 

Handle numerous Managed Objects with a single installation;



Manage all NetWrix products configuration and settings via an integrated interface;



Enable management features for the selected objects in bulk, for example, specify report generation frequency;



Use Advanced Reporting with lots of predefined out-of-the-box reports.

To launch NetWrix SQL Server Change Reporter, navigate to Start  All programs  NetWrix  SQL Server Change Reporter (Enterprise Edition). Alternatively, select the NetWrix Management Console shortcut main page will be displayed: Figure 11:

in the Start menu. The Management Console

NetWrix Management Console

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 15 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

4.1 Creating New Managed Object In NetWrix Management Console, Managed Object is a computer collection that you monitor for changes. After you have installed NetWrix SQL Server Change Reporter, you will need to create a new Managed Object and perform its initial configuration.

Procedure 5.

To create and configure new Managed Object

1.

In NetWrix Management Console, select the Managed Objects node in the left pane. The Managed Objects page will be displayed on the right.

2.

In the right pane, click Create New Managed Object. Alternatively, right-click the Managed Objects node on the left and select New Managed Object: Figure 12:

The New Managed Object Creation Option

Note:

You can group Managed Objects into folders. To do this, right-click the Managed Objects node, select New Folder, specify folder name, and then create new Managed Object in this folder. You cannot move existing Managed Objects into the folders once they have been created.

3.

The New Managed Object wizard will open. On the Select Managed Object Type screen, select Computer Collection as a new Managed Object type and click Next:

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 16 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide Figure 13:

4.

Select Managed Object Type

On the Default Data Processing Account screen, click the Specify Account button to set a default account (\). This account will be used by NetWrix SQL Server Change Reporter for data collection and reports generation: Figure 14:

Default Data Processing Account

When selecting the account, remember that it must be granted the system administrator role. You can change the default data processing account later if needed. Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 17 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide To change the account for all Managed Objects in bulk, navigate to the Management Console tree, expand the Settings node and select Schedule. On the Report Delivery Schedule page on the right, click Change to open the Default Data Processing Account dialog and edit the account credentials: Figure 15:

Default Data Processing Account

To change the default data processing account for each Managed Object individually, select the required Managed Object in the Management Console tree, right-click it and select Properties. On the Managed Object Properties dialog, enter new credentials: Figure 16:

5.

Managed Object Properties

On the Configure SMTP Server Settings screen, specify email settings that will be used by the system to send reports: Figure 17:

Configure SMTP Server Settings

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 18 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide 6. On the Specify Computer Collection Name screen, enter the computer collection name and select the Management Account for the Managed Object. You can leave the default account as Management Account (the one you set as a default data processing account), or use a specified account: Figure 18:

Specify Computer Collection Name

7. On the Enable Features screen, select the SQL Server Change Reporter feature to monitor your computer collection: Figure 19:

Enable Features

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 19 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide Note:

The Installed Features list contains all features that can be used to monitor your Managed Object type. If you have other NetWrix change auditing products installed, the list may contain several items. The Available Features list contains all features which have not yet been installed but can be downloaded and configured for the selected Managed Object type. Select a feature to get additional information about it on the NetWrix website and download an available product version.

8.

On the Advanced Reporting Settings screen, you must specify the settings that will be used for Advanced Reporting: 

SQL Server where the product database with data collected for the reporting purposes will be stored;



The URLs of the SSRS Report Server and Report Manager. Figure 20:

Advanced Reporting Settings

Specify the following:  SQL Server. The name of the server where the NetWrix_SQL_Server_Change_Reporter database will be created.  Windows authentication. If you select this box, the default data processing account (specified on the Default Data Processing Account screen of the New Managed Object wizard) will be used to access the SQL Server database. To use SQL Server authentication, clear this box and enter a user name and password for SQL Server access.  SQL Reporting Services. Specify the SSRS Report Server and Report Manager URL. The URLs must be in the following format: http:///. You can find the correct folder names in the SQL Reporting Services Configuration Manager. To do this, first launch the SQL Reporting Services Configuration Manager (for MS SQL Express 2005 it will be Start  All Programs  Microsoft SQL Server 2005  Configuration Tools  Reporting Services Configuration) where you can find the virtual directory names under Report Server Virtual Directory and Report Manager Virtual Directory menu categories. The default names for these folders are “ReportServer$SQLExpress” and “Reports$SQLExpress” respectively. To check correctness of your entered values, click Verify. Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 20 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide 

9.

Advanced Reporting Configuration Wizard. If you wish to install a SQL Server, click Run. The Reports Configuration wizard will be launched allowing you to install and automatically configure the SQL Server 2005 Express Edition with Advanced Services.

On the Add Items to Collection screen, add items that you want to monitor: Figure 21:

Add Items to Collection

On clicking Add  SQL Server Instance you will be presented with the New Shared Object dialog. Specify the object name or add it via the Browse button: Figure 22:

Add SQL Server Instance

You can also import the computer or shared object names list via the Import button on the Add Items to Collection screen. 10. On the Configure SQL Server Change Reporter Settings screen, specify recipients of the SQL Server configuration change summaries.

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 21 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide To monitor database content, select the Enable database content audit box: Figure 23:

Configure SQL Server Change Reporter Settings

The Database Content Audit feature is supported for MS SQL Server 2005 and above. This feature enables you to set rules for the data to be monitored and therefore to receive change reports on the selected data only. Specify database columns monitoring rules via the Database Content Audit dialog. Click Specify, to open the dialog: Figure 24:

Database Content Audit

Via the Database Content Audit dialog you can: -

Add, Edit or Remove columns monitoring rules; Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 22 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide -

Select the number of data changes per SQL transaction to be included in reports.

To create a new rule, click Add. Select the rule type, Include or Exclude, and enter the required values in the Add Rule dialog: Figure 25:

Add Rule

The following column types are currently not supported: text, ntext, image, binary, varbinary, Timestamp, sql_variant.

Note:

You may notice some performance loss on the monitored database, which is a standard overhead inherent in any change auditing systems. This overhead will only affect the database you have selected for monitoring.

On the Configure SQL Server Change Reporter Settings screen, you can also configure the format of reports sent by email. Click the Configure button to edit the settings. The Change Summary Format dialog will open: Figure 26:

Change Summary Format

The following settings are available: 

Attach as a CSV file. If this option is selected, the change summary report will be sent as a file attached to an email. Otherwise, you will receive the report as a part of the email body.



Compress before sending. If this option is selected, the attached file will be sent in the compressed format.

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 23 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide 11. On the Completing the New Managed Object Wizard screen, check the details and click Finish: Figure 27:

Completing the New Managed Object Wizard

As a result, you will be presented with a message informing about successful configuration completion: Figure 28:

NetWrix Management Console Message

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 24 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide The Managed Object you have created will appear under the Managed Objects node in the left pane of NetWrix Management Console. The Managed Object details will be displayed in the right pane: Figure 29:

The Managed Object General Page

The Managed Object General tab displays the following options: 

Managed Computers: Click this button to view and edit a list of managed computers belonging to this Managed Object.



Add/Remove Features: Click this button to add or remove some particular feature for this Managed Object.



Run: Click this button to manually launch the data collection task.



Stop: Click this button to stop the data collection process.

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 25 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

4.2 Modifying Managed Object Settings This section explains how to change settings of an existing Managed Object.

4.2.1 Editing Managed Computers List To edit a list of computers added to a Managed Object, follow the procedure below:

Procedure 6. 1.

To edit managed computers list In the left pane of NetWrix Management Console, expand the Managed Objects node and select the required Managed Object. Its details will be displayed in the right pane. Select the Computers tab or click the Managed Computers button. A list of computer collection items added to the Managed Object will be shown on the Computers page: Figure 30:

2.

The Computer Collection Page

Use the following buttons to edit the list:  Add: Click this button to add a new computer to the collection.  Import: Click this button to import computer names from a file (a *.txt file with one entry per line).  Remove: Click this button to delete a computer from the list.

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 26 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

4.2.2 Modifying Feature Settings In the left pane of NetWrix Management Console, expand the Managed Objects node, expand the required Managed Object node and select SQL Server Change Reporter. The page with general settings will be displayed on the right: Figure 31:

The NetWrix SQL Server Change Reporter General Page

On the SQL Server Change Reporter page, you can add, edit or remove email addresses of the change summary recipients, enable or disable the required types of access you want to monitor, and select additional options.

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 27 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

4.3 Configuring Global Settings NetWrix Management Console provides a convenient interface for configuring the settings that will be applied to all existing Managed Objects and all NetWrix features enabled for these Objects. This chapter provides detailed instructions on how to configure the global settings. To access the Settings configuration options, expand the Settings node in the left pane. The Settings page with available options will be displayed on the right: Figure 32:

The Global Settings Page

The following Settings are available: 

Configuring Task Schedule



Configuring E-mail Settings



Configuring Repository Settings



Configuring Advanced Reporting Settings

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 28 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

4.3.1 Configuring Task Schedule To access the Schedule settings, click Schedule on the Settings page or select the Schedule node under the Settings node in the left pane: Figure 33:

Report Delivery Schedule Settings

By default, data collection and report delivery is scheduled to run daily at 3:00 AM. To add a new scheduled task click Change. If you want the task to run from a specific account, modify the Default Processing Account by clicking the corresponding Change button.

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 29 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

4.3.2 Configuring E-mail Settings To access the SMTP settings, click E-mail Settings on the Settings page, or select the E-mail Settings node under the Settings node in the left pane: Figure 34:

E-mail Settings

Click Configure to open the Configure SMTP Settings dialog: Figure 35:

The Configure SMTP Settings Dialog

Populate the dialog fields with the relevant data. If your SMTP server needs authentication, check Use SMTP authentication and enter the required user name and password. If your SMTP server needs an SSL-encrypted connection, select the corresponding option.

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 30 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

4.3.3 Configuring Repository Settings For the archiving purpose, data collected by the product is saved locally in the Repository, a file-based storage. The data is stored in accordance with the retention period you specify. To access the Repository settings, click Repository on the Settings page, or select the Repository node under the Settings node in the left pane: Figure 36:

Repository Settings

On the Repository Settings page you can do the following: 

Specify a path to the Repository (for all Windows versions earlier than Vista the default path is the following: \NetWrix\Management Console\Data. Starting with Vista, the default repository path is the following: ProgramData\NetWrix\Management Console\Data).



Define data retention period. Select Enable long-term archiving for months, and specify the required value (by default the period is 24 months). Specify the period when the data is available for review via the Session retention field (default is 60 days).



Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 31 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

4.3.4 Configuring Advanced Reporting Settings To access the Advanced Reporting settings, click Reporting on the Settings page, or select the Reporting node under the Settings node in the left pane: Figure 37:

Advanced Reporting Settings

To change your SQL Server settings, click Configure. The following dialog will appear: Figure 38:

The Reports Settings Dialog

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 32 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide On the Reports Settings dialog, you can modify the following:  Server name. The name of the server where the NetWrix SQL Server Change Reporter database will be created.  Windows Authentication. If you select this box, the default data processing account (specified on the Default Data Processing Account screen of the New Managed Object wizard) will be used to access the SQL Server database. To use SQL Server authentication, clear this box and enter a user name and password for the SQL Server access. 

SQL Reporting Services. Specify the SSRS Report Server and the Report Manager URL. The URLs must be in the following format: http:///. You can find the correct folder names in the SQL Reporting Services Configuration Manager. To do this, first launch the SQL Reporting Services Configuration Manager (for MS SQL Express 2005 it will be Start  All Programs  Microsoft SQL Server 2005  Configuration Tools  Reporting Services Configuration) where you can find the virtual directory names under Report Server Virtual Directory and Report Manager Virtual Directory menu categories. The default values for these names are “ReportServer$SQLExpress” and “Reports$SQLExpress” respectively. To check correctness of your entered values, click Verify.



Reports Configuration. If you wish to install a SQL Server, click Start to launch the Reports Configuration wizard which allows you to install and automatically configure SQL Server 2005 Express Edition with Advanced Services.

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 33 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

4.4 Data Collection and Reporting This section provides instructions on how to perform data collection and generate reports using NetWrix Management Console.

4.4.1 Running Data Collection Task By default, NetWrix SQL Server Change Reporter runs data collection (scans the monitored servers for changes) daily at 3:00 AM, and generates reports on the detected changes. To manually start the data collection task, expand the Managed Objects node in NetWrix Management Console, select your Managed Object and click Run on the Managed Object General tab in the right pane: Figure 39:

NetWrix Management Console

Then the SQL Server audit data will be collected and change summaries will be sent by email to the specified recipients. The task session information can be examined via NetWrix Management Console, as described in Section 4.4.2 Viewing Task Session Results.

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 34 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

4.4.2 Viewing Task Session Results All information on the scheduled task session is shown in NetWrix Management Console under the Sessions node. To view the session details, expand the Managed Object node in the Console left pane, navigate to node  SQL Server Change Reporter  Sessions and pick a particular session. The Session page will be displayed with the details and options available: Figure 40:

The Session Details Page

The following information is available for a session: 

Session status: Success, Warning, Error. Error occurs if data collection failed to start due to an incorrect account, remote computer powered off, or other reason specified in the Details grid on the Session page.



Type: the name of the feature that processed data during the selected session (in this case, SQL Server Change Reporter).



SQL Server: a list of the servers monitored during the session.



Details: detailed information about the audit data collection results.

To generate a report based on the data collected during the selected session, use the settings in the lower pane on the right: 

Server name. Select the server whose data will be included in the report.



Contents. Select the type of contents to report on: SQL Server configuration audit or database content audit.

Click the Generate button to get a report. From the button drop-down menu select to run the report either in the HTML or CSV format. The report will be generated and displayed in the selected format. To see a report generated earlier, click View Change Summary for this session (if the report has no history, it will be generated first and then displayed).

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 35 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

4.4.3 Viewing Scheduled Reports After the first data collection run, an email notification confirming that the initial analysis has been completed is sent to the specified recipient(s): Figure 41:

The Initial Analysis Summary Report

The Initial Analysis report will present you with the following warning message:

The Who Changed and When Changed fields in the change report may show incorrect data, because tracing was disabled on the server during report generation. Tracing is required for the change reporting process, and it has been automatically enabled for future reports. This is a standard notification, no further actions are required. Then, every time the scheduled data collection task is run, a summary report on the changes occurred since the last data collection is sent to the specified recipient(s):

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 36 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide Figure 42:

The Change Summary Report

If Advanced Reporting has been configured (as described in the Section 4.5 Advanced Reporting), you can click the More reports link from this email report to view HTML reports via your web browser.

4.4.4 Running Ad-hoc Reports To generate an Ad-hoc report, expand the Managed Object node in the Management Console left pane, then navigate to the required Managed Object node  SQL Server Change Reporter  Ad-hoc Reports. The page with report filters will be displayed: Figure 43:

Ad-hoc Report Filters

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 37 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide You can specify the following report filters: 

SQL Server. Specify the SQL Server name whose data will be included in the report.



Contents. Select the type of contents to report on: SQL Server configuration audit or database content audit.



Date range. Specify the required period; by default the period between the last two data collection sessions is set.

Set the required values and click Run. From the button drop-down menu select to run the report either in the HTML or CSV format. The report will be generated and displayed in the selected format. To cancel the report generation process, click Stop.

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 38 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

4.5 Advanced Reporting With SQL Server Reporting Services deployed, you can also configure Advanced Reporting (SSRS-based). With Advanced Reporting you can: 

Use a wide variety of reports to analyze access attempts and modifications on your SQL Servers; dozens of reports will help you stay compliant with the standards and regulations your organization is subject to (SOX, HIPAA, PCI, GLBA, SAS70, and others).



Change report filters to fine-tune the data view according to your needs.



Apply the most commonly used formats to save a report, among them PDF, XLS and others.



Apply grouping and sorting to the report data.

4.5.1 Configuring Default Advanced Reporting Settings To access and configure the default Advanced Reporting settings, click Reporting on the Settings page or select Reporting under the Settings node in the NetWrix Management Console left pane. Then click Configure in the right pane: Figure 44:

The Advanced Reporting Settings Console Page

The Reports Configuration wizard will be launched. The wizard helps you configure the Advanced Reporting settings which will be used by default.

Procedure 7.

To configure Advanced Reporting settings

1.

Launch the Reports Configuration wizard.

2.

On the first step of the wizard, select whether you want to proceed with the automatic installation and configuration of SQL Server 2005 Express Edition with Advanced Services, or use an existing SQL Server instance.

Note:

If you use an existing SQL Server instance, make sure that the Reporting Services feature is installed and configured for this server. For instructions on the manual installation of SQL Server with Reporting Services required for NetWrix SQL Server Change Reporter, refer to the following technical article: Installing Microsoft SQL Server and Configuring the Reporting Services. For full installation and configuration details, refer to the Microsoft documentation. Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 39 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide 3.

On the next step, do the following: 

If you have selected to install and configure SQL Server 2005 Express Edition with Advanced Services, wait for the automatic installation and configuration process to complete.



If you have selected to configure an existing SQL Server instance for reporting, configure the SQL Server database connection settings: Figure 45:

The Reports Configuration Wizard

Note:

A database will be created automatically on the specified server with the following default name: NetWrix_SQL_Server_Change_Reporter. By default, it will be accessed using Windows authentication with the scheduled task account. To use SQL Server authentication, clear the Windows Authentication check box, and enter the credentials for the database access.

Enter and verify the URLs of the Reporting Services: Report Server URL and Report Manager URL. The URLs must be in the following format: http:///. You can find the correct path names in the SQL Reporting Services Configuration Manager. To do this, first launch the SQL Reporting Services Configuration Manager (for MS SQL Express 2005 it will be Start  All Programs  Microsoft SQL Server 2005  Configuration Tools  Reporting Services Configuration) where you can find the virtual directory names under Report Server Virtual Directory and Report Manager Virtual Directory menu categories. The default values for these names are “ReportServer$SQLExpress” and “Reports$SQLExpress” respectively. 4.

Click Next to save your configuration.

5.

On the final step, review your settings and click Finish. Make sure the account under which you plan to view the reports has sufficient rights for the Home folder.

You can also configure Advanced Reporting Settings via the Advanced Reporting Settings page in the Management Console. To access the page, in the left Console pane expand the Managed Object node, then navigate to node  SQL Server Change Reporter  Advanced Reports. Open the Settings tab on the right pane:

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 40 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide Figure 46:

Advanced Reporting Settings

You can view the reports either on the Management Console page or by using your web browser. On the Advanced Reporting Settings page, click Navigate under Web-based Reporting. The SQL Database Access page will be displayed:

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 41 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide Figure 47:

SQL Server Reporting Services

Select the folder of the report you want to generate and set the required parameters. Click View Report to get the report: Figure 48:

The Advanced Report Example

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 42 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

4.5.2 Modifying Advanced Reporting Settings To change the default Advanced Reporting settings, in the NetWrix Management Console left pane, select Managed Objects   SQL Server Change Reporter  Advanced Reports, and open the Settings tab: Figure 49:

Advanced Reporting Settings

Select the Enable advanced reporting option if required. The following options can be used: 

Customize. Select the option to customize the current values. Then specify the necessary values: o Enter the SQL Server and product database names.

Note:

If you have multiple NetWrix products deployed, consider that each of them must use a separate database. Databases can be located on the same SQL Server.

o

If you want to connect to the database using SQL Server authentication, supply access credentials.

Note:

Alternatively, you can use Windows Authentication to connect to the database, if this option is selected, the account specified as Data Processing Account (Management Account under which a scheduled task is run) for the scheduled task will be used.

o o



Enter the SSRS Report Server and Report Manager URLs and click Verify. You can also click Run Wizard and follow the wizard steps (for example, to install SQL Server Express). For details, refer to the Section 4.5.1 Configuring Default Advanced Reporting Settings.

Web-based Reporting. Click Upload, to get a set of predefined report templates to the Report Server. To launch a web browser and open the reports in the Report Viewer, click Navigate.

To save the updated configuration, click Apply.

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 43 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

4.5.3 Viewing Advanced Reports via Web Browser To view Advanced Reports, open your web browser and type in the address for your Advanced Reports website (for details refer to Section 4.3.4 Configuring Advanced Reporting Settings). On the Report Manager web page, navigate to Home  NetWrix SQL Server Change Reporter and select the type of changes you want to see in the reports. See an example of an Advanced Report below: Figure 50:

The Advanced Report Example

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 44 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

5. ADDITIONAL CONFIGURATION The following additional configuration options are available: 

Editing Report Delivery Schedule



Including and Excluding Data from Reports



Importing Changes That Occurred Between Two Snapshots

5.1 Editing Report Delivery Schedule You can set a particular task schedule for all Managed Objects in bulk, or fine-tune scheduled tasks for each Managed Object individually. For details on how to set a task schedule for all Managed Objects, refer to Section 4.3.1 Configuring Task Schedule. To edit a scheduled task for your Managed Object in Windows 2008, follow the procedure below. NetWrix SQL Server Change Reporter uses a standard Windows scheduled task called “NetWrix Management Console - SQL Server Change Reporter - Your_x0020_Computer_x0020_Collection” (or “NetWrix SQL Server Change Reporter” in the Freeware Edition).

Procedure 8. 1.

To edit scheduled task in Windows 2008

Navigate to the Start menu  Administrative Tools  Task Scheduler and select the required NetWrix Management Console task: Figure 51:

Task Scheduler

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 45 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide 2.

Double-click the task, and go to the Triggers tab in the displayed Properties form. Click the Edit button: Figure 52:

3.

Task Properties

In the Edit Trigger form, define the required settings to fine-tune your task schedule and click OK to save the changes: Figure 53:

Edit Trigger

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 46 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

Procedure 9. 1.

To edit scheduled task in Windows 2003

Navigate to the Start menu  Settings  Control Panel  Scheduled Tasks and select the NetWrix Management Console task. The scheduled task dialog will appear: Figure 54:

2.

Go to the Schedule tab to define the required task period and time settings: Figure 55:

3.

NetWrix Scheduled Task

NetWrix Scheduled Task

Click Advanced to access the advanced schedule options: Figure 56:

Advanced Schedule Options

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 47 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide 4.

Click OK to apply the changes and close the form.

In the Enterprise Edition, you can also edit the scheduled task by clicking Schedule on the Settings page or selecting the Schedule node under the Settings node in the NetWrix Management Console left pane: Figure 57:

Note:

The Settings Console Page

Configuration via Global Settings will affect all Managed Objects.

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 48 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

5.2 Including and Excluding Data from Reports To fine-tune data collection and reporting, change the required configuration files located in the product installation folder. The files must be added with one entry per line. The following configuration files are available: 

To exclude object types from your reports, add the type names to the omitobjlist.txt file.



To exclude particular objects (database, table, column view and so on) from your reports, specify the resource paths in the omitpathlist.txt file.



To exclude particular attributes from reports, add the attribute names to the omitproplist.txt file.



To prevent saving information about certain objects to the Audit Archive, specify the resource paths to these objects in the omitstorelist.txt file.



To prevent saving the “Who changed” and “When changed” information of certain SQL Server instances, specify their names in the omittracelist.txt file.



For SQL Server to store your trace logs locally, specify the relevant UNC in the pathtotracelogs.txt file.



To change the attribute display names in your reports, edit these names in the propnames.txt file.

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 49 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

5.3 Importing Changes That Occurred Between Two Snapshots Database Importer allows you to import data on the SQL Server changes made between two snapshots. The data is imported to a SQL Server database for the advanced analysis through Microsoft SQL Server Reporting Services. This option can only be applied if at least 2 data collection tasks have run.

Procedure 10. 1.

To import data via the Database Importer tool

Launch Database Importer by navigating to Start All ProgramsNetWrixSQL Server Change ReporterDB Importer: Figure 58:

2.

In the SQL Server Change Reporter DB Importer dialog:    

3.

SQL Server Change Reporter DB Importer

Specify the SQL Server whose changes need to be imported. Select the contents type: SQL Server configuration audit or database content audit. Select the snapshot sessions. Specify the server and database where you want to import data via the Configure button.

To start the process, click Import. Data will be imported from a local repository to the specified SQL Server.

In most cases the use of Database Importer is not required, because data is imported according to the defined schedule and is automatically written to the specified database if the corresponding option is selected. However, you may need to import data manually when, for example, the database fails, or some other error occurs.

Note:

This option is available in the Enterprise Edition only.

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 50 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

A

APPENDIX: MONITORED OBJECT TYPES AND ATTRIBUTES The following table provides the list of all monitored SQL Server Configuration objects and attributes. Table 3:

The NetWrix SQL Server Change Reporter Monitored Objects

Object Type Application Role

Attributes Date Created Date Modified Default Schema Extended Properties Id Name Owned Schemas

Backup

Backup name Description Device name logical_device_name Size Type

Column

Allow nulls ANSI Padding Status Collation Computed Text Default Constraint Full Text ID Identity Identity increment Identity seed Is Computed Length Name Not for replication Numeric precision Numeric scale Primary Key Rule Rule Schema System Type XML Schema Namespace

Constraints

Date Created Date Modified Definition ID Is system named

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 51 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide MS shipped Name Published Schema published Credential

Id Identity Date Created Date Modified Name

Database

Compatibility Database Size Database Space Available Date Created Date Modified Extended Properties File Id File Group File Name Growth Id Name Options Owner Permissions Size Usage

Database Role

Date Created Date Modified Extended Properties Id Name Owner Owned Schemas Role Members

Functions

Date Created Date Modified Id Name Permissions Type Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 52 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide Login

Date Created Date Modified Default Database Default Language Disabled Enforce Password Expiration Enforce Password Policy Id Name Password Hash Server Roles

Restore

Type

Schema

Date Created Date Modified Extended Properties Id Name Owner Permissions

Server Instance

Ad Hoc Distributed Queries* Affinity I/O Mask Affinity Mask Agent XPs* Allow Updates Awe Enabled Blocked Process Threshold* C2 Audit Mode Clr Enabled* Collation Cost Threshold For Parallelism Cross Db Ownership Chaining* Cursor Threshold Database Mail XPs* Date Modified Default Full-text Language Default Language Default Trace Enabled* Disallow Results From Triggers Fill Factor (%) Ft Crawl Bandwidth (max)*

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 53 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide Ft Crawl Bandwidth (min)* Ft Notify Bandwidth (max)* Ft Notify Bandwidth (min)* Id In-doubt Xact Resolution* Index Create Memory (K) Lightweight Pooling Locks Max Degree Of Parallelism Max Full-text Crawl Range* Max Server Memory (M) Max Text Repl Size (B) Max Worker Threads Media Retention Min Memory Per Query (K) Min Server Memory (M) Name Nested Triggers* Network Packet Size (B) Ole Automation Procedures* Open Objects Permissions PH Timeout (s)* Precompute Rank* Priority Boost Query Wait (s) Query Governor Cost Limit Recovery Interval (min) Remote Admin Connections* Remote Login Timeout (s) Remote Proc Trans Remote Query Timeout (s) Remote Access Replication XPs* Scan For Startup Procs Server Trigger Recursion* Set Working Set Size Show Advanced Options SMO And DMO XPs* SQL Mail XPs* Status Transform Noise Words* Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 54 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide Two Digit Year Cutoff User Connections User Instances Enabled* User Instance Timeout* User Options Web Assistant Procedures* Xp_cmdshell* Server Role

Date Created Date Modified Id Name Role Members

SQL Job

Automatically delete job Category Date Created Date Modified Description Email notification Email operator Enabled ID Name Net send notification Net send operator Owner Page notification Page operator Schedules Write to the Windows Application event log

SQL Job Schedule

ID Name On Failure On Success Output file Process exit code of a successful command Retry attempts Retry interval (minutes) Step Type

SQL Job Schedule

Date Created Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 55 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide Date Modified Enabled ID Name Owner Schedule Type Settings Stored Procedure

ANSI NULLs Date Created Date Modified Encrypted Execute us FOR replication Id Name Permissions Quoted Identifier Recompile Schema

Table

ANSI NULLs Date Created Date Modified Filegroup Id Name Partition scheme Permissions Schema Table is partitioned Table is replicated Text filegroup

Table Keys

Name ID Date Created Date Modified MS shipped Published Schema published Disabled Not for replication

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 56 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide Not trusted Delete referential action Update referential action Is system named Triggers

Date Created Date Modified Disabled ID Instead of trigger MS shipped Name Not for replication

User

Date Created Date Modified Default Schema Extended Properties Id Name Owned Schemas Roles

View

ANSI NULLs Date Created Date Modified Encrypted Id Name Permissions Quoted Identifier Schema Schema bound

View Column

Allow nulls ANSI Padding Status Collation Computed Text Default Constraint Full Text ID Identity Identity increment Identity seed Is Computed Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 57 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide Length Name Not for replication Numeric precision Numeric scale Rule Rule Schema System Type XML Schema Namespace XML Schema Namespace schema View Index

Allow Page Locks Allow Row Locks ID Data Space ID Disabled Fill Factor Hypothetical Ignore Dup Key Name Padindex Primary Key Schema Name Type Unique Unique Constraint View Name

View Index Column

Column ID ID Included Column Index ID Key Ordinal Name Partition Ordinal Schema Name Sort Order View Name

* The product does not monitor this attribute on SQL Server 2000.

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 58 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

B

APPENDIX: MONITORED DATA TYPES The following list contains the names of all data types monitored by NetWrix SQL Server Change Reporter. 

bigint



bit



char



cursor



date



datetime2



datetime



datetimeoffset



decimal



hierarchyid



int



float



money



nchar



nvarchar



numeric



real



smalldatetime



smallint



smallmoney



table



time



timestamp



tinyint



uniqueidentifier



varchar



xml

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 59 of 60

NetWrix SQL Server Change Reporter Administrator’s Guide

C

APPENDIX: RELATED DOCUMENTATION The following table lists all documents available to support NetWrix SQL Server Change Reporter: Table 4:

Document Name

Related Documentation

Overview

NetWrix SQL Server Change Reporter Administrator’s Guide

The current document provides detailed instructions on how to configure and use NetWrix SQL Server Change Reporter.

NetWrix SQL Server Change Reporter Quick Start Guide for the Enterprise Edition

The guide provides instructions on how to start working with the program quickly and easily.

NetWrix SQL Server Change Reporter Quick Start Guide (Freeware Edition)

The document is intended for evaluation of the product Freeware Edition. It provides instructions on how to start working with the program quickly and easily.

Installing Microsoft SQL Server and Configuring the Reporting Services

The technical article provides instructions on how to install Microsoft SQL Server 2005/2008 R2 Express and configure the Reporting Services.

Subscription to SQL Server Reports

The article provides step-by-step instructions on how to configure subscription to SSRS reports.

NetWrix SQL Server Change Reporter Release Notes

The document provides a list of known issues that customers may experience while using the release version 2.5.

Copyright © 2013 NetWrix Corporation. All Rights Reserved Suggestions or comments about this document? www.netwrix.com/feedback

Page 60 of 60