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.) 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.” 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."
C2 Audit Mode
C2 audit mode is a SQL Server 2005 feature that supports out-of-the-box audit logging. It logs all audit trace events, including every data column. A C2 audit mode log is identical to a SQL Server trace that includes every column of every security audit event.
C2 audit mode also utilizes a slight amount of system resources. I tested C2 audit mode at the Texas Microsoft Technology CXenter in Austin, by performing the same traces with C2 audit mode enabled, then disabled. C2 audit mode imposed a 3.6 percent performance penalty — as measured by time of workload execution. The test was run on a dedicated, single-processor test system with a mixed test workload provided by a second system running a Transaction Processing Performance Council benchmark B (TPC-B)-like script. With C2 audit mode off, the workload averaged 47 seconds over repeated tests. With C2 audit mode on, the same workload averaged 48.7 seconds. Average CPU use was 1.1 percent higher with C2 audit mode on — 57.89 percent versus 59.04 percent.
If all you need is basic “who logged in and accessed what” audit logging, create a trace that includes the Audit Schema Object Access event (all columns) and the Audit Login event (all columns) within the Security Audit event group.
To determine whether or not C2 audit mode is turned on, you can use the following T-SQL statement, which returns the current value of the C2 audit mode option:
SELECT value FROM sys.configurations WHERE name = ‘c2 audit mode’; GO
If the result is 1, C2 audit mode is enabled; if 0, it’s disabled.
To turn on C2 audit mode, run the T-SQL code in Listing 2. You’ll need to restart the instance for the setting to take effect. If you prefer to use the GUI rather than T-SQL to enable C2 audit mode, open Object Explorer and right-click a server. Then select Properties and the Security page. Click the Enable C2 audit tracing check box. Then restart the instance.
With C2 audit mode enabled, SQL Server will write information to a series of .trc files in the instance data directory. Once a file grows to 200MB, it’s closed and a new file is created. If the data directory runs out of space, the instance will immediately stop. To recover from this catastrophe, you’ll either need to clear some space or restart the instance from the command line with a -f flag (to disable audit logging).You can find more information about starting a SQL Server 2005 instance in the BOL “How to: Start an Instance of SQL Server (sqlservr.exe)” topic. Once you have a command line open to the appropriate binn directory, entering sqlservr -f will start SQL Server in minimal configuration and will place SQL Server in single-user mode.
You can disable audit logging by using the T-SQL statement in Listing 3. Press Ctrl+Break in the command window to stop the instance, then start the instance as you normally would. You can read the C2 audit mode .trc files either by using SQL Profiler or the sys.fn_trace_gettable system function.
There’s no way to change C2 audit mode files’ location from the instance data directory. You can change the default location of the data directory by using the Server Properties dialog Database Settings page.
SQL Server Profiler
SQL Server Profiler lets DBAs and developers record and analyze exactly which T-SQL statements were submitted to the server and how the server accessed the database to return result sets. You can also use SQL Server Profiler to automate the SQL Trace script-creation process. Although some DBAs may find using SQL Server Profiler easier than SQL Trace, Profiler’s performance overhead makes it an inefficient tool for production audit logging.
If you use Profiler to run a trace, SQL Trace starts an in-memory (or rowset) trace to output trace records to the Profiler GUI as they occur. If you choose to save the trace to a file, Profiler will write the file unless you check the Server processes trace data check box, in which case SQL Trace will take care of writing the file.
Profiler’s performance penalty is far greater than SQL Trace’s. The same test workload used in the C2 audit mode testing ran 71 percent slower compared with no logging when Profiler was used. Profiler also imposed an additional 17 percent CPU load. (This was without checking the Server processes trace data check box.) Running the same test with SQL Server processing the trace data rather than Profiler resulted in somewhat better results; the workload ran 47 percent slower and used 10 percent more CPU than the workload running with no logging.
When the test workload was analyzed with Profiler running on a remote machine, the test workload’s performance was almost five times worse than when Profiler was run on server’s console session. CPU load on the server actually decreased while the SQL Server process waited for the remote Profiler to keep up. Because rowset traces can greatly slow SQL Server, you’ll want to find out whether a SQL Server instance has any active rowset traces. To do so, run the following query:
SELECT * FROM sys.traces WHERE is_rowset=1 AND status=1
You could even set up a scheduled job to run this query every so often to alert administrators to rowset traces on production servers.
A better way to use Profiler is to first determine what you need to log, then set up a server-side trace by using SQL Trace, and use Profiler to build the appropriate trace. You can tell Profiler to export a trace script to a .sql file; to do so, navigate to Profiler’s file menu, select Export, then select Script Trace Definition). Profiler will write the sql file to disk. You then use the .sql file as your SQL Trace definition. You might find this approach preferable to hand-coding a trace script, especially if you’re tracking a large number of events and data columns.
Default Trace
In SQL Server 2005, a default trace is always running in the background, providing a persistent log of configuration activity and changes. It maintains the log data necessary to help you diagnose problems when they first occur, including the following:
- events related to database file auto-grow and auto-shrink
- instance errors and warnings
- full-text–search errors
- object creation, deletion or alteration
- audit events like login, login failed, schema ownership changes
To read all events recorded in the past 24 hours from the default trace, execute the T-SQL statement in Listing 4. To turn off the default trace, set the advanced configuration Default Trace Enabled option to 0 using sp_configure. To verify that the default trace is running, use the following query:
SELECT * FROM sys.configurations WHERE configuration_id = 1568
For more information about Default Trace, see Vipul Shah’s blog at http://blogs.technet.com/vipulshah/archive/2007/04/16/default-trace-in-sql-server-2005.aspx.
SQL Server 2008 Audit-Logging Improvements
Auditing in SQL Server 2005 is a do-it-yourself enterprise using triggers, logs, SQL Trace, and SQL Server Profiler. In contrast, Microsoft SQL Server 2008 promotes auditing to a first-class server object. You can do granular audit actions on database objects and users and write audit information to multiple outputs, including files, Windows application logs, and the security event logs.
SQL Server 2008 contains a new, high-performance, lightweight tracing infrastructure called Extended Events. The new auditing infrastructure is built on top of Extended Events, so it should be scalable, reliable, and have low overhead. SQL Server 2008 will provide tools for audit-log consolidation across the enterprise and fully supports the use of SQL Server Analysis Services (SSAS) and SQL Server Reporting Services (SSRS) on consolidated audit data.
Build Versus Buy
The ability to log database logins, login failures, and usage is a common requirement in today’s compliance-driven environment. Whether you have to log everything or almost nothing, SQL Server 2005's out-of-the-box audit-logging capabilities might be all you need to fulfill those requirements. If you need more extensive auditing capabilities, you might want to consider third-party solutions that provide such features. For more information about what third-party auditing products offer, see the sidebar “Third-Party Audit-Logging Options.” I recommend that anyone serious about database auditing do a build-versus-buy evaluation taking into account the costs of each approach. On the buy side, you should consider the costs of acquisition, support, maintenance, implementation consulting, and customization. On the build side, consider the costs of development and maintenance. One comparison between the two approaches is cross platform support. A third-party solution can probably be applied to multiple platforms, whereas a solution built on SQL Trace will be SQL Server specific.
