Chat now with support
Chat with Support

InTrust 11.6.1 - Preparing for Auditing Microsoft SQL Server

Setup

The Knowledge Pack is installed as part of the extended InTrust deployment.

InTrust Objects

InTrust setup includes the following InTrust objects related to SQL Server:

  • Data sources:
    • Microsoft SQL server C2 log
    • Microsoft SQL server C2 log (events for reporting)
    • Microsoft SQL Server Error log
    • Microsoft SQL server replication agents history
    • Microsoft SQL server miscellaneous replication agents history
  • Gathering policies:
    • SQL Server C2 log
    • SQL Server Error log
    • SQL Server replication agents history
  • Import policies:
    • SQL Server C2 log
    • SQL Server Error log
    • SQL Server replication agents history
  • “SQL Server logs daily collection” task
  • “All SQL servers in the domain” site

After installation, the site, the task and the policies do not require modification and are immediately ready for use. However, you can change the default settings to make the workflow fit your environment better. For instance, you may want to automate report creation by adding a reporting job to the “SQL Server logs daily collection” task.

Data sources must be configured for your environment, as described in the next chapter.

Report Pack

After you have completed the InTrust installation wizard, the reports appear in a new “InTrust | InTrust for Servers and Applications | SQL Server" report set in the Knowledge Portal.

Collecting SQL Server Data

Collecting the C2 Log

Collecting the Error Log

Collecting Replication Agent History

Using the Collection Task

Collecting the C2 Log

The “SQL Server C2 log (events for reporting)” data source, included in the “SQL Server C2 log gathering” policy, is used for collecting the C2 log. This is a lean data source designed strictly for reporting purposes. It helps you save bandwidth, database storage and processing time. This data source is used by default.

The other C2 log data source, “SQL Server C2 log”, lets you gather all events. Use it for purposes other than reporting, for example audit data archiving. This data source is not used by default.

Before you can start gathering, you must edit the data source you are using so that it suits your environment, as follows:

  1. In InTrust Manager, expand Configuration and select Data Sources.
  2. Open the data source’s properties in the right pane.
  3. On the Connection String tab, click Create to specify the correct SQL ODBC driver, SQL server and credentials.

Note: Note the following possible issues:

  • InTrust gathering jobs expect C2 logs to be unlocked. The log can become locked, for example, if you open it in SQL Profiler or if a gathering job that started earlier is still processing it. To gather the C2 log successfully, make sure you avoid situations when it gets locked.
  • You cannot gather events from the current C2 log file. Events from the file are accessible only after a newer file is created.
  • When you gather replication agent history, you may get warning messages that result in duplicate “No replicated transactions are available” events in reports. To work around this problem, you can exclude these events from reports using report filters.

If you want to collect C2 logs from several SQL servers, your course of action depends on whether they are default instances or named instances.

Collecting C2 Logs from Multiple Default SQL Server Instances

Specify the “All SQL servers in the domain” site in the gathering job.

In the connection string of the C2 log data source that you are using, insert the variable %COMPUTER_NAME%, as follows:

SERVER = %COMPUTER_NAME%;

This variable is resolved as the name of the SQL server from which data is gathered. The list of SQL servers is obtained from the site.

Note: These actions will be successful only if the same credentials are required by all the relevant SQL servers.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating