To create a trace, you execute the stored procedure sp_trace_create. This stored procedure takes several arguments, including an OUTPUT variable that records the trace identifier, options for the trace, and the name of the file to write the trace to. Web Listing 1 (http://www.secadministrator.com, InstantDoc ID 25728) shows a SQL Server script that creates a trace. Simply type the commands into a file called createtrace.sql, log in to the system with an account that has DBA privileges, and type
osql —E —S
<servername> —i createtrace.sql
Createtrace.sql creates the log file C:\sqlauditfile.trc. (The database server appends the suffix automatically, even if you specify a .trc filename extension.) The value 6, which appears on createtrace.sql's second line, represents the bit mask that defines the trace's options. Createtrace.sql sets the bits TRACE_FILE_ROLLOVER (2) and SHUTDOWN_ON_ERROR (4). When the trace file reaches its maximum size, SQL Server stops it and appends an integer value to its filename. For example, when sqlauditfile.trc becomes full, SQL Server names it sqlauditfile.trc_1 and creates a new sqlauditfile.trc file. If a file called sqlauditfile.trc_1 already exists, SQL Server uses sqlauditfile.trc_2 for the old file, and so on. The default maximum size for a log file is 5MB, but you can change this default value by specifying an additional argument to the stored procedure. Createtrace.sql's PRINT statement displays the trace identifier issued to the newly created trace. This identifier is required to configure and start the trace.
After you create a trace, you need to use the stored procedure sp_trace_setevent to configure it. This stored procedure's parameters include the event to audit, the columns of information to record, and a bit value that turns auditing on. Several columns of information can be associated with each event. The Security Audit Data Columns page in BOL outlines the information available for each audit-event category. (Events are grouped into event categories.) In BOL, you'll also find a list of auditable events and columns in the Transact-SQL description of sp_trace_setevent. Web Listing 2 shows a trace-configuration script that records the username, domain name, client host name, client process ID, client application name, and server name for all login and logout activity (including failed logins). You'll need to replace traceid with the value that TraceIdOut in Web Listing 1 returns. The first value after the trace identifier is the event you want to record. In the example, event 14 is a successful login, event 15 is a logout, and event 20 is a failed login. The third value is the column you want to record. In the example, I'm recording the NTUserName (6), the NTDomainName (7), the HostName (8), the ClientProcessID (9), and the client ApplicationName (10). The fourth and final argument to the stored procedure is whether to turn the column on or off. This bit value is defined at the top of the listing.
You must use sp_trace_setstatus to begin a trace. This stored procedure takes two arguments: the trace identifier and the status. The value 0 stops a trace, 1 starts a trace, and 2 closes and deletes resources allocated to a trace. You can close and delete a trace only after you stop it.
You can use Profiler to examine trace files, just as you would examine the C2 audit logs. As with audit logs, Profiler can't open a file that SQL Server is using. You'll need to either close and delete a trace before viewing the trace file or wait for a trace-file rollover to occur. Stopping a trace doesn't make a trace file available to Profiler.
As I mentioned earlier, whenever SQL Server restarts, all trace configuration information is lost and requires reentry—a time-consuming and problematic proposition. You need to reenter the commands necessary to enable tracing as soon as the server starts but before a user logs on. Web Listing 3 is a stored procedure that combines Web Listing 1 and Web Listing 2 (replacing traceid with the value that TraceIdOut returns) with the necessary invocation of sp_trace_setstatus. To configure this stored procedure to run at SQL Server start-up, use the stored procedure sp_procoption as follows:
exec sp_procoption
N'<procname>', 'startup', 'on'
After you're comfortable using server-side trace files to record security-related activity on your database server, you can experiment with filters. You use the sp_trace_setfilter stored procedure to define filters, which let you—among other things—record only activity that matches a certain pattern, such as a SQL verb (e.g., SELECT, INSERT, UPDATE, DELETE), or that involve a particular object (e.g., a specific table).
Monitor the contents of the logs that SQL Server traces and filters generate. Over time, you'll be able to craft an auditing policy that meets the needs of your organization and minimizes the performance impact that's typically associated with auditing. If, however, your database systems are used infrequently and have sufficient capacity, you might be able to do without traces and filters and stick with the C2 auditing mode.
End of Article
Prev. page
1
2
[3]
next page -->
