SEM and SQL Server Audit - Better Together

As a production database administrator for many years, I was tasked with security requests. These requests ranged from “who changed what” to detection of SQL injection attacks. The role taught me how proper data security is a never-ending job, requiring the right tools and knowledge.

This is one reason I advocate the use of Security Event Manager (SEM) to help with database security requirements. With SEM you can use the SQL Audit Events connector to monitor for security events. The previous version of the connector required a server-side trace to capture events related to schema changes, user changes, and failures for any query activity.

The latest version of SQL Audit Events connector allows for using SQL Server Audit instead of a trace. SQL Server Audit is a great feature, but a bit cumbersome to work with if you haven’t before.

The first step is to create a Server Audit. This is the “kitchen sink” for SQL Server Audit, as it catches events and determines where to send the event output. The SQL Audit Events connector requires the SQL Server Audit output to the security or application event log on the server. One thing to note here - the Windows event log can fill up and be overwritten. Make sure you have modified the retention policy accordingly before you flood the event logs with audit events from SQL Server. It’s also worth noting that the Windows Application event log is less secure than the Windows Security event log - any authenticated user is allowed to read and write from the Windows Application event log.

After you have created the Server Audit, the next step is to create either a Server Audit Specification or a Database Audit Specification. The Server Audit Specification is for events affecting the instance of SQL Server, and you can only have one Server Audit Specification output to one Server Audit object. The Database Audit Specification is for events affecting a specific database, and you can have multiple Database Audit Specifications output to a Server Audit object. Here’s what it all looks like:

The full list of SQL Server Audit action groups and actions can be found here. It is difficult to list out the specific groups and actions, as each company will have different requirements. But there’s a few I would suggest you consider.

First, start by auditing the audit. You will want to know if the audit has been turned on or off, or if it has been altered in any way. You will use the AUDIT_CHANGE_GROUP for this task.

Next, you should set up a Server Audit Specification for events that affect the entire instance. I recommend the following:

FAILED_DATABASE_AUTHENTICATION_GROUP

LOGIN_CHANGE_PASSWORD_GROUP

SERVER_PRINCIPAL_CHANGE_GROUP

SERVER_ROLE_MEMBER_CHANGE_GROUP

USER_CHANGE_PASSWORD_GROUP

Be mindful that a busy server will flood your event log. Be precise with what data you want to collect. While it is possible to collect events at a server instance level for all database activity, doing so will flood the event log. That’s why I recommend using Database Audit Specifications inside of the databases you want to audit. These are the groups you should consider at a minimum:

DATABASE_OBJECT_CHANGE_GROUP

DATABASE_PERMISSION_CHANGE_GROUP

DATABASE_PRINCIPAL_CHANGE_GROUP

DATABASE_PRINCIPAL_IMPERSONATION_GROUP

DATABASE_ROLE_MEMBER_CHANGE_GROUP

You must review the groups and actions to decide if they meet your auditing requirements. The ones I have listed here are meant as a guide, a starting foundation upon which to build.

You will notice I didn’t include any groups or actions regarding query activity, such as a SELECT statement. I don’t like the idea of capturing that anything that has query data, especially update or insert data, and allowing that text stored in an event log or inside the SEM database.

SQL Server Audit is a great tool that doesn’t get enough love and attention, in my opinion. To me, the strength of this feature is how you can extend it to do things like auditing SQL Agent jobs. I’ve written an example here: https://thomaslarock.com/2017/10/audit-sql-server-jobs/

The downside to SQL Audit is the reporting and viewing of the audit event data. SQL Server Management Studio has a log viewer, but the user experience can be frustrating at times. By using SEM we create a better user experience. Not just for viewing event data, either. SEM allows for the creation of Correlation Rules, allowing us to automate actions to take if a specific event occurs. Here’s an example:

I can create a custom rule that would trigger an action, in this case I will have an email sent should a database object change event is found. You can’t do that out of the box with SQL Server Management Studio.

If you are using SQL Audit, you should give SEM a trial and discover what is possible. If you are using SEM, you should consider leveraging SQL Audit to enhance your security. Together, SQL Audit and SEM offer you the opportunity to lower your risk of loss due to a data breach.