write-sql-server-audit-events-to-the-security-log.md

title	description	author	ms.author	ms.reviewer	ms.date	ms.service	ms.subservice	ms.topic	helpviewer_keywords
Write SQL Server Audit events to the Security log	Learn how to write SQL Server audit events to the Windows Security log. Find out about the limitations and restrictions to using that log.	sravanisaluru	srsaluru	randolphwest	03/20/2023	sql	security	conceptual	logs [SQL Server], Security Log server audit [SQL Server] audits [SQL Server], writing to Security Log security logs [SQL Server]

Write SQL Server Audit events to the Security log

[!INCLUDE sql-windows-only]

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 three key requirements for writing [!INCLUDEssNoVersion] server audits to the Windows Security log:

• The audit object access setting must be configured to capture the events. The audit policy tool (auditpol.exe) exposes various subpolicies settings in the audit object access category. To allow [!INCLUDEssNoVersion] to audit object access, configure the application generated setting.

• The account that the [!INCLUDEssNoVersion] 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 isn't required if [!INCLUDEssNoVersion] is running under one of those accounts.

• Provide full permission for the [!INCLUDEssNoVersion] service account to the registry hive HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Security.

  [!IMPORTANT]
  [!INCLUDEssnoteregistry-md]

Limitations and restrictions

• 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"). [!INCLUDEssNoVersion] doesn't have any way to detect that the events it is trying to audit aren't going to be recorded.

• Events can be lost if the audit policy is incorrectly configured. The Windows audit policy can affect [!INCLUDEssNoVersion] auditing if it is configured to write to the Windows Security log. Typically, the Windows Security log is set to overwrite the older events. This preserves the most recent events. However, if the Windows Security log isn't set to overwrite older events, then if the Security log is full, the system issues Windows event 1104 (Log is full). At that point:

  • No further security events are recorded

  • [!INCLUDEssNoVersion] can't detect that the system isn't 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.

• [!INCLUDE ssnoversion-md] audit records contain significantly more data than regular Windows Event log entries. In addition, depending on the configuration of the audit specification, [!INCLUDE ssnoversion-md] may generate many thousands of audit records in a short period of time (thousands per second). Under periods of high load, this may result in adverse conditions if the audit records are written to either the Application log or the Security log.

  These adverse conditions may include:

  • Rapid cycling of the event log (events are overwritten very quickly as the log file reaches its size limit)

  • Other applications or services that read from the Windows event log may be negatively affected

  • The targeted event log may be unusable by administrators due to events being overwritten so quickly

  Steps that administrators may take to mitigate these adverse conditions:

  1. Increase the size of the target log (4 GB isn't unreasonable when the audit specification is very detailed).

  2. Reduce the number of events being audited.

  3. Output the audit records to a file instead of the Event Logs.

Permissions

You must be a Windows administrator to configure these settings.

Configure the audit object access setting in Windows using auditpol

1. Open a command prompt with administrative permissions.

   1. From the Start menu, navigate to Command Prompt, and then select Run as administrator.

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

2. Execute the following statement to enable auditing from [!INCLUDEssNoVersion].

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

3. Close the command prompt window.

Grant the generate security audits permission to an account using secpol

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

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

3. In the Local Security Policy tool, navigate to Security Settings > Local Policies > User Rights Assignment.

4. In the results pane, open Generate security audits.

5. On the Local Security Setting tab, select 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 select OK, or select Advanced and search for the account.

7. Select OK.

8. Close the Security Policy tool.

9. Restart [!INCLUDEssNoVersion] to enable this setting.

Configure the audit object access setting in Windows using secpol

1. If the operating system is earlier than [!INCLUDEwinvista] or Windows Server 2008, on the Start menu, select Run.

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

3. In the Local Security Policy tool, navigate to Security Settings > Local Policies > Audit Policy.

4. In the results pane, open Audit object access.

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

6. Select OK.

7. Close the Security Policy tool.

See also

• SQL Server Audit (Database Engine)