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,” InstantDoc ID 98814. 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.
End of Article
Prev. page
1
[2]
next page -->
