After you connect to the database and receive a prompt, you can use the system stored procedure sp_configure to set the c2 audit mode option to 1. Doing so updates an internal SQL Server variable that controls the auditing functionality. If no errors occur, the system will prompt you to run the RECONFIGURE statement, as Figure 2, page 10, shows, to effect the change. If you can't change the audit mode—that is, if you get an error message and the system displays a list of settings—you first need to use sp_configure to set Show advanced options to 1. After you set the audit mode, you'll need to stop and restart the database server for logging to commence.
SQL Server Audit Logs
After you enable C2 auditing for the default database or for an instance, the database server will log all activity to the data directory that you specified during the installation process. (SQL Server doesn't let you log auditable events to an alternative location.) This directory holds the databases that SQL Server initially created. This directory is also the default location for all new databases and their transaction log files. You need to be aware of some implications of this configuration.
First, on a system that has limited disk space, you might find that your databases can't grow because audit log files are consuming all the free space. Second, on a busy system, performance might suffer because both the databases and the audit logs use the same disk. In general, you should store databases and their transaction logs on separate, dedicated disk devices so that you can avoid these two problems.
SQL Server writes all auditable activity to a file with the format audittrace_YYYYMMDDHHMMSS.trc, where YYYYMMDDHHMMSS is the log's creation time by year, month, day, hour, minute, and second. When a log reaches a maximum size of 200MB, SQL Server automatically creates a new log and begins to record to the new log instead. This feature lets you safely move old log files out of the data folder or delete them.
If SQL Server can't write to a log file—for example, if the disk contains no more free space—it will halt all execution. SQL Server won't restart until it can resume logging. If you need to force SQL Server to run even though logging isn't possible, you can modify the arguments to the service settings in the Service Control Manager (SCM), which is available through the Microsoft Management Console (MMC) Computer Management snap-in. Alternatively, you can use the -f flag to start a minimal SQL Server configuration from the command line. You might consider using the -m flag with the -f flag. Doing so starts the database in single-user mode, preventing clients from connecting to the database and performing transactions while auditing is disabled.
Understanding the Audit Log
When C2-level auditing is enabled, the system uses a default profile of auditable events to determine what to log. These auditable events, which are necessary in any implementation to meet C2 certification requirements, include the execution of stored procedures, the creation or deletion of objects such as tables, and user login and logout activity.
You can use SQL Server Profiler to view the log files that SQL Server creates. To open a log file, click SQL Profiler in the Microsoft SQL Server program group. Select File, Open, and click Trace File. Use the Open dialog box to navigate to the SQL Server data folder (the folder you specified when you installed SQL Server), and open the log file that you want to view. You can't open the log file to which SQL Server is logging activity. If you need to access data in the current audit log, you'll need to wait until it fills up and the system creates a new log file, or simply stop and restart SQL Server.
Figure 3 shows a typical log file that user activity generated. The fragment shows the results of a DBA adding a user to a fixed role (db_owner), user login activity, the creation of a table, and the querying of other tables. The system captures much more information for each audited event than the figure shows. Of particular interest are the EventClass column, which identifies the recorded auditable event; the TextData column, which contains the command (typically a stored procedure or SQL statement) that was executed in the database; the NTUserName and NTDomainName columns, which identify the user who ran the command; the ObjectName and DatabaseName columns, which identify the table, view, or stored procedure that was manipulated or invoked; and the StartTime column, which records when the command was issued. Of secondary interest might be the RoleName, TargetUserName, TargetLoginName, and DBUserName columns. Not all columns are populated for each event. SQL Server Books Online (BOL) discusses which columns are populated for each type of event and describes how you should interpret the values found in each column.
Unfortunately, security administrators will need to dig through the logs to determine what's going on in their databases. For example, when a stored procedure is executed, several entries in the log will appear for each statement that was executed. Navigating the entries can take time and requires knowledge about the called stored procedures to determine exactly what's happening and how the database was affected. The audit log records the arguments to any given stored procedure; to retrieve these arguments, you can select the first entry that records the invocation of the store procedure. The arguments appear in Profiler's bottom pane.
Using Server-Side Traces
One disadvantage of using SQL Server 2000's C2 auditing functionality is its all-or-nothing approach. You can't instruct SQL Server which event categories to audit, and you can't instruct SQL Server to record access to particular objects (e.g., tables)—as you can in Win2K.
To achieve a fine-grained approach to these types of auditing, you'll need to turn to Server-Side Traces. SQL Server has long provided the ability to trace activity for debugging and performance-monitoring purposes. You can also use Server-Side Traces to monitor security-related activity. Unlike C2 auditing, traces aren't persistent. If you restart SQL Server, the trace is lost and you must recreate it. Another difference between C2 auditing and traces is that you can start and stop a trace without restarting the database service. Traces are extremely configurable; you can choose exactly which events to audit and what information about each event to record.
Prev. page
1
[2]
3
next page 
