How to analyze and read SQL Server Audit information

In the previous parts of the SQL Server auditing methods series, we described the SQL Server Audit feature, its characteristics, components, how to configure and use it

In this article, we will show what information is captured and how to read it when it’s saved in different target types – a *.sqlaudit file, security log, or application log

The information captured

For all target types, the captured audit information is the same. Some of the columns are not populated if the audited event doesn’t provide this information. For example, a database_name, schema_name, and object_name are not populated when a failed login occurs [1]

event_time – the time when the event occurred
sequence_no – the Log Sequence Number (LSN) of the event
action_id – the ID of the event [2]
succeeded – a bit value that indicates the event status 1 = Success, 0 = Fail
permission_bitmask – not always applicable, shows the permissions granted, revoked, or denied
is_column_permission – a bit value indicates a column level permission 1 = True, 0 = False
session_id – the ID of the session when the event occurred
server_principal_id – the ID of the SQL Server login
database_principal_id – the ID of the database user
object_ id – the ID of the object (server object, database, database object, or schema object) affected by the event
target_server_principal_id – the login that GRANT/REVOKE/DENY is performed on, 0 if not applicable
target_database_principal_id – the database user GRANT/REVOKE/DENY is performed on, 0 if not applicable
class_type – the type of object audited
session_server_principal_name – the SQL Server session login
server_principal_name – the current SQL Server login
server_principal_sid – the security ID of the current SQL Server login
database_principal_name – the current database user
server_instance_name – the SQL Server instance name where the event occurred
database_name – the name of the database where the event occurred
schema_name – the schema name where the event occurred
object_name – the object name where the event occurred
statement – the T-SQL statement that triggered the event, if applicable
additional_information –additional information is stored as XML, if exists
file_name – the path and name of the sqlaudit file read

We’ll show what information is captured for the following events:

A SELECT statement on a table, as a part of the SCHEMA_OBJECT_ACCESS_GROUP Audit Action Type in the Database audit specification

SELECT [AddressID]
	,[AddressLine1]
	,[City]
FROM [AdventureWorks].[Person].[Address]

An insert

INSERT INTO Person.AddressType ( AddressTypeID,
	                        NAME,
	                        rowguid,
	                        ModifiedDate
	)
VALUES (
	7
	,N'Office #2'
	,'7fb99e95-bdcf-4b13-982f-c3d8188cb5d0'
	,'20130509 00:00:00.000'
	);

An update

UPDATE [Person].[AddressType] SET [Name] = N'Office #4'
WHERE [AddressTypeID] = 8

And a drop table, as a part of the SCHEMA_OBJECT_CHANGE_GROUP as the Audit Action Type

DROP TABLE dbo.TestAudit

We will audit a failed SQL Server instance login and the enable/disable of a SQL Server Audit object in the Server audit specification

Reading the audit information from the *.sqlaudit files

There are two methods to read the audit information captured by SQL Server Audit into *.sqlaudit files

I Using the fn_get_file_audit function

The fn_get_audit_file function reads the *.sqlaudit file created by the SQL Server Audit feature

SELECT event_time
	,action_id
	,session_server_principal_name AS UserName
	,server_instance_name
	,database_name
	,schema_name
	,object_name
	,statement
