Dinesh Asanka

SQL Server auditing with Server and Database audit specifications

November 20, 2017 by

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.

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.

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.

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).

See more

For fault tolerant auditing with centralized storage and reporting, consider ApexSQL Audit, an enterprise level SQL Server auditing and compliance tool.

References


Dinesh Asanka

Dinesh Asanka

Dinesh Asanka is MVP for SQL Server Category for last 8 years. He has been working with SQL Server for more than 15 years, written articles and coauthored books. He is a presenter at various user groups and universities. He is always available to learn and share his knowledge.

View all posts by Dinesh Asanka
Dinesh Asanka
2,955 Views