SQL Server 2000 Auditing

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%\system32\config 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. . . .