Step 9. Auditing and Logging

Auditing does not prevent system attacks, although it is a vital aid in identifying intruders, attacks in progress, and to diagnose attack footprints. It is important to enable all auditing mechanisms at your disposal, including Windows operating system level auditing and SQL Server login auditing. SQL Server also supports C2 level extended auditing. This may be required in specific application scenarios, where auditing requirements are stringent.

In this step, you:

• Log all failed Windows login attempts .

• Log all failed actions across the file system .

• Enable SQL Server login auditing .

Log All Failed Windows Logon Attempts

You must log failed Windows logon attempts to be able to detect and trace malicious behavior.

 Task   To audit failed logon attempts

1. Start the Local Security Policy tool.

2. Expand Local Policies and then select Audit Policy .

3. Double-click Audit account logon events .

4. Click Failure , and then click OK .

Windows logon failures are recorded as events in the Windows security event log. The following event IDs are suspicious:

• 531 . This means an attempt was made to log on using a disabled account.

• 529 . This means an attempt was made to log on using an unknown user account or using a valid user account but with an invalid password. An unexpected increase in the number of these audit events might indicate an attempt to guess passwords.

Log All Failed Actions Across the File System

Use NTFS auditing on the file system to detect potentially malicious attempts. This is a two-step process:

 Task   To enable logging

1. Start the Local Security Policy tool.

2. Expand Local Policies , and then select Audit Policy .

3. Double click Audit object access .

4. Click Failure , and then click OK .

 Task   To audit failed actions across the file system

1. Start Windows Explorer and navigate to the root of the file system.

2. Right-click the root of the file system, and then click Properties .

3. Click the Security tab.

4. Click Advanced , and then click the Auditing tab.

5. Click Add , and then enter Everyone into the object name to select field.

6. Click OK , and then select the Full Control check box in the Failed column to audit all failed events.

   By default, this applies to the current folder and all subfolders and files.

7. Click OK three times to close all open dialog boxes.

Failed audit events are logged to the Windows security event log.

Enable SQL Server Login Auditing

By default, SQL Server login auditing is not enabled. Minimally, you should audit failed logins. Auditing failed login attempts is a useful way of detecting an attacker who is trying to crack account passwords. For more information, about how to enable SQL Server auditing, see "Step 10: SQL Server Security."

Additional Considerations

The following are additional measures to consider when auditing and logging:

• Consider shutting down the system if unable to log security audits . This policy option is set in the Security Options of the Local Security Settings management console. Consider this setting for highly secure servers.

• Consider C2 level auditing . SQL Server offers an auditing capability that complies with the U.S. Government C2 certification. C2 level auditing provides substantially more audit information at the expense of increased disk storage requirements.

  For more information about the configuration of a C2-compliant system, see the TechNet article "SQL Server 2000 C2 Administrator's and User's Security Guide" at http://www.microsoft.com/technet/prodtechnol/sql/maintain/security/sqlc2.asp?frame=true#d .