Using the SQL Server Audit Feature to Audit Different Actions
Using the SQL Server Audit Feature to Audit Different Actions
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 Latest posts by Ahmad Yaseen (see all) 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
SQLShack
SQL Server training EspañolUsing the SQL Server Audit Feature to Audit Different Actions
April 22, 2019 by Ahmad Yaseen 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: 123456 DBCC CHECKDB ([AdventureWorks])GOBACKUP 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'GOCREATE 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: 12345678910111213141516 USE [AdventureWorks]GOCREATE USER [test] FOR LOGIN [test]GOALTER ROLE [db_datareader] ADD MEMBER [test]GOALTER ROLE [db_datawriter] ADD MEMBER [test]GOCREATE SCHEMA HRB GOSELECT * FROM [dbo].[AWBuildVersion]GOINSERT INTO t2 VALUES (10,102)GO DELETE FROM T2 WHERE C1=10GO 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: 123 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
SQL Server Audit Overview Implementing a manual SQL Server Audit Creating a SQL Server audit using SQL Server Extended Events and Triggers Auditing by Reading the SQL Server Transaction Log Change Data Capture for auditing SQL Server Creating a SQL Server audit using SQL Server Change Tracking SQL Server Audit Feature Components Using the SQL Server Audit Feature to Audit Different Actions Performing a SQL Server Audit using System-Versioned Temporal Tables Perform a SQL Server Audit using ApexSQL Audit SQL Server Auditing Best Practices Author Recent Posts Ahmad YaseenAhmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields.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 Latest posts by Ahmad Yaseen (see all) 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