SQL Server 2000 Auditing

Tracking database activity

Microsoft has made each version of SQL Server more secure and easier to administer than the previous one. In August 2000, an independent testing facility found that SQL Server 2000 meets the US Department of Defense's C2 security requirements. (You can obtain more information about the C2 evaluation from Microsoft's Web site at http://www.microsoft.com/technet/treeview/default.asp?url=/technet/security/prodtech/dbsql/sqlc2.asp.) If your company needs to meet C2 security standards or you need to audit usage of your SQL Server 2000 installation for other reasons, you'll find this discussion of auditing helpful.

Enabling Auditing
You're probably familiar with the Windows event logs—particularly the Security log—that reside in the %systemroot%system32config folder. To view the logs, you run Event Viewer (eventvwr.exe).

During their installation process, many applications—including SQL Server 2000—register with the event-log subsystem. If you run instances of SQL Server 2000 in your network, you're familiar with the variety of log entries that describe SQL Server's startup, shutdown, and day-to-day operations (e.g., backups, server-side traces). What you might not be familiar with is SQL Server 2000's ability (not enabled by default) to audit login activity, including failed login attempts, to the Windows Application log. To configure this auditing, launch Enterprise Manager, select a database server, right-click Properties, go to the Security tab, and set your desired level of auditing, as Figure 1 shows. (If you intend to enable C2 auditing, I recommend that you don't audit to the Application log; otherwise, SQL Server will write audit information about user-login activity to two places simultaneously and unnecessarily degrade system performance.) After you change audit settings, you need to restart the database.

Even if you enable auditing to the Application log, you won't find details in the logs about user activity such as which tables users access, which queries users run, and which stored procedures users invoke. To enable SQL Server auditing to such a level, you must turn to the SQL Server software.

Although SQL Server 2000 can audit user actions, the feature isn't enabled by default; your DBA must activate it. DBAs have unrestricted access to databases on the database server and are responsible for database management. In many environments, the systems administrator or network administrator is also the DBA. By default, the Administrators group on the SQL Server 2000 machine has permission to log in to the database. The Administrators group is granted the System Administrators server role, which lets group members perform necessary database actions. Most security-conscious administrators remove the local Administrators group from database logins, especially when the database server system is a member of a domain. When a system joins a domain, the Domain Admins group becomes a member of the local Administrators group; therefore, members of the Domain Admins group are also DBAs. Before you remove the Administrators group from database logins, ensure that you've added another user account or group that has the System Administrators role. In SQL Server installations that use mixed-mode authentication, you should use the SQL Server account sa as the DBA account. With mixed-mode authentication, you can create SQL Server user accounts that are separate from Windows 2000 user accounts. However, if your site has a need for strict security, I don't recommend mixed-mode authentication.

The easiest way to enable auditing in SQL Server 2000 is to use the osql.exe command-line tool. You can even use this tool to enable auditing on remote SQL Server systems. If you used an account with DBA privileges on the target database server to log on to Windows, type

osql -S  -E

where —S tells osql.exe to connect to a remote server and servername is the name of the database server or instance. The —E option tells osql.exe to use the credentials with which you logged on to Windows to establish a trusted database connection. If you didn't use an account with DBA privileges, you need to type

osql —S —U  —P 

where —U tells osql.exe that the following argument is the username (username should be either sa or the name of an account with DBA privileges) and —P signals that the next argument is the password for the specified account. If you omit the —P option, the system will prompt you to type in your password.

After you connect to the database and receive a prompt, you can use the system stored procedure sp_configure to set the c2 audit mode option to 1. Doing so updates an internal SQL Server variable that controls the auditing functionality. If no errors occur, the system will prompt you to run the RECONFIGURE statement, as Figure 2, page 10, shows, to effect the change. If you can't change the audit mode—that is, if you get an error message and the system displays a list of settings—you first need to use sp_configure to set Show advanced options to 1. After you set the audit mode, you'll need to stop and restart the database server for logging to commence.

