Setting up SQL Server Audit feature

This article discusses the ways to enable and use the SQL Server Audit option to audit the SQL database.

Prerequisites

>

• Ensure that you have the Alter Any Server Audit or Control Server permission on to the relevant SQL Server instance to create, modify, delete, and enable server audit objects.
  • Granting ALTER ANY SERVER AUDIT permission
  USE master;

GO

GRANT ALTER ANY SERVER AUDIT TO AuditConfigurationLogin
  • Granting CONTROL SERVER permission
  USE master;

GO

GRANT CONTROL SERVER TO AuditConfigurationLogin

In the above queries, master refers to the master database that records all the system-level information for a SQL Server system. The GO command is not a Transact-SQL statement. It is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

• In order to configure database audit specifications, a user must have the ALTER ANY DATABASE AUDIT, ALTER, or CONTROL permissions on the audited database. (WideWorldImporters in this example)
  • Granting the ALTER ANY DATABASE AUDIT permission
  USE WideWorldImporters;

GO

GRANT ALTER ANY DATABASE AUDIT TO AuditConfigurationLogin
  • Granting the ALTER permission
  USE WideWorldImporters;

GO GRANT ALTER TO AuditConfiguration;
  • Granting the CONTROL permission
  USE WideWorldImporters;

GO

GRANT CONTROL TO AuditConfiguration;

How to configure the SQL Server Audit in SQL Server Management Studio

The SQL Server Audit feature can be set up using either T-SQL, or SQL Server Management Studio options. Here are the steps to configure the SQL Server Audit using SQL Server Management Studio.

• To create a SQL Server Audit object, expand the Security folder in Object Explorer.
• Expand the SQL Server Logs folder.
• Select New Audit.
• In the Create Audit dialog, specify the audit name, audit destination, and path.
  • Queue delay – sets the number of milliseconds before the audit information is processed into a target file. When the value is set to 0, the process is synchronous.
  • Maximum rollover files – the number of files kept in the system. When the maximum number is reached, the new files overwrite the old ones. The default value is unlimited.
  • Maximum files – the number of files kept in the system. When the maximum number is reached, the old files will not be overwritten, and new audit information will not be stored.
  • Maximum file size (MB) sets the size of the target file. When the specified size is reached, a new file is created. The default value is unlimited.

  An audit destination can be a file (*.sqlaudit file), security log, or an application log. You don't require any permission to write to a file or an application log. However, the Audit Object Access setting must be configured to record the events into a security log. The best way to do this varies based on the operating system that you're using

  • In Windows Vista and Windows Server 2008, you have to use the audit policy tool (auditpol.exe). The audit policy program exposes a variety of sub-policies settings in the audit object access category. The application generated settings must be configured to allow SQL Server to audit object access.
  • For earlier versions of Windows, the audit policy tool is not available. Use the security policy snap-in (secpol.msc) instead. When available, the audit policy is preferred because you can configure more granular settings.
  • Though the audit policy tool is preferable as it enables you to granularly configure the settings, it isn't available for earlier versions of Windows. Hence, it is recommended to use the security policy snap-in tool.

  You must have the generate security audits permission to write to the Windows Security log in the account that SQL Server is using. If the server is running under the LOCAL SERVICE and the NETWORK SERVICE accounts, they will have this permission by default.

• Right-click on the created audit and select Enable Audit

To modify an existing audit you must first disable the audit. For this, right-click the audit and select the Disable Audit. The changes will not be saved if the existing audit isn't disabled before modifying.

How to configure the SQL Server Audit using T-SQL

Execute the following query in T-SQL to create and enable the server audit.

CREATE SERVER AUDIT [WideWorldImportersAudit_DDL_Access] TO FILE
( FILEPATH = N'D:\TestAudits\'
,MAXSIZE = 10 MB
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT [WideWorldImportersAudit_DDL_Access]WITH (STATE = ON)
GO

Once the audit object and database audit specifications have been enabled, every single entry in the table will be audited and reported.

You can use T-SQL and SQL Server Management Studio options to configure and enable the SQL Server Audit and its components. Compared to the auditing done using the SQL Server Change Tracking and Change Data Capture, this provides results at the granular level. The events are divided into groups and only individual groups are audited.