By: Nico Botes | Updated: 2023-05-12 | Comments (5) | Related: > Auditing and Compliance
Problem
SQL Server includes a built-in auditing feature to capture various events. This was introduced over a decade ago as part of the Microsoft SQL Server 2008 product launch. Initially, only a full Enterprise Edition included this feature but it was later changed allowing all editions to support every level of server and database audits from SQL Server 2016 SP1 onwards. Still today, this is a very attractive feature included natively available in SQL Server.
Use cases generally fall into one of these buckets: either to capture event activity internally on a SQL Server Database Engine or to track and log events that occur to satisfy external regulatory and compliance standards for government, financial, educational, healthcare, or other institutions based on frameworks such as STIGS, DOD8500, NIST800-53, ISO27001, PCI-DSS, SOX, JSOX, BASEL3, GDPR, POPIA, HIPAA, FERPA, COBIT, CIS, SOC.
SQL Server Auditing utilizes Extended Events in SQL Server. Although it is a feature-rich native solution available in all editions, you'll realize that when you start configuring the Audit and Specifications the filter options are not that widely documented or intuitive. Hopefully, this article will help you plan and implement an effective method of reducing some of the "noisy" events captured in your audit that you do not necessarily want to report on. SQL Server Auditing is a reactive security measure where the "Less is More" principle applies, allowing you to focus on the more critical audit events without missing any required ones.
Solution
In this article, we look at how to use SQL Server Management Studio (SSMS) and Transact-SQL (T-SQL) to enter a predicate filter on the SQL Server Audit definition (this is available in SQL Server 2012 (11.x) and later versions) to allow filtering an audit.
SQL Server Audit Background Information
SQL Server Audit Action Groups are predefined groups of actions at the Server Audit Specification and the Database Audit Specification level, which encapsulate one or more atomic events that occur in SQL Server. These audit action items are intrinsically audited as part of the Action or must be explicitly specified in the Audit Specification. But the bottom line is that an Audit Action Group captures Action Events with specific Action and Class attributes, amongst other fields (see below). Server-Level and Database-Level Audit Action Groups are similar to SQL Server Security Audit Event Classes – if you can remember the event classes from SQL Server Profiler in your trace definition.
The SQL Server Audit consists of three sections: SQL Server Audit, SQL Server Audit Specification, and Database Server Specification – referred to as an Audit.
Here is a summary of each, with some information applicable to the solution to this problem:
- SQL Server Audit: This is the main required object that forms the manifest of the Audit. Think of this as the Parent or the Header component. It allows for various configurations and settings to be applied, such as the output location of the results, which can be a physical file on disk or written entries to the Application or Security Folder in the Windows Event Logs. Note: This is also where you will create the WHERE clause to filter the Audit!
- SQL Server Audit Specification: This instruction contains the Server Audit Action Groups, collecting one or more server-level actions, and then sent to the SQL Server Audit to be recorded in the target. Only one Specification can be linked to an Audit but can be combined with a Database Audit Specification.
- SQL Database Audit Specification: This is also the instruction, at a more granular step, that contains the Database Audit Action Groups, collecting one or more database-level actions, and then sending them to the SQL Server Audit to be recorded in the target. Only one Specification can be linked to an Audit but can be combined with another Server Audit Specification.
The results of events captured by an Audit are best described in this Microsoft Document, detailing the usage of the system defined table-valued function: sys.fn_get_audit_file. When this function is called with the applicable parameters, it returns information from an audit file captured by the SQL Server Audit. The scope of this solution is limited to the file output mechanism of the SQL Server Audit only and excludes the structure of the Windows Application or Security Event Log entries. Before we go any further, let's take a look at the anatomy of the SQL Server Audit File, as illustrated in the following diagram:
Pic. 1: High-level architecture of all the components involved with a SQL Server Audit
In the example, we will read the resulting output file of the Server Audit: [Audit-001-OutputToFile_Srv]
This Audit is linked to the Server Audit Specification, indicated above, as: [SrvAuditSpec-001-LinkTo(Audit-001)] set up to capture all Server Audit Action Types (see script attached).
The fields returned by the function vary and can include Database Name (returns NULLS for events at server-level), Database Principal Name & ID, Object Name & ID, Schema Name, Server Instance Name, Server Principal Name & ID, as well as Action ID & Class Type (both not NULLable).
The fields listed above are simple to use in a filter, for example:
... WHERE server_principal_name <> N'service account name'; ...
But the last two fields, i.e., action_id & class_type, are not as straightforward to use as filter predicates, and that is for a good reason, which I will explain later.
The predicate expression you can optionally specify will be evaluated before the audit event is recorded and will determine whether the field will be sent to be written to the output target. All the fields with the abovementioned nuances, excluding fields file_name, audit_file_offset, and event_time, can be combined with the standard logical operators as filter criteria.
Pic. 2: Output from T-SQL Statement calling system table-valued function reading the contents of an Audit-file
There are two built-in system dynamic management views (DMV) that can be utilized to investigate the output listed above, namely sys.dm_audit_actions and sys.dm_audit_class_type_map. The following queries use the output from the screenshot above, where action_id = "VSST" and class_type = "SR".
Pic. 3: Output from T-SQL Statements calling system DMV's
Logically, removing the Action Group Name, SERVER_OPERATION_GROUP, makes sense. But what if this group had contained more than one action and/or class items? When we run the same DMV, this time for the group name predicate, we do see this from the output below:
Pic. 4: Output from T-SQL Statements calling system DMV
Now what to do? The next step is to attempt to filter the Server Audit and try to exclude the action_id = "VSST". Let's see if that works.
Pic. 4.1: Error message when an attempt was made to use a filter predicate on the Audit
Nope. We encountered an error, as shown in the screenshot above.
Hint: In a T-SQL statement for predicate, it is required to specify the WHERE clause. However, in SSMS, you only need to exclude the keyword and include your filter criteria within a specific bracket notation!
Now we see a different message, but still an error nonetheless:
Pic. 4.2: Error message when an attempt was made to use a filter predicate on the Audit
Why are we able to filter successfully on literal values for certain fields in the output of the Audit, but for action_id (and class_type), it is not consistent?
Msg 25713, Level 16, State 23, Line 5 The value specified for event attribute or predicate source, "action_id", event, "audit_event", is invalid.
The action_id is stored internally in the Database Engine as a number. When you call the system TVF or DMV, it automatically converts the value from the character code to an integer and vice versa. The reasons, as described in the SQL Server Blog Post, which we alluded to earlier in the article, are two-fold:
- User friendliness – the character code we saw in Picture 1 above is much easier to read than a number.
- System design – from a Microsoft SQL Server codebase perspective, the internal metadata of the Database Engine is based on this encoding.
An Integer has 20384234234 possible values…which ones do you use? Luckily, the blog post also provides scalar functions as one of the options to decode the character to an integer, which can be used from the attached T-SQL Script-File: .\MultipleQueryOptions_MSSQLTips_GetINTNumberFrom_ActionID_or_ClassType.sql
To make life even easier, I have created a Power BI Report located in the Microsoft Public Gallery that contains all of this information at the tip of your fingers.
By using either of the above-mentioned methods, you can find the mapped Integer-value for the action_id = "VSST" as:
([action_id]=(538987603))
Configure Audit Filter with SQL Server Management Studio
Step 1
Open SSMS and connect to the SQL Server Instance in our example:
Note: If the Login does not have SYSADMIN permissions, it should be granted the ALTER ANY SERVER AUDIT permission to perform the ALTER (including the DROP and CREATE permissions) of the SQL Server Audit.
Step 2
In Object Explorer, expand the Security folder. Underneath the Security folder, find the Audits folder and expand that as well. Search for the Audit named: [Audit-001-OutputToFile_Srv] from our example.
Step 3
Right-click the Audit name, and choose "Disable Audit".
This step is only required if the Server Audits are "running" or enabled. If you try to edit/modify the Server Audit not in a disabled state, the step will fail.
Step 4
Right-click on the Audit name, or double-click on the Audit name, to open the Audit Properties dialog box. Navigate to "Filter Page".
Step 5
In the white empty code block (if no predicate(s) are present), enter the following T-SQL clause without the WHERE-clause, enclosing the filter predicate in the specific parentheses:
([action_id]=(538987603))
Step 6
When you finish Step 5 above, click OK to save the changes and ensure the transaction completes successfully.
Step 7
Right-click on the Audit name, and choose "Enable Audit". You will receive a successful notification message if you have entered the syntax correctly.
Configure Audit Filter with Transact-SQL
Step 1
Open SSMS, and in Object Explorer, connect to an instance of the Database Engine named: SRV, in our example. Use a Login User, preferably with SYSADMIN permissions.
Note: If the Login does not have SYSADMIN permissions, it should be granted the ALTER ANY SERVER AUDIT permission to perform the ALTER (including the DROP and CREATE permissions) of the SQL Server Audit.
Step 2
On the Standard bar, click "New Query" to open a blank query window, connected to the SQL Server Instance with the applicable Login from Step 1 above.
Step 3
Copy and paste the following example into the query window and click Execute. It is important to mention that the SQL Server Audit needs to be in a disabled state to modify it. If you attempt to modify (ALTER or DROP) a SQL Server Audit in a "running"-state, the transaction will fail, creating the following error message:
Msg 33071, Level 16, State 1, Line 4 This command requires audit to be disabled. Disable the audit and rerun this command.
The code below will include all the required steps in sequence and the cardinal inclusion of the WHERE clause. Ensure that the execution completes successfully.
USE [master] GO ALTER SERVER AUDIT [Audit-001-OutputToFile_Srv] WITH (STATE = OFF); GO ALTER SERVER AUDIT [Audit-001-OutputToFile_Srv] WHERE ([action_id] = (538987603)); GO ALTER SERVER AUDIT [Audit-001-OutputToFile_Srv] WITH (STATE = ON); GO
Testing
As a quick and simple test, open "Activity Monitor" using SSMS again on the same SQL Server Instance in both Solution options above, and let it run for a couple of seconds once started. You could have optionally performed this step at the start to confirm the VIEW SERVER STATE events getting captured. After implementing the solution, verify that the same events are getting excluded based on your predicate filter.
The script to reproduce: Query_MSSQLTips_ConfigSetupSQLServerAudit&SpecExample.sql
Next Steps
- In the next article, we will look at how to utilize this feature and implement this solution in the Microsoft Azure SQL Server Database offerings.
- As an alternative, if you do not want to incur the efforts of building and maintaining a custom solution inside of your SQL Server Product (for which you already pay a premium licensing cost), you are also able to investigate the following paid-for products on the market, described in this article here: Auditing Products for SQL Server (mssqltips.com)
- Power BI Report PBIX-File attached to this tip: MSSQLTips_Article001_SQLSrvAudActions_V1-0.pbix
- For further reading, see related articles here:
About the author
Nico Botes is a Data Platform Specialist, focusing on the Microsoft stack. He loves working with data and started his career in IT working with Microsoft SQL Server. After spending a decade in the UK, working for several multi-national organizations across different industries in various roles, he has settled back in his country of birth in a remote part of the Karoo, South Africa.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2023-05-12
