InTrust 11.6 - Preparing for Auditing Microsoft SQL Server

SQL Server Auditing Overview

The SQL Server Knowledge Pack expands the auditing and reporting capabilities of InTrust to SQL Server. It lets you gather events from the SQL Server C2 log, the Error log and replication agent history, and make reports on these events.

Requirements

The Knowledge Pack is compatible with the following versions of Microsoft SQL Server Reporting Services:

• 2005
• 2008
• 2008 R2
• 2012
• 2014
• 2016
• 2017
• 2019

Auditing Requirements

To gather the C2 log and the Error log with InTrust for reporting, configure SQL Server audit as described in the related topics:

• C2 Log Requirements
• Error Log Requirements

C2 Log Requirements

C2 logging is the more comprehensive of the two logging options in SQL Server. However, it is also more resource-intensive than Error log writing, and it is turned off by default. If C2 logging is disabled on your SQL servers for performance reasons, then you can only audit events from the Error log.

If you need to collect more information than the Error log can provide, then enable C2 logging for your SQL servers.

To enable C2 logging

1. In SQL Analyzer, run the following query to the relevant SQL server to enable advanced options:
   EXEC sp_configure 'show advanced options', 1
   RECONFIGURE
2. Enable C2 audit mode:
   EXEC sp_configure 'c2 audit mode', 1
   RECONFIGURE
   The setting “1” establishes the C2 audit trace and turns on the option to fail the server should the server be unable to write to the audit file for any reason.
3. Run the following command:
   EXEC sp_configure 'xp_cmdshell', 1
   RECONFIGURE
4. Restart the SQL server.

To successfully turn on C2 logging, you must a member of the sysadmin role.

Gathering from Multiple Servers

To ensure correct operation when gathering C2 logs from several SQL servers

1. 1 On the InTrust server that performs the C2 log gathering, click Start | Programs | Administrative Tools | Data Sources (ODBC).
2. 2 On the Connection Pooling tab, double-click the name of the driver that is used for gathering, and select Don’t pool connections to this driver. If you are unsure which driver you need, check in the properties of the database events data source used during the gathering.

Error Log Requirements

Unlike the C2 log, the Error log is always enabled.

To prepare the SQL Server Error log for gathering

1. In SQL Server Management Studio, open the properties of the SQL server you need.
2. On the Security page, select the Both failed and successful logins option under Login Auditing.