Auditing SQL Server with EventSentry

Requires: SQL Server 2016 (13.x) SP1 or newer (all editions)

Starting with SQL Server 2016 (13.x) SP1 or newer, auditing can be enabled on all editions (not just enterprise) of SQL Server. EventSentry can log and alert on sensitive events like user creation, database deletion, permission changes and more.

Auditing can be enabled at the server and database level.

This article will outline how to enable auditing and how to monitoring the alerts generated by SQL Server.

1. Enabling Auditing

A. Creating the Audit Object on SQL Server

The first step wen enabling auditing is to create a new audit object. In Microsoft SQL Server Management Studio (SSMS), expand the server instance and security. Then, right-click on Audits and select “New Audit”.

Fig.1: New Audit Creation

B. Configuring an Audit Object:

In the Create Audit dialog you can select whether audit information is logged to the event log (Application or Security) or a file. While EventSentry can support all three options, in this article we select "Application Log". Click OK to confirm the new audit object.

Fig.2: Create Audit Window

Security Note: There are some scenarios where logging audit data to the security event log may be preferable, for example on secure servers where Non-Admin users should not be able to view SQL Audit logs. Depending on how SQL Server was installed, you will need to grant access to Security Log to the user under which the SQL Server instance is running. See this article for more information.

C. Enabling the Audit Object:

After the Audit Object (or later an audit specification) is created, it needs to be enabled by right-clicking the Audit Object and clicking "Enable Audit"

Fig.3: Enabling Audit

2. Creating a Server-Level Audit Specification

After the audit object is created & enabled, a Server-Level and/or Database-level Audit specification can be created and associated with our audit object. In SSMS, right-click the Server Audit Specification container under Security and select "New Server Audit Specification".

Fig.4: Creating Audit Specification (Server-Level)

Now, specify a name for the audit specification and select the previously created audit object in the in the "Audit" field. In the "Audit Action Type" column select the "SERVER_ROLE_MEMBER_CHANGE_GROUP" which is raised whenever a login is added or removed from a fixed server role. Depending on the type of Audit Action, fields like Object Class, Object Schema, etc may be grayed out. Multiple audit actions can be added to a single audit specification object. Click OK.

Note 1: For more information about audit actions types follow this link.

Note 2: All audit specifications end with GROUP, whereas the prefix usually defines the scope, such as SERVER or DATABASE. For example, "SERVER_PRINCIPAL_CHANGE_GROUP" audits user changes on the server (regardless of the database), and "DATABASE_PRINCIPAL_CHANGE_GROUP" audits users changes on the database level.

You can use "FAILED_LOGIN_GROUP" and "SUCCESS_LOGIN_GROUP" to log successful and failed logins.

Fig.5: Create Audit Specification Window.

Just like with the audit object earlier, right-click the Audit Specification that was just created and click enable. Please note that you will need to disable (and re-enable) an audit specification whenever you want to make changes to it.

Fig.6: Enabling Audit Specification.

Note: To verify that auditing is working as expected (using the example from above), you can run the following SQL statement:
alter server role sysadmin add member [MYDOMAIN\SomeUser]

Fig.7: Running SQL query to check the Audit Specification.

If auditing was setup correctly then an event log alert should be created under the Application event log. Note that all SQL Audit events are generated with Event ID 33205.

Fig.8: Application Even Log generated

3. Configuring EventSentry to send an alert email

Now that we have auditing enabled and configured, we can configure EventSentry to alert us via email when activity matches one of our audit specifications.

The quickest way to create a filter rule for an event is by viewing the event in the event viewer that is built into the EventSentry Management Console. If SQL Server is installed on the same machine where EventSentry was installed, then you can simply expand the "Event Log Viewer (local)" node and view the application event log. If SQL Server is running on a remote host, then right-click the event log viewer and select a host from the respective group menu to connect and view its application event. Locate & view the event and click the "Forward this event to an action ("Include") button (indicated by a green arrow).

Fig.9: Creating EventSentry Event Log Action

In the resulting dialog select the package in which the filter should be created, along with a name for the filter. Here we name the filter "SQL Audit" and place it into the existing SQL Servers package.

Fig.10: Naming filter / selecting group

Note: If you create a new package for the filter, ensure that the package is properly assigned either statically or dynamically.

The "SQL Servers" package is configured to forward events to the default email, which can be changed if necessary. To ensure that SQL audit events that are written to the application event log are also consolidated to the EventSentry database, navigate to "Packages -> Event Log -> Database Consolidation -> Consolidate Non-Security Events" and make sure that all check boxes under "Event Severity" are checked.

Fig.11: Filter Window

Save the configuration (Save and Deploy if you have the Collector in Semi-Automatic); push the configuration to remote hosts if you do not use the collector.

4. Final Audit Testing

With the audit object and audit specification created, and a basic filter setup to send us an email, the setup is complete. You should now start getting emails similar to the one shown below whenever an action that matches your audit specification(s) is performed.