11/7/2019 6:02 AM

9/3/2025 10:44 PM

Before configuring the Microsoft SQL Audit Events connector to process SQL events into SEM, you must establish a SQL audit within a SQL server, and then send those events to the Windows Security or Windows Application log. 
 
Once the logs are in the Windows Application (or Security) log, install the SEM agent on the server, and then configure the SQL Audit Event connector. By default, the connector looks for the logs in the Application log, but can be adjusted to look in the Security log, if needed.
 

Security Event Manager

Microsoft SQL Server configuration

Configure the audit object access setting in Windows using auditpol

1. Open a command prompt with administrative permissions. 
   1. To run the command prompt as an administrator, press Windows+R to open the Run box, type cmd, and then press Ctrl+Shift+Enter.
   2. If the User Account Control window opens, click Continue.
2. Execute the following statement to enable auditing from the SQL Server: 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
Set the permissions to the service account that is running the SQL Server so it can generate security audits.

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 window 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 window, either type the name of the user account, such as domain1user1, and then click OK, or click Advanced and search for the account.
7. Click OK.
8. Close the Security Policy tool.
9. To enable this setting, restart the SQL server.

Create the Audit Object on the SQL server

1. Go to the SQL Server instance you want to audit, open Security, and then locate the Audits folder.
2. Right-click the Audits folder and select New Audit. The Create Audit window appears.
3. Use the name that is automatically generated for you as the audit object name, or to assign your own name. 
4. Provide a Queue Delay number. This refers to the amount of time after an audit event has occurred before it is forced to be processed and written to the log. The default value is 1000 milliseconds, or one second. if you have a busy server, consider increasing this value.
5. In the window next to Audit, a drop-down box shows File selected by default. Select Security Log (or Application Log depending on which one you want to use).
6. Click OK.
7. Right-click the audit object you created and select Enable Audit.

Create the Server Audit Specification on the SQL server
After creating the audit, create the matching audit specification. To conduct an instance-wide audit, create a server audit specification.

1. Under Security, right-click Server Audit Specifications, and then select New Server Audit Specification.
2. Give the specification a name, and then select the audit object to associate it with the specification.
3. Select specific audit action types you want the SQL server to log, and then click OK.
4. Right-click the server audit specification, and then select Enable Server Audit Specification.

Note: In the Security (or Application) log, audit logs are usually listed as event code 33205, so you can filter Security logs to only show those events.

Install the SEM agent on the server
Learn how to install the SEM agent here.

SEM HTML5 console configuration

Configure the MS SQL Audit Events connector in SEM

1. In the SEM Events Console, click the Nodes tab.
2. Under Refine Results, expand the Type group, and then select the Agent check box.
3. Select an agent, and then click Manage node connectors.
4. In the search box, enter MS SQL.
5. Under Available connectors, select MS SQL Audit Events, and then click Add Connector.

   
    

6. Complete the connector configuration form.
   1. Enter a user-friendly name, or keep the default.
   2. From the Windows event target drop-down list, select Application or Security based on the log type you selected to include the SQL audit events.
   3. Output: Normalized, Raw + Normalized, Raw. You only need to configure these values if SEM is configured to save raw (unnormalized) log messages. 
7. Click Add.
8. Under Configured connectors, select the MS SQL Audit Events connector, and then click Start.