• Article

How to: Write Server Audit Events to the Security Log

In a high security environment, the Windows Security log is the appropriate location to write events that record object access. Other audit locations are supported but are more subject to tampering.

There are two key requirements for writing SQL Server server audits to the Windows Security log:

  • The audit object access setting must be configured to capture the events. The best way to do this varies depending on your operating system.

    • In Windows Vista and Windows Server 2008, use the audit policy tool (auditpol.exe). The audit policy program exposes a variety of sub-policies settings in the audit object access category. To allow SQL Server to audit object access, configure the application generated setting.

    • For earlier versions of Windows, the audit policy tool is not available. Use the security policy snap-in (secpol.msc) instead. When available, the audit policy is preferred because you can configure more granular settings.

  • The account that the SQL Server service is running under must have the generate security audits permission to write to the Windows Security log. By default, the LOCAL SERVICE and the NETWORK SERVICE accounts have this permission. This step is not required if SQL Server is running under one of those accounts.

The Windows audit policy can affect SQL Server auditing if it is configured to write to the Windows Security log, with the potential of losing events if the audit policy is incorrectly configured. Typically, the Windows Security log is set to overwrite the older events. This preserves the most recent events. However, if the Windows Security log is not set to overwrite older events, then if the Security log is full, the system will issue Windows event 1104 (Log is full). At that point:

  • No further security events will be recorded

  • SQL Server will not be able to detect that the system is not able to record the events in the Security log, resulting in the potential loss of audit events

  • After the box administrator fixes the Security log, the logging behavior will return to normal.

Administrators of the SQL Server computer should understand that local settings for the Security log can be overwritten by a domain policy. In this case, the domain policy might overwrite the subcategory setting (auditpol /get /subcategory:"application generated"). This can affect SQL Server ability to log events without having any way to detect that the events that SQL Server is trying to audit are not going to be recorded.

You must be a Windows administrator to configure these settings.

To configure the audit object access setting in Windows using auditpol

  1. If the operating system is Windows Vista or Windows Server 2008, open a command prompt with administrative permissions.

    1. On the Start menu, point to All Programs, point to Accessories, right-click Command Prompt, and then click Run as administrator.

    2. If the User Account Control dialog box opens, click Continue.

  2. Execute the following statement to enable auditing from SQL Server.

    auditpol /set /subcategory:"application generated" /success:enable /failure:enable

  3. Close the command prompt window.

    This setting takes effect immediately.

To configure the audit object access setting in Windows using secpol

  1. If the operating system is earlier than Windows Vista or Windows Server 2008, on the Start menu, click Run.

  2. Type secpol.msc and then click OK. If the User Access Control dialog box appears, click Continue.

  3. In the Local Security Policy tool, expand Security Settings, expand Local Policies, and then click Audit Policy.

  4. In the results pane, double-click Audit object access.

  5. On the Local Security Setting tab, in the Audit these attempts area, select both Success and Failure.

  6. Click OK.

  7. Close the Security Policy tool.

    This setting takes effect immediately.

To grant the generate security audits permission to an account using secpol

  1. For any Windows operating system, on the Start menu, click Run.

  2. Type secpol.msc and then click OK. If the User Access Control dialog box appears, click Continue.

  3. In the Local Security Policy tool, expand Security Settings, expand Local Policies, and then click User Rights Assignment.

  4. In the results pane, double-click Generate security audits.

  5. On the Local Security Setting tab, click Add User or Group.

  6. In the Select Users, Computers, or Groups dialog box, either type the name of the user account, such as domain1\user1 and then click OK, or click Advanced and search for the account.

  7. Click OK.

  8. Close the Security Policy tool.

    This setting takes effect when SQL Server is restarted.