Companies that have critical data stores must ensure that their data is secure by monitoring, tracking, and reporting changes. Any unwanted change—whether inadvertent or malicious—can have a significant impact on a company's bottom line and its very survival. For example, undocumented, unauthorized changes can lead to extended downtimes. Therefore, the ability to audit SQL Server instances is beneficial. And in companies subject to Payment Card Industry (PCI), Sarbanes-Oxley (SOX) Act, and Health Insurance Portability and Accountability Act (HIPAA) regulations, it's a legal compliance requirement to audit their systems.
Designing an auditing system in-house requires a herculean effort because the native auditing tools in SQL Server lack many important features crucial to meeting security requirements. Plus, they don't have any built-in reporting capabilities. Fortunately, automated, intelligent software is available to monitor, track, and report changes occurring on multiple SQL Server instances across a company.
I recently tried NetWrix's SQL Server Change Reporter, which is part of the Change Reporter Suite. This suite consists of modules for automated auditing of the entire IT infrastructure, including SQL Server, Active Directory (AD), Microsoft Exchange, SharePoint, file servers, appliances, and virtual and physical infrastructures. The good news is that you can purchase each module separately. The licensing for SQL Server Change Reporter is based on the number SQL Server instances you want to audit.
Installing and Configuring
Installing SQL Server Change Reporter was fairly straightforward. I installed the software on a SQL Server 2008 R2 instance with no problems. According to the documentation, the current version of SQL Server Change Reporter supports all editions of SQL Server 2000, SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2. The product supports both 32-bit and 64-bit versions of Windows 2000 and later.
When configuring SQL Server Change Reporter, you can define a computer collection, enable features, configure email SMTP and account settings, and even configure advanced reporting options, which use SQL Server Reporting Services (SSRS). Therefore, during configuration, you'll need to have some information handy, such as your SMTP and SSRS server information.
Auditing
You can use SQL Server Change Reporter to monitor administrative changes to SQL Server. The software audits and reports changes made to configurations, databases, permissions, and other objects. (For a full list of monitored objects, see the "NetWrix SQL Server Change Reporter Monitored Object Types andAttributes" document.) The level of detail is very granular. Even modifying a database option, such as changing the recovery model from SIMPLE to FULL or changing an index on a table, will be reported.
To see how the software performs, I made several manual and scripted changes in my SQL Server environment and ran the collector both manually and on a scheduled basis. For example, I modified the SQL Server configuration through the SQL Server Management Studio (SSMS) GUI and the system stored procedure sp_configure. I also added, dropped, and modified logins, tables, columns, indexes, and more. The final result was that SQL Server Change Reporter successfully captured and reported on all the changes and provided corresponding forensic details.
One major drawback to the software's current feature set is that it lacks the ability to capture changes to actual data records. The ability to track changes made through Data Manipulation Language (DML) statements (i.e., INSERT, UPDATE, and DELETE statements) is crucial to ensure data integrity and is already available as a native feature (Change Data Capture) in SQL Server 2008 and later. According to a NetWrix representative, this long sought-after feature will be included in the next release.
Reporting
An effective auditing solution will tell you the four W's: Who did What, When, and Where. When I first launched the Report Viewer in SQL Server Change Reporter, it was very easy to discern the four W's, as Figure 1 shows.
Figure 1: Report Viewer in SQL Server Change Reporter
Report Viewer makes it easy to generate predefined reports on selected sessions. There's a convenient report delivery scheduler in the management console, and you can receive reports by email if desired. Saved reports can be viewed through a web interface, which lets you filter the view. As you can see in Figure 1, the labels for the different types of changes (e.g., Added, Modified) are color coded, which makes the reports easy to review.
You can extend the reporting capabilities by configuring Advanced Reporting, which leverages SSRS. You'll be able to access dozens of reports that have sorting, filtering, and grouping capabilities. You can view NetWrix's available SQL Server reports on the NetWrix Report Samples web page.
In addition to viewing the audit data in reports, you can use this information for troubleshooting and forensic and root-cause analyses. You can store the audit data for seven years (which meets the federal government's criteria for records retention) in the product's central repository.
An Effective Change-Auditing Tool
To be effective at tracking SQL Server changes, it's important to have a sensible strategy and an auditing tool that's flexible enough to meet all your needs but doesn't get in the way of your strategy. Having a sound audit policy and a good change-auditing tool such as SQL Server Change Reporter provides the additional benefits of helping you improve the management and stability of your SQL Server environment.
|
SQL Server Change Reporter 2.2
|
