March 05, 2001, 7:02 PM — In the past, the question of how to perform auditing under SQL Server has had a simple answer: Do it yourself. But now, SQL Server 2000 has eased the burden of auditing by introducing an extensive and configurable auditing system built on the SQL Trace tool.
The most important auditing feature for average users is the ability to track modifications made to data. Unfortunately, while SQL Server 2000 can indicate which user might have updated a table, it cannot capture the actual content of the update. Still, SQL Server 2000 does conform with the C2-level auditing required by certain government users.
Let's take a quick look at the evolution of SQL Server auditing, then dive into its capabilities.
SQL Server auditing prior to SQL Server 2000
Earlier releases of SQL Server have only one built-in auditing feature: the ability to capture server logins. Under SQL Server 7.0, for example, you can specify that logins -- both successful and failed -- be written to the Windows 2000 (or Windows NT) application log, which you view using the Event Viewer. To start this process, you specify an audit level via the Audit Level option in the Enterprise Manager (on the Server Properties Security tab). The SQL Server 7.0 documentation erroneously states that this audit facility will also write login attempts to SQL Server's own log. It does not.
To audit SQL Server 7.0 activity on a deeper level, you could use the Profiler to capture both successful logins and SQL statements issued by users. But beware: overhead is high, since SQL Server 7.0's Profiler was built to analyze performance and tuning, not to conduct auditing. Also, the Profiler captures only successful commands and logins; if a user issues a SQL command that is denied, for example, the SQL Server 7.0 Profiler will not report it.
SQL Trace's new role and new capabilities
SQL Trace is the heart of SQL Server 2000's auditing capabilities. In fact, Microsoft resurrected SQL Trace, which had been downplayed in SQL Server 7.0, specifically to facilitate auditing.
SQL Trace, the server-side component of SQL Server 2000's tracing system, works with the Profiler client-side viewing utility. (The functionality and naming of SQL Trace and Profiler have changed with each SQL Server release since version 6.5. See the sidebar for details.) Traces, which are activated by issuing a SQL Trace stored procedure, incur much lower overhead than did SQL Server 7.0's Profiler.
The drawback of auditing is the size of the log files that audit procedures generate. SQL Trace eases the management of those files with several features. For example, SQL Trace supports file rollover. Simply specify audit files' maximum size (in megabytes), and SQL Trace will automatically start a new file when the previously generated file reaches that limit. You can also specify the amount of time a trace should run.
When you create a trace, you can specify that a SQL Server instance be initiated to stop log generation under certain conditions -- for example, if audit logs have filled up a drive, or the SQL Server account requesting auditing services has reached its disk-use quota. After log generation has stopped, you must then either free up disk space or restart SQL Server with auditing turned off.
Reported events
To start auditing, first ensure that your login is assigned SQL Server sysadmin rights. Depending on what you want audited, you can either customize SQL Trace or enable C2 auditing. (SQL Server 2000's C2-compliant feature set supports the rigorous security that some government agencies require.)
Using the SQL Profiler client, you can set up a custom system that audits virtually every event that might be applied against your server's data and security system. SQL Trace can report on an extensive array of events. Only a fraction of those are actually worth auditing, but even that number is large. For example, SQL Trace can capture important events related to server security - for example, when users log in and out of the server, add and drop SQL Server logins, change a SQL Server login password, and add or remove a login from a server role.
SQL Server 2000 auditing extends to database security. SQL Trace can report:
- The addition and removal of database users
- The addition and dropping of database roles
- The addition and removal of database role members
- The granting, revocation, and denial of permissions for both objects and statements
(Revoking a permission simply removes a former grant; a user can still maintain permissions derived from another source. A denial overrides all other derived permissions, completely preventing a user from using a specified statement or object.)