FROM sys.fn_get_audit_file('D:\TestAudits\*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id IN ( 'SL', 'IN', 'DR', 'LGIF' , '%AU%' )

As SCHEMA_OBJECT_ACCESS_GROUP was defined on the database-level, all SELECTs executed on any database table are audited

The action_id = ‘SL’ shows SELECT statements executed, ‘IN’ – inserts, ‘DR’ – dropped objects, ‘LGIF’ – failed logins, ‘%AU%’ – events related to the audit feature, and ‘UP’ – updates

The actual audit feature change (action_id = ‘AUSC’ is stored as XML in the additional_information column. For example, the following is saved when the SQL Server audit object named AdventureWorksAudit_DDL_Access is manually enabled

<action_info xmlns=”http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data”>
<session><![CDATA[AdventureWorksAudit_DDL_Access$A]]></session>
<action>event enabled</action>
<startup_type>manual</startup_type>
<object><![CDATA[audit_event]]></object>
</action_info>

Unlike SELECT, INSERT, DROP TABLE actions shown above, where the T-SQL returned by the function is the same as the statement executed, the T-SQL statement for UPDATEs is different. It doesn’t show the rows affected and the new values, so it cannot be determined what and how was changed

The T-SQL returned is:

UPDATE TOP (200) Person.AddressType SET NAME = @Name WHERE (AddressTypeID = @Param1) AND 
(NAME = @Param2) AND (rowguid = @Param3) AND (ModifiedDate = @Param4)

While the statement executed was

UPDATE [Person].[AddressType] SET [Name] = N'Office #4'
WHERE [AddressTypeID] = 8

II Using the Log File Viewer utility in SQL Server Management Studio

The information retrieved is the same as with the fn_get_audit_file function

1. Expand the Security folder in Object Explorer
2. Expand the Audits folder
3. Right-click the audit you want to read
4. Select View Audit Logs

All entries in the *.sqlaudit file will be shown in Log File Viewer, with details for each event in the lower pane

Reading the SQL Server Audit information from the application log

When the target type is the application log, use Windows Event Viewer to read the results

1. Open Windows Control Panel
2. Open Administrative Tools
3. Open Event viewer
4. Expand Windows Logs
5. Select Application

The Event Viewer will show the events captured via SQL Server Audit among other events. The details for each event are shown in the lower pane. The event details are the same as captured in the *.sqlaudit file

As the application log stores events logged by other programs as well, it’s large so filtering is recommended

1. In the Actions pane on the right, click Filter Current Log
2. In the Logged drop-down menu, select the time-range you want to see
3. As Event sources, select MSSQL$<instance_name>$Audit to see only the events logged by the SQL Server Audit feature

Application logs store a large number of events, not just the events captured by the SQL Server Audit feature, so querying the results and finding the specific ones might not be efficient. That’s why it’s recommended to use a *.sqlaudit file as a target, import the records into a SQL Server table, and use T-SQL or Reporting Services for data analysis

Reading the SQL Server Audit information from the Windows Security log

It’s recommended to use a Windows Security log as a target file in high security environments. The permissions needed for this target type are specific and strict. Tampering with this type of files is also difficult [3]

The log can be read programmatically using APIs, Windows Event Viewer, and SQL Server Management Studio Log File Viewer, as shown previously

Again, due to a large number of logged events, it’s recommended to use filters

When analyzing the audit events in any target file, a large range of information is available – the time the event occurred, who did it, what server, database and object were affected, etc. The information that is not provided is the name of the machine where the event occurred. This is useful in situations when users use the same SQL Server user to connect to the database. One of the drawbacks of this feature is incomplete capturing of the UPDATE statement. The information captured does not indicate what records and columns were changed, nor shows the old and the new values. The most precise information about UPDATEs is captured by SQL Server Change Data Capture, as it clearly indicates what and how was changed

The feature provides the server-level and database-level auditing, pre-defined groups and activities, and audits both DDL and DML changes. It can also audit changes of the auditing. Setting auditing for the INSERT, UPDATE, DELETE, SELECT, REFERENCES, and EXECUTE statements is very granular, it can be specified for each statement individually, for specific users, and objects

The feature provides three types of files for storing the audit events. For deeper analysis, the records saved as files must be queried and inserted into SQL tables, and reports created using T-SQL or Reporting Services as there is no built-in reporting

There is no out-of-the-box solution for archiving or deleting the old records

The feature is lightweight and provides minimal overhead. However, for detailed auditing in high-transaction databases more resources are used, so the feature should be avoided in these scenarios

There is no easy way to manage auditing on multiple SQL Server instances, as each configuration step must be taken on every instance

Unlike SQL Server Change Tracking and Change Data Capture, the SQL Server Audit feature can track SELECT statements

All articles in this series:

1. What is SQL Server Change Tracking and how to set it up?
2. How to read SQL Server Change Tracking results
3. SQL Server Change Data Capture (CDC) – Introduction
4. How to enable and use SQL Server Change Data Capture
5. How to analyze and read Change Data Capture (CDC) records
6. SQL Server Audit feature – Introduction
7. How to set up and use SQL Server Audit
8. How to analyze and read SQL Server Audit information
9. Auditing SQL Server data changes – the centralized solution

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.

References:

[1] MSDN – SQL Server Audit Records
[2] SQL Server Audit action_id List
[3] MSDN – Write SQL Server Audit Events to the Security Log

Useful resources:

MSDN – SQL Server Audit Action Groups and Actions
MSDN – CREATE SERVER AUDIT (Transact-SQL)