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%\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.
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 <servername> -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 <servername>
—U <username>
—P <password>
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.