So far, in our series, we have been focusing on tasks geared towards configuration changes, which increased the default security level of the SQL Server 2000 installations. However, no matter how much time and attention you dedicate to this process, it is likely that sooner or later you will have to face an attempt of unauthorized intentional or accidental data or object definition change, exploit of security vulnerability, or even a malicious modification by an unethical system administrator. You need to be prepared for such possibilities and ensure you can track their source and prevent them from happening in the future. This is the purpose of auditing, which is the topic of this article.
Auditing can be performed using several methods, with varying level of details, configuration settings and monitoring tools (and some degree of overlap between them):
-
built-in default Windows events auditing – Windows Event logs reside in the \%systemroot%\system32\config folder in the form of several *.evt files (at the minimum AppEvent.Evt, SecEvent.Evt and SysEvent.Evt corresponding to Application, Security and System Event Logs – the number of logs can be larger depending on the role of the server and additional software installed on it). The easiest way to view the logs is by running Event Viewer MMC snap-in (present in the Administrative Tools menu). Size and retention behavior of the logs is controlled from the log Properties dialog box. You can set from here the maximum size of each and specify what happens when its maximum size is reached (the three available options are – overwrite events as needed, overwrite events older than specified number of days, and do not overwrite events).
SQL Server related events are recorded in the Application log. This includes, by default, major activities that potentially affect the operating system, such as SQL Server startup, shutdown, backups, restores, change of configuration options.
-
Login auditing – four option buttons – None, Success, Failure and All – under the “Audit level” heading on the Security tab of the SQL Server Properties dialog box in SQL Server Enterprise Manager control level of SQL Server login audits. These choices correspond to values of 0, 1, 2 and 3 in the AuditLevel entry in the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer registry key of DWORD data type. This value determines whether login attempts with specified outcome (or any at all) will be recorded in the Windows Application Event Log and SQL Server error log.
-
default SQL Server auditing – SQL Server stores up to seven subsequently created logs (although this number is configurable using HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\NumErrorLogs registry entry of DWORD data type), named ErrorLog, ErrorLog.1, ErrorLog.2, etc. in the folder C:\Program Files\Microsoft SQL Server\MSSQL\LOG. You can review their content from SQL Server Logs node under Management folder for the target SQL Server in the SQL Server Enterprise Manager. Its entries consist of the timestamp, source (this is either server or one of user processes, indicated by its process id), and message. Most of them correspond to the entries you can find in the Windows Application Event log.
-
C2 auditing – the US Department of Defense established a set of ratings applicable to security levels of computer systems, based on their capabilities in regard to auditing and discretionary access control. SQL Server 2000 was determined to be compliant with a C2 rating in August 2000 by the National Computer Security Center (more information about the C2 evaluation process is available on the Microsoft Web site at http://www.microsoft.com/technet/treeview/default.asp?url=/technet/security/prodtech/dbsql/sqlc2.asp). This compliance is relevant for companies, which need to secure their computing operations according to the US Government requirements (which applies to most government agencies and contractors).
C2 auditing records information that goes beyond server-level events, such as shutdown or restart, successful and failed login attempts, extending it to successful and failed use of permissions when accessing individual database objects and executing all Data Definition, Data Access Control, and Data Manipulation Language statements. The audit information contains the timestamp, identifier of the account that triggered the event, target server name, event type, its outcome (success or failure), name of the user’s application, and Server process id of the user’s connection.
Audit logs are stored in the Program Files\Microsoft SQL Server\Data\ folder as AuditTrace_yyyymmddhhmmss.trc, where the second part of the name indicates date and time when the log file was created. Size of a log is limited to 200MB, but new ones are generated automatically whenever the old one is full as long as there is available disk space. Otherwise, shutdown of SQL Server is initiated. Ensure that you have sufficient space on your hard drive, as the volume of recorded information is significant. In emergency situations, where no space can be immediately freed for new log files, you can restart SQL Server with the -f flag, which will disregard auditing settings.
The content of the audit files can be viewed using SQL Server Profiler (you can also import them into a new or an existing table). Alternatively, you can use for this purpose a built-in function fn_trace_gettable, which displays the content of a trace file in a table format (the following sample T-SQL command can be executed from the SQL Query Analyzer):
SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL
Server\MSSQL\Data\audittrace_20040822191554.trc', default)
GO
where the first parameter defines the location and name of the first log file and the second (default option) specifies that all rollover files should be read as well.
To enable C2 auditing, use sp_configure stored procedure with the ‘c2 audit mode’ parameter. Assigning it a value of 1 enables auditing, 0 reverts it back to the default. Since this is an advanced option, you will need to turn on the “show advanced options” setting. In addition, changing this setting requires a restart of the SQL Server. Permissions to perform these steps are limited to members of sysadmin fixed server role. The following sequence of T-SQL commands can be executed from the SQL Query Analyzer in order to enable C2 auditing (you will need to restart the SQL Server afterwards):
USE master EXEC sp_configure 'show advanced option', '1' RECONFIGURE EXEC sp_configure 'c2 audit mode', 1 RECONFIGURE
Note that if you have enabled C2 auditing, you might want to disable login auditing, configurable via the Security tab of the SQL Server Properties dialog box in SQL Server Enterprise Manager (described previously), otherwise you will record the same type of event twice, unnecessarily degrading server performance.
Server\MSSQL\Data\audittrace_20040822191554.trc', default)
GO
There are also other, less common mechanisms which allow either custom audits or enhance existing standard procedures described above. For example, you can monitor changes to SQL Server via SQL Server Alerts, which monitor particular events or performance conditions and trigger notifications or corrective actions in case these take place. Similarly, you can define triggers on tables and views that fire if specific modifications take place (although in this case, you are limited to INSERT, DELETE and UPDATE T-SQL statements on target objects). There is also a number of auditing tools, both in the form of freeware/shareware and commercial software, which allow you to determine the degree of security of your SQL Server installation. For example, free password auditing utilities (which, in essence, attempt brute force attacks on SQL passwords to verify their complexity level) can be obtained from www.nextgenss.com/software/ngssqlcrack.html (NGSSQLCrack) or www.sqlsecurity.com/uploads/sqldict.zip (SQLdict).
Keep in mind that auditing serves its purpose only if the logs are regularly reviewed (although the level of details is dependent on security requirements you need to satisfy).
