Chat now with support
Chat with Support

InTrust 11.4.1 - Preparing for Auditing Microsoft SQL Server

Collecting C2 Logs from Multiple Named SQL Server Instances

The following table shows differences between gathering with and without agents.

  Agents

No agents

What to include in the site One computer with the appropriate ODBC driver is enough for the InTrust site specified in the gathering job. It does not matter much what computer that is, as long as the computer has a reliable connection with the InTrust server on the one hand, and with all necessary SQL servers on the other hand.

If the InTrust server itself is connected to the SQL servers, it is a good idea to create and use a site that includes only the InTrust server.

How to configure data sources, jobs and policies

InTrust gathers from one SQL server instance at a time, so there is no point in creating many jobs.

Take the following steps:

  1. Make a copy of the appropriate C2 log data source for each named instance that you want to collect from.
  2. Configure their connection strings accordingly.
  3. Specify these copied data sources in the appropriate gathering policy.

You can also use a separate copy of the gathering policy for this purpose.

InTrust gathers from all specified SQL server instances at once if you have a separate gathering job for each SQL server instance, each using a separate gathering policy based on a separate data source.

However, SQL servers are processed one by one if you configure data sources, jobs and policies as for gathering with agents.

For more information about configuring database events data sources, see Auditing Custom Logs.

Load Balancing

Depending on how you organize gathering of SQL Server audit data, the SQL server, The InTrust server or the InTrust agent computer gets the most load. When planning the gathering, consider the following:

  • What you include in the site or sites
  • Whether or not to use agents

Method 1

You can use a site that includes one InTrust agent computer connected to one SQL server. To collect from multiple SQL servers in this way, create a similar site for each SQL server.

Gathering takes place as follows:

  1. The InTrust server passes the SQL connection string to the agent. This connection string explicitly specifies the SQL server that data must come from.
  2. The agent uses the connection string with the local ODBC driver to connect to the SQL server.
  3. The agent retrieves C2 log data and packs it.
  4. The collected data is compressed and forwarded to the InTrust server. Optionally, the data is encrypted.

Caution: When you use an agent for data gathering and trusted_connection is set to true in the connection string, then the connection to the SQL server will be created under the agent account.

In most environments, this is the preferred way to organize C2 log gathering. The InTrust agent computer gets the most load in the process without significantly affecting SQL server and InTrust server performance.

Note: The following measures can help you improve gathering performance in this scenario:

  • Running the agent on a powerful multi-core computer
  • Adding RAM to the agent computer—however, this helps only if the available RAM is insufficient

With regard to performance, it does not matter whether the agent computer runs a workstation operating system or server operating system.

Method 2

You can use a site that includes multiple SQL servers and gather from them without agents. The following figure shows this workflow:

If you organize gathering like this, include the %COMPUTER_NAME% variable in the connection string of the data source that you use. The variable will be resolved for each computer in the site, and all computers in the site will be processed one by one.

The following is a sample connection string for this gathering method:

DRIVER = SQL Server; SERVER = %COMPUTER_NAME%; UID = sa; PWD = %PASSWORD%; APP = Quest InTrust; WSID = InTrustServer

The InTrust server gets the most load in this case. Note that sequential gathering can take very long.

Method 3

You can use a site that includes multiple InTrust agents installed on SQL servers, as shown in the following figure:

In this workflow, include the %COMPUTER_NAME% variable in the connection string of the data source that you use. The variable will be resolved by each agent, and all agents will gather data simultaneously. See the description of Method 2 for a sample connection string.

In this case, the SQL servers get the most load.

Cleaning Up After Gathering

Cleanup is performed by running a cleanup SQL query. This query is specified in the data source properties on the SQL Cleanup Query tab. By default, cleanup is disabled. However, it is recommended that you enable it, unless you must retain all audit data. Cleanup helps reduce gathering time and saves disk space on your SQL servers.

To enable cleanup

  1. Expand the node of the policy that uses the data source.
  2. Open the properties of the data source.
  3. On the General tab, select the Clear log files after gathering check box.
  4. Commit the changes you have made.

The “Microsoft SQL Server C2 log” and “Microsoft SQL Server C2 log (events for reporting)” data sources are configured to delete C2 trace files after they have been gathered. If you want to keep the files, do the following:

  1. Open the properties of the data source and switch to the SQL Cleanup Query tab.
  2. Comment out the following line:

    set @HowToClean = 'delete'

Now, instead of removing the files, InTrust will keep them and append the extension “.que” to their names.

Collecting the Error Log

The “Microsoft SQL Server Error log” data source, included in the “SQL Server Error log” policy, is used for collecting the Error log. The data source properties contain the absolute path to the Error log file. You need to edit the path for your particular environment; the default path specified there is just an example.

If the Error log is written to a location other than the default, then do as follows:

  1. Make a copy of the “Microsoft SQL Server Error log” data source.
  2. Open the properties of the new data source and, on the Settings tab, click Edit.
  3. On the Path to Custom Text Log step of the wizard, correct the path.
  4. Make a copy of the “SQL Server Error log” gathering policy, and include the new data source in it. Use the new policy in your Error log gathering job.

If you want to gather Error logs of named SQL server instances, then do as follows for each named instance:

  1. Make a copy of the “Microsoft SQL Server Error log” data source.
  2. Open the properties of the new data source and, on the Settings tab, click Edit.
  3. On the Path to Custom Text Log step of the wizard, correct the path.
  4. Continue to the next step of the wizard, select the regular expression and click Edit.
  5. On the dialog box that appears, click Add next to the Field Mapping list box, and name the new field “Insertion String #2”.
  6. Specify the name of the named instance as the value of “Insertion String #2”.
  7. Complete the wizard.
  8. Make a copy of the “SQL Server Error log” gathering policy, and include the new data source in it. Use the new policy in your Error log gathering job.

Note: The log name is specified in the Log name field on the Settings tab in data source properties. For the SQL Server Error log, make sure “SQL Server Error log” is specified in that field. Otherwise, your Error log-based reports will not work properly.

Related Documents