By: Derek Colley | Updated: 2024-06-11 | Comments | Related: > Auditing and Compliance
Problem
You are implementing the auditing feature in SQL Server, but the out-of-the-box server and database audit specifications provided by Microsoft are not sufficient to audit specific activities, so a custom specification is needed.
Solution
In this tip, we will create custom audit specifications using SQL and show how to attach these to an audit in SQL Server. We will test the audit and demonstrate how the output can be used downstream to feed into monitoring and alerting frameworks.
About SQL Server Audit
Relational database management systems (RDBMS) often concern themselves with the storage, handling, and access of critical business data, including information that is:
- Business-confidential, such as financial transactions;
- Subject to legislation, compliance, and control, including Personally-Identifiable Information (PII);
- Conducive to facilitating insider trading;
- Subject to national security constraints; or
- Could influence or prejudice business decisions.
Due to the sensitivity of data potentially held within these systems, it is important that the organization be able to keep a record of any actions that can access or change the data and refer to that record both on-demand (proactively) or have any such action trigger some monitoring, security, or review (reactively).
We call this the ability to audit. In environments with high degrees of customization, out-of-the-box configuration may not suffice. This tip will show how to configure and implement pre-configured audit specifications and how to write and implement our own. We will also show how the outputs can be fed to downstream reactive logging systems.
History and Evolution
SQL Server Audit was introduced as a feature to Microsoft SQL Server in a limited form in SQL Server 2005, with the introduction of DDL triggers, which could, for example, insert an entry into a logging table on the CREATE, ALTER, or DROP commands. However, it was not until SQL Server 2008 that Audit, as a separate feature, was integrated into SQL Server and SQL Server Management Studio (SSMS), where it was initially an Enterprise-only feature.
In later versions (v.2012 to present), Audit became available across all editions at the basic level. Microsoft differentiates 'basic' and 'fine-grained' auditing functionality, corresponding to instance-level (basic) and database-level ('fine-grained') audit specifications. From SQL Server 2016 SP3 on, database-level audit specifications became available to all editions.
Audit is built on the Extended Events framework, being essentially a specialist use-case of the same with database- and instance-level audit configuration components. It leverages the underlying events and event packages corresponding to each definition.
Today, Audit is available in Azure DB Managed Instances, together with Microsoft SQL Server traditional installations, and is well-integrated into SSMS. Audit outputs can be consumed by downstream logging systems via the Windows Application Log (WAL), specific SQL table-valued functions, or reading audit files directly from disk.
Use Cases
It is a truism that an organization's policies are written in blood. This is to say that it is often once the stable door is open and the horse is missing that a business realizes that the ability to track historical actions against its data is vital. Sadly, this is often in retrospect.
Without Audit enabled, there are few options for detailed database forensic investigation. One might be tempted to use the transaction log to review and extract historical and recent transactions; however, if the database is in a simple recovery model and/or transaction log backups aren't available, this may not be viable, and this is a laborious and time-consuming approach in any case that isn't guaranteed to yield useful results. One might argue that increased emphasis on security would prevent accidental or malicious data damage in the first place, but this observation becomes useless after such an event actually occurs. Another argument is that operating system (OS)-level monitoring would be able to correlate system- or service-level login to database activity; this is true circumstantially, but OS-level monitoring does not collect information on DDL statements executed or data extracted, making it impossible to piece together a reliable narrative.
SQL Server Audit vs. Trace/Extended Events
Extended Events replaced SQL Trace, which was officially marked for deprecation in SQL Server 2012 and, although currently available in all versions, is marked for removal in a future version. Extended Events was introduced in SQL Server 2008 and has developed since as the formal framework for events monitoring and management, replacing Trace; Audit builds upon Extended Events to offer user- and DBA-friendly pre-configurations specifically for audit and compliance purposes.
So why use Audit over Extended Events if Audit is merely a subset of the functionality of Extended Events?
Some good reasons include:
- As of SQL Server 2022, Microsoft provides 48 server-level audit action groups and 29 database-level audit action groups, which are pre-configured groups of audit actions that combine to achieve a named goal. For example, the BACKUP_RESTORE_GROUP notifies whenever a backup or restore operation command is issued. These pre-configurations are accessible and easy to configure without the need to find the equivalent class in Extended Events (in the case of this example, the 'Audit Backup and Restore Event Class').
- Audit provides a neat wrapper to configuring Extended Events, which is easy to set up and use for even inexperienced DBAs. In contrast, Extended Events is a more fully-featured framework that can be intimidating and difficult for even the most seasoned professionals (Jason Strate and others even wrote a 336-page book about it!).
- Some of the features of Extended Events are equally available in Audit. For example, the opportunity to write custom audit actions that apply filters, or predicates, to the actions being taken and the ability to write out to the Windows Application Log.
However, you might prefer Extended Events if any of the following apply:
- More Complex Requirements in Terms of Target Configuration. Extended Events allows event collection to the ring buffer (memory area), for example, or to pair-matching (which can detect paired events where only one of the two items occurs – a lock taken out or a transaction starting), or to the Event Tracing for Windows (ETW) framework, or output to file or Azure BLOB storage, or even as an event stream object for consumption by C#-based monitoring applications.
- Customization. You have a need for very fine-grained auditing and the granularity of the audit actions that Audit provides is too coarse to meet your needs.
Pre-Configured Audit Specifications
An important note: Both server- and database-audit specifications are tied to an individual server audit. We can have multiple server audits, which reside at the instance level, but each audit corresponds to exactly zero-to-one server audit specifications and zero-to-one database audit specifications. There is no direct relationship between a server audit specification and a database audit specification, except by virtue of sharing a server audit object.
It may be beneficial to have multiple server audit specifications, like for writing out to multiple sources. Equally, it may be beneficial to have multiple database audit specifications, such as for activating different audit specifications at different times.
The following diagram illustrates how database audit specifications and server audit specifications relate to server audit objects:
Server Audit Specifications
Microsoft provides a comprehensive set of 48 server audit specifications (as of 2022), which we can use out of the box. A short list is below; find more information: SQL Server Audit Action Groups and Actions.
The exact operations audited per specification aren't easy to get to – for each item, in the Microsoft documentation at the link above, click the link to the relevant Extended Events class associated with the server audit specification, then in the event class data columns specification, scroll down to the relevant row. The data columns returned can differ between groups; this is where the power of Audit really helps because we don't need to worry about this in our output format (to the WAL or audit files) - it's taken care of by the Audit feature.
To take two examples at random, the EventSubClass descriptor in the data columns associated with the DATABASE_OPERATION_GROUP specification (Audit Database Operation Event Class in Extended Events) shows that this specification audits the following activities:
- CHECKPOINT operation (in any database), and
- Subscribe to Query Notification (in any database).
To look at another example, the DBCC_GROUP audit corresponds to the Audit DBCC Event Class in Extended Events, and the data columns returned by that class include the EventClass and TextData columns which return the type of event and the text of the DBCC command issued. We can infer it audits and records all DBCC commands issued. We also have a great deal of contextual information available, such as the user who ran the command, the database it was run against, and so forth.
We can set up a new server audit specification simply enough. The following example creates a new audit object, configures this to write to audit file(s), sets the maximum file size as 256MB, sets the maximum number of files as 10 (files roll over in the same way the error logs do) and reserves the disk space up front to avoid nasty disk space surprises; the example then configures two server audit action groups under a single specification linking this to the server audit object by name and enables both the audit and the specification.
-- First, set up the server audit object
CREATE SERVER AUDIT TestAudit
TO FILE ( FILEPATH = 'C:\TEMP\TESTAUDIT\',
MAXSIZE = 256MB,
MAX_ROLLOVER_FILES = 10,
RESERVE_DISK_SPACE = ON
)
WITH (
ON_FAILURE = CONTINUE
);
GO
-- Now, set up the server audit specification we'd like. We can have multiple groups within a single specification, but we can have only one specification per server audit object.
CREATE SERVER AUDIT SPECIFICATION ServerAuditSpecification FOR SERVER AUDIT TestAudit
ADD (DATABASE_OPERATION_GROUP),
ADD (DBCC_GROUP)
GO
-- Now, enable both.
ALTER SERVER AUDIT TestAudit WITH ( STATE = ON );
ALTER SERVER AUDIT SPECIFICATION ServerAuditSpecification WITH (STATE=ON);
Database Audit Specifications
As with server audit specifications, Microsoft provides a number of database audit specifications out-of-the-box. These also correspond to Extended Events audit classes and details of this correspondence are on the Microsoft documentation page: Database-Level Audit Action Groups.
Unique to database audit specifications, one may also configure database-level audit actions, not just action groups. This means we can configure custom database audit specifications that capture SELECT, UPDATE, INSERT, DELETE, EXECUTE, RECEIVE, and REFERENCE operations. We examine this in greater detail in the next section.
The example below sets up a database audit specification using a predefined audit action group.
-- First, set up the server audit object – skip if already in place.
CREATE SERVER AUDIT TestAudit
TO FILE ( FILEPATH = 'C:\TEMP\TESTAUDIT\',
MAXSIZE = 256MB,
MAX_ROLLOVER_FILES = 10,
RESERVE_DISK_SPACE = ON
)
WITH (
ON_FAILURE = CONTINUE
);
GO
-- Now, set up the database audit specification we'd like. We can have multiple groups within a single specification, but we can have only one specification per database audit object.
USE <dbname>
GO
CREATE DATABASE AUDIT SPECIFICATION DatabaseAuditSpecification FOR SERVER AUDIT TestAudit
ADD (SCHEMA_OBJECT_ACCESS_GROUP),
ADD (USER_CHANGE_PASSWORD_GROUP)
-- Now, enable both.
ALTER SERVER AUDIT TestAudit WITH ( STATE = ON );
ALTER DATABASE AUDIT SPECIFICATION DatabaseAuditSpecification WITH (STATE=ON);
Limitations of Pre-Configured Audit Specifications
There are plenty of caveats about the limitations of using preconfigured audit action groups. Some of these limitations are listed below, with mitigations. Generally speaking, the preconfigured server-level audit action groups tend to be sufficient for most needs—groups exist for auditing all DDL activity, for example, or batch start/end dates; there is even a separate group of audit-level audit action groups for auditing audit activity! For database-level audits, the addition of specific object-level, command-level specifications of audit functions are powerful.
However, it is worth bearing in mind:
- Server-level audit actions/action groups don't cater to database-level audit filtering—a separate database audit specification is recommended.
- Transactional-level server audit action groups (TRANSACTION_GROUP) will only work if Common Criteria Compliance is enabled. Microsoft also warns that trace flag 3427 disables transaction-level auditing, beginning from SQL Server 2016 SP2 CU3.
- In database-level audit specifications, audits on types, XML schema collection, database objects, and schema objects are not auditable. Instead, use server-level audit specifications, especially for database- and schema-level audit actions.
- From Microsoft: 'Database-level audit actions do not apply to columns.' Columnar-level auditing, i.e., auditing column accesses, is not supported; instead, audit by standard DML SQL operations. This does not allow filtering.
- If the query is parameterized, then the parameters (@1, @2, etc.) may appear in the audit log instead of the actual values of the query. This could be a limitation if parameterized queries, i.e., sp_prepare, are used frequently in the codebase.
- Server- and database-level audit specifications do not allow for custom filtering of auditable actions. This is achievable with database audit actions in a specification at the DML level, as already described, but otherwise, groups are provided as-is. For more detailed customization, configuring Extended Events may be a better option.
Creating Custom Audit Specifications
Custom Server Audit Specifications
Note: SQL Server does not support the creation of custom server-level audit actions in the same way that it supports the use of custom database audit actions since the type and specification of events that can occur at the server/instance level are finite, whereas the type and specification of events that can occur at the database object level are infinite (bounded by the number of tables, views, etc. that exist). Therefore, Extended Event classes map to every one of the server audit action groups and cover nearly all possible scenarios.
However, we can add predicate WHERE filters at the server audit level – the level above the server audit specification. To do this, we simply add a WHERE clause to the end of the CREATE SERVER AUDIT statement like this:
CREATE SERVER AUDIT MyTestAudit TO APPLICATION_LOG WHERE object_name = 'InterestingTableToAudit';
This is particularly powerful as we can audit any event fields that turn out in the audit file/application log—even statements. So, this is a way of adding a predicate WHERE to the audit a priori rather than a posteriori, which is a great way of simplifying log files and saving on performance overhead.
Custom Database Audit Specifications
However, custom database audit specifications can be created by specifying one's own database audit actions, including filters on the objects concerned and the DML used.
First, we must specify what we wish to audit, at what level of detail we require, and any exceptions to the object(s) or types of accesses concerned.
We will work through this with a fictional use case and implementation example based on the AdventureWorks2019 database.
Scenario. Sally is a Product Owner working with the technical team behind the Adventure Works Cycles company. In this company, salespeople sell cycle-related products to the general public. They earn a commission on the sales and work to a sales quota.
Recently, a salesperson was caught modifying their sales figures and commission percentage to fraudulently maximize their take-home income. To prevent this from happening again, Sally has instructed the technical team to implement SQL Server Audit with a custom database-level audit action specification to log any future changes to any salesperson's historical and current YTD sales figures or their set commission level.
Analysis. The first step is to analyze the requirements and identify the objects involved.
Examining the schemas in AdventureWorks2019, these are:
[Table] - Sales.SalesPerson
[Column] - Sales.SalesPerson.SalesQuota
[Column] - Sales.SalesPerson.Bonus
[Column] - Sales.SalesPerson.CommissionPct
[Column] - Sales.SalesPerson.SalesYTD
[Column] - Sales.SalesPerson.SalesLastYear
[Column] - Sales.SalesPerson.ModifiedDate
[View] - Sales.vSalesPerson
[Column] - Sales.vSalesPerson.SalesQuota
[Column] - Sales.vSalesPerson.SalesYTD
[Column] - Sales.vSalesPerson.SalesLastYear
Next, we will identify the actions we want to audit based on the requirement. We ask ourselves, what actions would a malicious actor want to take to achieve the goal of fraudulently misrepresenting sales?
- INSERT new rows to Sales.SalesPerson, perhaps inventing new salespeople or updating row versions;
- UPDATE rows to supersede existing values;
- ALTER the VIEW to replace actual values with hardcoded fraudulent values for particular salespeople
Specification. So, we can see we need to audit, at a minimum, INSERT and UPDATE operations against the Sales.SalesPerson table and DDL operations, particularly ALTER, against the associated view.
This will require:
- Database-level audit specification encompassing the ALTER DDL statement against the view; and
- Custom database-level audit actions on INSERT, UPDATE on the Sales.Salesperson table.
Looking through our list of available preconfigured database audit specifications, we see DATABASE_OBJECT_CHANGE_GROUP, which might do for the DDL audit item. Checking the specification from the Microsoft documentation, we see this is defined as:
'This event is raised when a CREATE, ALTER, or DROP operation is executed on database objects...'
And, it is correlated to the Audit Database Object Management Event Class in Extended Events.
Important! As we'll see below, there is no iron-clad guarantee that the audit action group we select will work as expected, even if indicated in the documentation. The test below will illustrate this. Always make sure you test your audits!
Checking this class definition (Audit Database Object Management Event Class), we see that the return table provides the username (DBUserName) and the EventSubClass (type of action carried out). EventSubClass = 2 corresponds to the ALTER event.
Next, we define that we want to audit INSERT and UPDATE operations on the Sales.SalesPerson table. Remember that database audit actions don't audit by column. This limitation has already been discussed, so any INSERT or UPDATE operation will be audited regardless of which columns are affected. This might result in many false positives.
Now, we can define our server audit, assign this audit action group to a new server audit specification, and define our custom database audit actions all in one code snippet.
We cannot filter by column, but we can filter by server principal. So, if we want to audit changes by salespeople (assuming they have their own SQL-level logins, not just an application login) and ignore legitimate changes by managers, we may change the BY public to BY <username>. Note: BY public audits all accesses. We can, however, filter the audit file by any number of available criteria when we query it, and we cover this in the next section.
-- First, set up the server audit object – skip if already in place.
USE master
GO
CREATE SERVER AUDIT ServerAudit1
TO FILE ( FILEPATH = 'C:\TEMP\ADVENTUREWORKSAUDIT\',
MAXSIZE = 256MB,
MAX_ROLLOVER_FILES = 1,
RESERVE_DISK_SPACE = ON
)
WITH (
ON_FAILURE = CONTINUE
);
GO
USE AdventureWorks2019
GO
CREATE DATABASE AUDIT SPECIFICATION AdventureWorksAudit FOR SERVER AUDIT ServerAudit1
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (INSERT, UPDATE ON Sales.SalesPerson BY public);
-- Now, enable both.
USE master
GO
ALTER SERVER AUDIT ServerAudit1 WITH ( STATE = ON );
USE AdventureWorks2019
GO
ALTER DATABASE AUDIT SPECIFICATION AdventureWorksAudit WITH (STATE=ON);
Now we test it:
-- Query the audit file - 1 row returned indicating the audit setup
SELECT *
FROM sys.fn_get_audit_file('C:\TEMP\ADVENTUREWORKSAUDIT\*', NULL, NULL)
-- SELECT the contents of Sales.SalesPerson SELECT * FROM Sales.SalesPerson
-- UPDATE the commission percentage for BusinessEntityID (links to person) 276 from 0.015 to 0.25
UPDATE Sales.SalesPerson
SET CommissionPct = 0.25
WHERE BusinessEntityID = 276;
-- Query the audit file again - 2 rows returned, note action_id = UP means an update occurred
SELECT *
FROM sys.fn_get_audit_file('C:\TEMP\ADVENTUREWORKSAUDIT\*', NULL, NULL)
-- Who updated the commission percentage and when did it happen?
SELECT event_time, server_principal_name, database_name, schema_name, object_name, statement
FROM sys.fn_get_audit_file('C:\TEMP\ADVENTUREWORKSAUDIT\*', NULL, NULL)
WHERE action_id = 'UP';
GO
-- Now we will drop and recreate the view to uplift the SalesLastYear figure by 10% for salesperson Linda Mitchell.
DROP VIEW [Sales].[vSalesPerson];
GO
CREATE VIEW [Sales].[vSalesPerson]
AS SELECT
s.[BusinessEntityID], p.[Title], p.[FirstName], p.[MiddleName], p.[LastName], p.[Suffix],
e.[JobTitle], pp.[PhoneNumber], pnt.[Name] AS [PhoneNumberType], ea.[EmailAddress], p.[EmailPromotion],
a.[AddressLine1], a.[AddressLine2], a.[City], [StateProvinceName] = sp.[Name], a.[PostalCode],
[CountryRegionName] = cr.[Name], [TerritoryName] = st.[Name],
[TerritoryGroup] = st.[Group], s.[SalesQuota], s.[SalesYTD],
-- Here is the hostile amendment
CASE WHEN p.FirstName = 'Linda' AND p.LastName = 'Mitchell' THEN s.[SalesLastYear] * 1.1
ELSE s.[SalesLastYear] END [SalesLastYear]
FROM [Sales].[SalesPerson] s
INNER JOIN [HumanResources].[Employee] e ON e.[BusinessEntityID] = s.[BusinessEntityID]
INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = s.[BusinessEntityID]
INNER JOIN [Person].[BusinessEntityAddress] bea ON bea.[BusinessEntityID] = s.[BusinessEntityID]
INNER JOIN [Person].[Address] a ON a.[AddressID] = bea.[AddressID]
INNER JOIN [Person].[StateProvince] sp ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
LEFT OUTER JOIN [Sales].[SalesTerritory] st ON st.[TerritoryID] = s.[TerritoryID]
LEFT OUTER JOIN [Person].[EmailAddress] ea ON ea.[BusinessEntityID] = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PersonPhone] pp ON pp.[BusinessEntityID] = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PhoneNumberType] pnt ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID];
GO
/*
Query the audit file again - 3 rows returned, note the 3rd row capturing the
CREATE VIEW operation.
Important! DATABASE_OBJECT_CHANGE_GROUP purports to capture ALTER and DROP statements; in this -- test, these were NOT captured, despite the documentation assuring us that ALTER operations are captured by this class.
However, the CREATE VIEW was captured successfully.
This highlights the importance of making sure appropriate audit action groups are chosen AND tested.
We could capture ALTERs through transaction-level logging (BATCH_COMPLETED_GROUP for example) or -- by looking to see if another audit action group would do this (e.g. at the server level, or an alternative at the database level).
*/
SELECT event_time, server_principal_name, database_name, object_name, statement
FROM sys.fn_get_audit_file('C:\TEMP\ADVENTUREWORKSAUDIT\*', NULL, NULL)
WHERE statement LIKE ('CREATE%');
Audit File Consumption and Actions
Output Formats
Audit files can be output to:
- Windows Application/Security Log – Note: to write to the Security log, additional configuration is needed in Windows – see Write SQL Server Audit events to the Security log.
- Audit Files - Accessible via sys.fn_get_audit_file('path', 'filename'|NULL, NULL);
These are specified in the CREATE SERVER AUDIT command – the full syntax is available at CREATE SERVER AUDIT (Transact-SQL).
Remember, as already discussed, that WHERE conditions (predicates) that limit the objects to which an audit is constrained are specified here, not in the server audit specification.
Interfacing with Log Monitoring Solutions
Any log consumption mechanism capable of using SQL queries to fetch data or interfacing with the Windows Application Log can be used to consume the audit output. This includes, for example, Splunk, DataDog, or variations on the ELK stack (Elastic, LogStash/Beats, Kibana).
- Using sys.fn_get_audit_file. Expect the output fields listed in the following Microsoft documentation page: sys.fn_get_audit_file (Transact-SQL)
- Using the Windows Application Log, filter on Event ID and Action ID. You can fetch a full data dictionary of auditable items from the sys.dm_audit_actions system view, joining on action_id and/or class_desc to audit content, which can be useful when setting up downstream event rules. See this Microsoft documentation for more information: sys.dm_audit_actions (Transact-SQL)
SELECT * FROM sys.dm_audit_actions order by class_desc, action_id;
(616 rows in SQL Server 2019 RTM)
Next Steps
About the author
Dr. Derek Colley is a UK-based DBA and BI Developer with more than a decade of experience working with SQL Server, Oracle and MySQL.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2024-06-11
