In the previous article of the SQL Server Audit series, SQL Server Audit Feature Components, we discussed the three main components of the SQL Server Audit feature; the SQL Server Audit, the Server Audit Specifications, and the Database Audit Specifications in details. In this article, we will show how to use the SQL Server Audit feature to track, log and review the different operations performed at both the SQL Server and the database levels.
SQL Server Instance Level Actions
SQL Server provides us with a number of server-level audit action groups that can be tracked and audited using the SQL Server Audit feature. These action groups include:
- AUDIT_CHANGE_GROUP: This event is raised when any audit or audit specification is created, modified or deleted
- BACKUP_RESTORE_GROUP: This event is raised when a backup or restore command is issued
- DATABASE_CHANGE_GROUP: This event is raised when a database is created, altered, or dropped
- DATABASE_OBJECT_CHANGE_GROUP: This event is raised when a CREATE, ALTER, or DROP statement is executed on database objects, such as schemas
- DBCC_GROUP: This event is raised when any DBCC command is executed
- FAILED_LOGIN_GROUP: This event is raised when a principal tried to log on to SQL Server and failed
- Note: For a complete list of all server-level audit action groups, check the Server-Level Audit Action Groups document.
Starting from SQL Server 2012, SQL Server provides us with the ability to create user-defined audit events, that can be integrated with ant application and allow it to write a customized event using sp_audit_write procedure. In order to track and audit the event written by an application, the USER_DEFINED_AUDIT_GROUP should be selected in either the Server or the Database Audit Specification.
Assume that you are requested to track and audit the action groups described previously. To do that, you need to configure and enable the Server Audit Specification below, that is bind to a SQL Server Audit:
To check how the Server Audit Specification can be used to audit server-level actions, we will perform number of actions at the SQL Server instance, as below:
|
1 2 3 4 5 6 |
DBCC CHECKDB ([AdventureWorks]) GO BACKUP DATABASE [CTAudit] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\CTAudit.bak' WITH NOFORMAT, NOINIT, NAME = N'CTAudit-Full Database Backup' GO CREATE DATABASE NewTestDB |
Once these operations are performed, you will see that the SQL Server Engine tracked and logged these actions to the audit target specified in the SQL Server Audit. Whatever target specified in the SQL Server Audit, you can simply view the audit logs by right-clicking on the SQL Server Audit, under the server-level Security node, and choose the View Audit Logs options, as shown below:
In the displayed Log File Viewer, you can review all the tracked actions that are performed by the server principals, caught by the SQL Server Audit feature and logged to the SQL Server Audit target, as below:
For example, the previously executed DBCC command, the performed database backup operation, the newly created database and the failed login attempt are audited and logged by the SQL Server Audit built-in feature, with complete details about the action date time, the operation type, the principal name, the server name, the database name, if the operation is completed successfully or failed, the executed statement and other useful information, as shown clearly below:
SQL Server Database Level Actions
SQL Server provides us also with a number of database level audit actions and action groups that can be tracked and audited using the SQL Server Audit feature. These actions and action groups include:
- DATABASE_PRINCIPAL_CHANGE_GROUP: This event is raised when a database user is created, altered, or dropped from a database
- SCHEMA_OBJECT_CHANGE_GROUP: This event is raised when a CREATE, ALTER, or DROP operation is performed on a schema
- SELECT: This event is raised when a SELECT statement is executed
- INSERT: This event is raised when an INSERT statement is executed
- DELETE: This event is raised when a DELETE statement is executed
- Note: For a complete list of all database-level audit actions and action groups, check the Database-Level Audit Action Groups document.
Assume that we are requested to design an audit solution to track the list of database-level actions and action groups described previously. To achieve that, we need configure and enable the database Audit Specification below, that is bind to a SQL Server Audit. Recall that when you select to audit an action group, you will not be able to specify the properties of the audited object. On the other hand, choosing an action to be audited will require you to specify the class of the audited object, the name of the object(s) to be audited and the name of the principal(s) that will be audited on the selected action, as shown below:
To see how the Database Audit Specification can be used to audit the database-level actions, let us perform the operations below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
USE [AdventureWorks] GO CREATE USER [test] FOR LOGIN [test] GO ALTER ROLE [db_datareader] ADD MEMBER [test] GO ALTER ROLE [db_datawriter] ADD MEMBER [test] GO CREATE SCHEMA HRB GO SELECT * FROM [dbo].[AWBuildVersion] GO INSERT INTO t2 VALUES (10,102) GO DELETE FROM T2 WHERE C1=10 GO |
From the Log File Viewer, you can see that all the performed actions will be caught by the SQL Server Audit feature and logged to the SQL Server Audit target, as below:
For example, the previously executed CREATE USER statement, the newly created database schema and the executed SELECT, INSERT and DELETE statements are audited and logged by the SQL Server Audit built-in tool, with useful details that describe when the action is performed, the type of that operation, the name of the principal who performed this change, the server name from which the command executed, the database name, whether this operation is completed successfully or failed, the executed statement and more useful information, as shown below:
Reading Audit Data File
If the SQL Server Audit is configured to write the audit data to a flat file, with SQLAUDIT extension, the data inside that file cannot be read directly from the file. If you open the audit file using any text editing tool, you can see only useless data, as the audit data is written to the file in binary, as shown below:
In order to view the audit data that is written to the target flat file, the fn_get_audit_file() table-valued function should be used. This function accepts three parameters:
- File Pattern: that specifies the path or the path with file name for the audit file set to be read. You can read all the audit files in a specific folder by specifying the path of the folder with the asterisk (*)
- Initial File Name: that specifies the path and name of a specific file in the audit file set to start reading audit records from
- Audit record Offset: that specifies a known location in the specified initial_file_name. If this parameter is provided, the function will start reading at the first record of the buffer immediately following the specified offset
The fn_get_audit_file function can be used within a SELECT statement that makes it more flexible to filter the audit data retrieved from the file to limit the number of retrieved columns or rows. For example, rather than displaying all the audit file content, you can use the fn_get_audit_file function to read the actions performed during a specific period of time, a specific type of actions, the action performed by a specific principal, on a specific database and so on, as in the T-SQL script below:
|
1 2 3 |
SELECT event_time,Action_id,succeeded,server_principal_name,database_name,object_name,Statement FROM fn_get_audit_file('C:\Ahmad Yaseen\*',default,default) WHERE Action_id in ('LGIF', 'CR','AL','BA','IN','DL') |
And the result in our example will be like:
Important Considerations
- The SQL Server Audit file should be secured and protected from any unauthorized access. This can be achieved by writing the file to a secure location on a different server with limited permission to the auditors only, or simply writing the audit logs to the Windows Security log
- When a database, with a Database Audit Specification configured on it, is attached to a new instance that has no SQL Server Audit, has SQL Server Audit that has a different GUID, or the new SQL Server instance does not support the SQL Server Audit feature, this Database Audit Specification will be orphaned and will not record any event. This situation can be corrected by binding the Database Audit Specification to an existing SQL Server Audit, or create a new SQL Server Audit and bind that Database Audit Specification to it
- When a database, with Database Audit Specification, configured on it, participates in high availability solution such as Database Mirroring or Always On Availability Group, the mirrored and secondary replicas should contain a SQL Server Audit with the same GUID as in the Principal and the Primary servers. In addition, the SQL Server service account should have permission on the folder where the audit log files will be written
- If the SQL Server Audit is configured to shut down in case of any failure writing the audit logs to the specified target, using ON_FAILURE=SHUTDOWN option, the SQL Server instance will not be able to start normally. In this case, the SQL Server instance can be started in minimal configuration mode using the -f flag in the startup parameters. In addition, the database administrator can fix the audit issue by starting the SQL Server instance in Single User mode using the -m flag in the startup parameters
- For better performance, audit only the events that you really need to track
In the next article, in this series, we will show how to audit the SQL Server using the system temporal table feature. Stay tuned!
Table of contents
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021