The article covers the steps to create an SQL server audit object, audit specification, and SQL server database audit specification.
The first step to set up SQL auditing is to create a Server Audit object which defines the audit destination, i.e. Windows Event Log. Then add a Server Audit Specification object to start auditing server events and Database Audit Specification object to audit SQL statements.
Creating an SQL Server Audit object
1. Open SQL Server Management Studio and connect to the relevant database instance.
2. Expand Security, right-click on Audits, and select New Audit.
3. Enter a name for the audit object in the Create Audit dialog.
4. In the Audit destination drop-down list, Choose Security log/Windows Security log.
5. Click OK to create the Server Audit object.
6. A red arrow will be displayed next to the new object’s name, which indicates a disabled object. Right-click on the audit object and select Enable audit.
From Step 4. There are three key requirements for writing SQL Server server audits to the Windows Security log:
- The Audit object access security policy and Audit Application Generated advanced audit policy must be enabled. These settings are usually controlled by the domain group policy, which overwrites the local policy settings.
- The SQL Server service account must be able to write to the Security log. The LOCAL SERVICE and NETWORK SERVICE accounts have permission by default.
- The SQL Server service account must be able to create an entry under the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Security registry key. To grant the account permission:
- Open the Registry Editor (regedit.exe).
- Navigate to HKLM\SYSTEM\CurrentControlSet\Services\EventLog\Security.
- Right-click on the key name and select Permissions…
- Add the SQL Server service user and assign it Full Control.
- Disable, then re-enable the Server Audit object so SQL Server attempt to recreate the MSSQLSERVER$AUDIT sub-key.
- Once the key is created, you may remove the Full Control access permission since it is no longer required.
Important: Incorrectly editing the registry can severely damage your system. Before making changes to the registry, we recommend that you back up any valued data on the computer.
Creating an SQL Server Audit Specification
1. Open SQL Server Management Studio and connect to the relevant database instance.
2. Expand Security, right-click on Server Audit Specifications, and select New Audit.
3. Choose the Server Audit object created earlier and select the Actions you want to audit.
4. Click OK to create the Server Audit Specification object.
5. A red arrow will be displayed next to the new object’s name, which indicates the object is disabled. Right-click on the audit object and select Enable audit.
Creating an SQL Server Database Audit Specification
1. Open SQL Server Management Studio and connect to the relevant database instance.
2. Expand Databases > [database_name] > Security.
3. Right-click on Database Audit Specifications and select New Audit.
4. Choose the Server Audit object created earlier and select the actions you want to audit.
5. Click OK to create the Database Audit Specification object.
Once you enable SQL Server audit, SQL Server writes audit logs (which is Audit Event ID: 33205) to the Security log/Windows Security log, then NXLog will collect the logs from Windows Event Log.
If the Security log doesn’t contain any SQL Server auditing events, check the Application log for Event ID: 33204, which indicates a failure to write to the Security log.
Comments
0 comments
Please sign in to leave a comment.