SQL Server Magazine

Creating and maintaining accurate SQL Server 2005 logs won't necessarily drive your business to sell more widgets, increase customer satisfaction, or bill more hours, but logging database-usage information for auditing is a common requirement in today’s business and legal environment. Database audit-logging requirements run the spectrum from “none needed” to “it’s critical that we log everything” and will chiefly depend on the auditor. A DBA might need to track high-level usage information for capacity planning. A corporate security group might want proactive notification of failed access attempts. A compliance officer might have a list of specific information required by statute. SQL Server 2005’s built-in audit-logging tools — the SQL Trace utility; its client application, SQL Server Profiler; and C2 audit mode — can help DBAs perform various degrees of auditing along that spectrum. We’ll briefly review all the audit-logging options in SQL Server 2005, then walk through how to use SQL Trace and SQL Server Profiler. Finally, we’ll take a quick look at upcoming auditing improvements in SQL Server 2008 and the option of using third-party auditing tools.

Built-In Logging Tools
SQL Server 2005 has several built-in, audit-logging options. Of these, SQL Trace is the most straightforward, out-of-the-box option for audit logging a SQL Server 2005 instance. SQL Trace records event information for SQL Server instances, and SQL Server Profiler provides a GUI view of your trace results.

In addition to SQL Trace and SQL Server Profiler, SQL Server 2005 provides the following options for audit logging:

• Data definition language (DDL) and data manipulation language (DML) triggers. An application developer can use DDL and DML triggers to leave a thorough record of SQL Server actions. Additionally, you can use SQL Server Service Broker in conjunction with triggers for audit logging. Service Broker uses extensions to DML to provide queuing and asynchronous messaging between SQL Server 2005 instances — to make the audit workload asynchronous to the application workload. When a database event occurs, a trigger sends information about the event to an auditing database via a Service Broker message. You can find more information about using SQL Server Service Broker for auditing at http://www.sqlteam.com/article/centralized-asynchronous-auditing-with-service-broker.
• Transactional replication. Transactional replication is a data-distribution method whereby incremental data changes are replicated from the source system (Publisher) to recipient systems (Subscribers). (For more information about replication methods, see “SQL Server 2005 Replication,” February 2007, InstantDoc ID 94413.) Transactional replication has limited audit-logging value. It's possible to alter the transactional replication subscriber-side stored procedures to record the publisher DML. However, since you can’t pass client credentials to the subscriber, there's no way to record which user carried out a particular task.
• Application audit logging. In some cases, it may make the most sense to have applications write their own audit logs However, applications might not know everything that happens in the database at their behest, so they might not be able to record all the information needed for audit logging. If you need to log only binary large objects (BLOBs), this is your best option.

Using SQL Trace
SQL Trace consists of a set of stored procedures that record event information for a SQL Server instance. SQL Trace writes in a proprietary format to .trc files, which can be read either by using SQL Server Profiler or the sys.fn_trace_gettable system function.

To define your own trace by using stored procedures, call sp_trace_create to create a new trace. Then specify an output parameter (to capture the ID of the new trace), the option parameter value, the maximum file size, and an output filename. Be sure to set the option parameter to 2 if you’d like SQL Trace to create a new file whenever the current file fills up (i.e., rollover). Otherwise, your trace will last only until the first file reaches its maximum size.

Next, specify the events to capture by using the sp_trace_setevent stored procedure, specify any event filters to reduce overhead, and call sp_set_tracestatus — setting the status of your trace to 1. The sample code in Listing 1 demonstrates a typical trace.

The first line of this trace definition declares a variable to hold the newly created trace ID. The first EXEC statement creates the trace, outputting the ID. It specifies an option value of 6, which is 2 (trace_file_rollover) + 4 (shutdown_on_error). It also provides a file path and specifies a 5MB file size. It doesn’t specify a maximum number of rollover files. Because we’ve added 4 to the option value, this trace will stop the SQL Server instance if the file share specified becomes unavailable for any reason. This stoppage would be inconvenient, but if an audit log of the databases on the instance is important enough, that could be an acceptable risk. Clearly, one should consider the consequences of using shutdown_on_error carefully.

The second EXEC statement adds a single event to the trace. This event is 109 Audit Add DB User Event, and column 7 is ClientProcessID. You can find documentation for all the available events and columns in the sp_trace_setevent SQL Server Books Online (BOL) topic at http://msdn2.microsoft.com/en-us/library/ms186265(SQL.90).aspx.

Keep in mind that server-side traces don’t persist. If a SQL Server instance is restarted, you need to restart the trace. To automate restarting the trace, create a stored procedure in the master database that contains a trace definition. You’ll need to dynamically generate a trace filename, or the stored procedure will fail because of a filename collision. The following code sample shows one way to generate a trace filename:

Declare @FileName nvarchar(100)
set @FileName = '\\Server\Share\'+ ‘startuptrace_’ + convert(nvarchar(25),
GETDATE())

Then use sp_procoption to specify that your trace stored procedure should be executed when the server starts, as follows:

sp_procoption sp_name, StartUp,
  True

Insert your own stored procedure name for sp_name.
A great resource for trace-script writers is a trace script for Common Criteria Compliance that the SQL Server 2005 SP2 product group built. It performs the same basic function as the Listing 1 trace but is much more comprehensive. For example, it defines the trace in a stored procedure, dynamically retrieves the instance path, makes the stored procedure a startup procedure, prints diagnostic information, and sets a large number of trace events for a comprehensive log. (For more information about Common Criteria Compliance, see the sidebar “Common Criteria Compliance,” InstantDoc ID 98815. You can see the Common Criteria Compliance trace script at https://members.microsoft.com/sqlcommoncriteria/EAL1_trace.sql.

To enable SQL Server to find your stored procedure at startup, the scan for startup procs advanced configuration option must be set. Using sp_procoption to mark a stored procedure as an autoproc will cause SQL Server to automatically set the scan for startup procs option for you.

Be aware that using SQL Trace has a performance cost and places some processor and I/O overhead on the SQL Server engine. However, SQL Trace imposes a small amount of overhead as compared with SQL Server Profiler, whose overhead is significantly greater (more about this shortly). For tips on mitigating SQL Trace’s overhead, see the BOL article "Optimizing SQL Trace" at http://msdn2.microsoft.com/en-us/library/ms187023.aspx.

   Prev. page   [1] 2     next page