SQL Server Audit Logs
After you enable C2 auditing for the default database or for an instance, the database server will log all activity to the data directory that you specified during the installation process. (SQL Server doesn't let you log auditable events to an alternative location.) This directory holds the databases that SQL Server initially created. This directory is also the default location for all new databases and their transaction log files. You need to be aware of some implications of this configuration.

First, on a system that has limited disk space, you might find that your databases can't grow because audit log files are consuming all the free space. Second, on a busy system, performance might suffer because both the databases and the audit logs use the same disk. In general, you should store databases and their transaction logs on separate, dedicated disk devices so that you can avoid these two problems.

SQL Server writes all auditable activity to a file with the format audittrace_YYYYMMDDHHMMSS.trc, where YYYYMMDDHHMMSS is the log's creation time by year, month, day, hour, minute, and second. When a log reaches a maximum size of 200MB, SQL Server automatically creates a new log and begins to record to the new log instead. This feature lets you safely move old log files out of the data folder or delete them.

If SQL Server can't write to a log file—for example, if the disk contains no more free space—it will halt all execution. SQL Server won't restart until it can resume logging. If you need to force SQL Server to run even though logging isn't possible, you can modify the arguments to the service settings in the Service Control Manager (SCM), which is available through the Microsoft Management Console (MMC) Computer Management snap-in. Alternatively, you can use the -f flag to start a minimal SQL Server configuration from the command line. You might consider using the -m flag with the -f flag. Doing so starts the database in single-user mode, preventing clients from connecting to the database and performing transactions while auditing is disabled.

Understanding the Audit Log
When C2-level auditing is enabled, the system uses a default profile of auditable events to determine what to log. These auditable events, which are necessary in any implementation to meet C2 certification requirements, include the execution of stored procedures, the creation or deletion of objects such as tables, and user login and logout activity.

You can use SQL Server Profiler to view the log files that SQL Server creates. To open a log file, click SQL Profiler in the Microsoft SQL Server program group. Select File, Open, and click Trace File. Use the Open dialog box to navigate to the SQL Server data folder (the folder you specified when you installed SQL Server), and open the log file that you want to view. You can't open the log file to which SQL Server is logging activity. If you need to access data in the current audit log, you'll need to wait until it fills up and the system creates a new log file, or simply stop and restart SQL Server.

Figure 3 shows a typical log file that user activity generated. The fragment shows the results of a DBA adding a user to a fixed role (db_owner), user login activity, the creation of a table, and the querying of other tables. The system captures much more information for each audited event than the figure shows. Of particular interest are the EventClass column, which identifies the recorded auditable event; the TextData column, which contains the command (typically a stored procedure or SQL statement) that was executed in the database; the NTUserName and NTDomainName columns, which identify the user who ran the command; the ObjectName and DatabaseName columns, which identify the table, view, or stored procedure that was manipulated or invoked; and the StartTime column, which records when the command was issued. Of secondary interest might be the RoleName, TargetUserName, TargetLoginName, and DBUserName columns. Not all columns are populated for each event. SQL Server Books Online (BOL) discusses which columns are populated for each type of event and describes how you should interpret the values found in each column.

Unfortunately, security administrators will need to dig through the logs to determine what's going on in their databases. For example, when a stored procedure is executed, several entries in the log will appear for each statement that was executed. Navigating the entries can take time and requires knowledge about the called stored procedures to determine exactly what's happening and how the database was affected. The audit log records the arguments to any given stored procedure; to retrieve these arguments, you can select the first entry that records the invocation of the store procedure. The arguments appear in Profiler's bottom pane.

Using Server-Side Traces
One disadvantage of using SQL Server 2000's C2 auditing functionality is its all-or-nothing approach. You can't instruct SQL Server which event categories to audit, and you can't instruct SQL Server to record access to particular objects (e.g., tables)—as you can in Win2K.

To achieve a fine-grained approach to these types of auditing, you'll need to turn to Server-Side Traces. SQL Server has long provided the ability to trace activity for debugging and performance-monitoring purposes. You can also use Server-Side Traces to monitor security-related activity. Unlike C2 auditing, traces aren't persistent. If you restart SQL Server, the trace is lost and you must recreate it. Another difference between C2 auditing and traces is that you can start and stop a trace without restarting the database service. Traces are extremely configurable; you can choose exactly which events to audit and what information about each event to record.

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 —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'', '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.