Auditing is a key feature in any application or any system as it provides end users with better analysis for administrators. Apart from analysis, auditing can be used as a troubleshooting mechanism too. Apart from organizational reasons, there are compliance reasons for enabling auditing depending on the domain of operation.
Auditing is mainly about answering four questions, i.e. who, when, what and where. However, depending on the situation, it might be decided what questions of the mentioned four should be answered.
Following are the most common reasons to use auditing shown in the below table.
| Task | Description |
| Ensure | Accountability for all actions – in particular server, database, schema, and table operations |
| Discourage | Users from inappropriate actions due to their accountability |
| Assist | The investigation of suspicious activity |
| Notify | An auditor of the actions of an unauthorized user |
| Monitor | Gather data about specific database activities |
| Detect | Problems with an authorization or access control implementation |
| Ensure | Compliance with legal requirements, such as: Sarbanes-Oxley Act, Data Protect Act (UK) |
Since databases are used in most applications for the backend, databases are expected to enable auditing as data auditing is a key feature for an organization. Before SQL Server 2008 era, there was no direct way of implementing database audit. In those versions of SQL Server, SQL Profiler was used as an auditing mechanism. However, due to many issues, mainly performance, using SQL Profiler is not a viable option mainly in Production environment. For data level auditing, INSTEAD triggers are used. This has resulted in some performance issues and users and reluctant to use triggers due to these reasons.
In SQL Server 2008, tailor-made auditing option was introduced and during the later versions of SQL Server, auditing feature is more improved. This article is to discuss options of auditing in SQL Server 2017 which is the latest version of SQL Server.
In SQL Server, there are two main configurations for auditing, namely server and database as shown in the SQL Server Management Studio below.
Server Audit Specifications
Before creating an audit, you need to create an audit specification as shown below.
After specifying the audit name, the next step is to set the Queue delay. It is recommended set at least more than one second. If this is set to zero, audit will be delivered synchronously. This is not recommended as this will increase the transaction duration. Auditing is highly unlikely to be needed for synchronous delivery.
Next is a new feature introduced in SQL Server 2012. It will decide what would happen when the failure in the audit log. In some compliance standards, the system should down when a failure occurs in the Audit. This means auditing should be a high priority.
The Audit designation is to define where the Audit destination should occur. The default option is File where audit records are written to the File. In case of File option, there are other configurations to be done. However, for the Application and Security logs are the other options available. These options are available so that standard tools can be utilized when they are available at the event viewer.
With the File option, most of the options are self-expiatory except Reserve disk space option. Clicking this option will pre-allocate the file size so that later failures will not happen. However, this is not a frequently selected option. When setting the file path, it is important to define correct RAID level for the disk.
Another new feature introduced in SQL Server 2012 is the filter option. This is to include a where option for the Audit. For example, if (object_name = ‘Simple_Table’) is set as a filter, entire audits will be filtered for the filter condition. This filtering happens at the top level so that auditing can be easily handled.
By default, auditing is disabled. This means that just because you create the audit specification it will not be active. It will be active only when you enable it after creating it.
The same thing can be done from T-SQL as shown below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
USE [master] GO CREATE SERVER AUDIT [Audit_001] TO FILE ( FILEPATH = N'C:\AuditData' ,MAXSIZE = 0 MB ,MAX_FILES = 1000 ,RESERVE_DISK_SPACE = OFF ) WITH ( QUEUE_DELAY = 1000 ,ON_FAILURE = CONTINUE ) -- Enabling Audit ALTER SERVER AUDIT [Audit_001] WITH (STATE = ON) GO |
After creating the server audit and enabling it, the next server audit or database audit can be created. Before creating those specifications, it is essential to get a high-level understanding of the relationships between them and the workflow of SQL Server Audit as shown in the below image.
It is important to highlight the fact that, to create a database specified audit, there is no requirement to create a server audit specification. Instead, the audit needs to be created. Once the audit specification is created, it can be viewed from the audit and all the audit specifications created under the audit whether it is Server specification audit or Database specification audit, can be viewed.
A Server specification audit can be created by the shown in the option using SQL Server Management Studio.
There are several Action Types which can be configured and a sample of them are shown in the below table.
| Action Group Name | Description |
| SUCCESSFUL_LOGIN_GROUP | Indicates that a principal has successfully logged in to SQL Server |
| LOGOUT_GROUP | Indicates that a principal has logged out of SQL Server |
| FAILED_LOGIN_GROUP | Indicates that a principal tried to log on to SQL Server and failed |
| LOGIN_CHANGE_PASSWORD_GROUP | This event is raised whenever a login’s password is changed by way of ALTER LOGIN statement or sp_password stored procedure |
| APPLICATION_ROLE_CHANGE_PASSWORD_GROUP | This event is raised whenever a password is changed for an application role |
| SERVER_ROLE_MEMBER_CHANGE_GROUP | This event is raised whenever a login is added or removed from a fixed server role |
| DATABASE_ROLE_MEMBER_CHANGE_GROUP | This event is raised whenever a login is added to or removed from a database role |
| BACKUP_RESTORE_GROUP | This event is raised whenever a backup or restore command is issued |
| DBCC_GROUP | This event is raised whenever a principal issues any DBCC command |
A full list can be found at SQL Server Audit Action Groups and Actions
It is important to note that by default, Server Audit is disabled and should be enabled explicitly.
Similarly, Server Audit Specification can be created using T-SQL.
|
1 2 3 4 5 6 |
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20171112-222330] FOR SERVER AUDIT [Audit_001] ADD (FAILED_LOGIN_GROUP), ADD (SERVER_OPERATION_GROUP) WITH (STATE = ON) GO |
Database Audit Specifications
Similarly, Database Audit specifications can be created SQL Server Management Studio from the following image.
Database Specifications can be created for SELECT, UPDATE, INSERT, DELETE, EXECUTE, REFERENCES event. A legacy auditing technique is to create triggers to track UPDATE, INSERT, DELETE operations
As many of you have experienced, triggers can create issues with maintainability and performance. Therefore, Database Audit Specifications can be used instead of triggers.
Similar to the Server Audit Specification, Database Audit Specifications are also disabled by default and explicitly needs to be enabled.
Following are the T-SQL command which can be used to create Database Audit Specification.
|
1 2 3 4 5 6 7 |
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20171112-224945] FOR SERVER AUDIT [Audit_001] ADD (DELETE ON DATABASE::[AdventureWorks2012] BY [db_accessadmin]), ADD (SELECT ON DATABASE::[AdventureWorks2012] BY [db_accessadmin]), ADD (INSERT ON OBJECT::[dbo].[Const] BY [db_accessadmin]) WITH (STATE = ON) GO |
After audits are created, it can be viewed by right-clicking the Audit.
This viewer has the option of sorting and filtering. With the export option, you have the option of extracting this audit to the database using SQL Server Integration Service (SSIS).
References
- SQL Server Audit (Database Engine)
- Create a Server Audit and Database Audit Specification
- SQL Server Audit Records
View all posts by Dinesh Asanka
- Testing Type 2 Slowly Changing Dimensions in a Data Warehouse - May 30, 2022
- Incremental Data Extraction for ETL using Database Snapshots - January 10, 2022
- Use Replication to improve the ETL process in SQL Server - November 4, 2021