InTrust 11.6 - 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.

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.

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.

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.