How does Netwrix Auditor for SQL Server collect data

This article will explain how Netwrix Auditor for SQL Server works and what data it collects
Email It to Me Print this Page
Question How does Netwrix Auditor for SQL server work? What is the data source for it?
Answer There are two options of monitoring SQL servers within Netwrix Auditor.
 
1.     Audit SQL server configuration changes

To find these changes Netwrix Auditor for SQL Server collects a state snapshot from server, compares with previously taken, and determines what was changed.
 
To get WHO CHANGED \ WHEN CHANGED information for found changes the product uses internal SQL server traces. If there is no tracing enabled – changes will be reported as made by system.
 
That is why the product checks if internal SQL audit mechanism is enabled and enables it if needed during every data collection as follows:
 
When enabling internal SQL traces, the following parameters are used:
@pathtolog = retrieved from SQL server.
@option_value = 2
@max_file_size = 100
@max_rollover_files = 6
@on=1
@create_trace = 0
@create_filter_trace = 0
@create_filter_stmt_trace = 0
@traceName = @pathtolog + N'netwrix sql cr trace'
@traceFilterName = @pathtolog + N'netwrix sql cr filter trace'
@traceFilterStmtName = @pathtolog + N'netwrix sql cr stmt trace'
 
Then audit triggers are enabled by means of the
exec sp_trace_setevent @traceName_id,%eventID%, @current_num,@on command.
 
The following triggers are enabled:
·         102 --Audit Statement GDR Event
·         103 --Audit Object GDR Event
·         104 --Audit AddLogin Event
·         105 --Audit Login GDR Event
·         106 --Audit Login Change Property Event
·         108 --Audit Add Login to Server Role Event
·         109 --Audit Add DB User Event
·         110 --Audit Add Member to DB Role Event
·         128 --Audit Database Management Event
·         129 --Audit Database Object Management Event
·         130 --Audit Database Principal Management Event
·         131 --Audit Schema Object Management Event
·         135 --Audit Database Object Take Ownership Event
·         152 --Audit Change Database Owner
·         170 --Audit Server Scope GDR Event
·         171 --Audit Server Object GDR Event
·         172 --Audit Database Object GDR Event
·         176 --Audit Server Object Management Event
·         177 --Audit Server Principal Management Event
(more details about these triggers can be found here http://technet.microsoft.com/en-us/library/ms186265(v=sql.105).aspx)

The list of variables and events can be easily found in the sqlcr_db.sql file located in the program installation directory (by default - C:\Program Files (x86)\NetWrix\SQL Server Change Reporter)
All internal traces are stored in .trc files in the SQL server LOGs folder - by default it is C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\
There is a size limit for these files - 100 Mb per file. It is allowed to create 6 trace files so the maximum log size is 600 Mb. The oldest one gets automatically removed once the size limit is reached.
 
More information about internal SQL server audit can be found here http://technet.microsoft.com/en-us/library/ms191006%28v=sql.105%29.aspx
 
Also the detailed list of monitored objects can be found in Appendix A of the SQL server change reporter admin guide
http://www.netwrix.com/download/documents/NetWrix_SQL_Server_Change_Reporter_Administrator_Guide.pdf
 
2.   Audit database content changes
 
For more in-depth auditing of SQL databases Netwrix Auditor - SQL server - database content auditing can be enabled. If this option is enabled, the product performs the following steps:
a.     For each database it checks for the corresponding owner record in Master database and on current database, and enables TRUSTWORTHY property (http://technet.microsoft.com/en-us/library/ms187861.aspx) for the current database.
b.    Creates an additional database - NetwrixSQLCRaudit, that is used to store information about all changes to other databases.
c.     Creates a trigger called Netwrix_audit_trg_%tablename% in every table of monitored databases that logs transactions info to the NetwrixSQLaudit database.
d.    Creates additional table - dbo.Netwrix_Audit_errors - in each database and is used to store info about all errors that occurred during the audit process.
 
Data on Who changed is again got from internal SQL traces.
 
The product itself runs on a scheduled tasks, gathers all .trc files and information from NetwrixSQLaudit DB, and generates reports based on this information. All dbo.Netwrix_Audit_errors tables and NetwrixSQLaudit database get cleared once data is collected from them.
By default it is done once a day.
 
All changes which are performed within the 2nd option (enabling database content auditing) can be reverted by running the script “sqlcr_remove_audit_from_db.sql” located in the program installation directory against each affected database. To run this script, open it in SQL Server Management Studio, connect to the target SQL Server instance, type the target database name in square parentheses [ ] and click the "Execute" button.
Was this information helpful?