Auditing Features in SQL Server 2016 and Azure SQL Database

Auditing Features in SQL Server 2016 and Azure SQL Database

SQL Server auditing features allow you to track and log both server level events, as well as individual database events. It involves tracking and logging of events that occur on the Database Engine. You have options to write the audit logs either into event logs or to individual audit files. Both SQL Server 2016 and Azure SQL Database have introduced new security features that I blogged about earlier. In this post, however, I am going to focus on auditing features in SQL Server 2016 and Azure SQL Database.

Behind the scenes, SQL Server audit leverages Extended Events to help create and run audit related events. It is key to SQL Server Standard deployments, which does not have “Fine-Grained Security” out of the box. You can manually create those Audit events using extended events and track various database or server level changes that you want to follow.

SQL Server audit comprises of several audit components. These are audit objects which defines the scope and the target. On a high level, these are the components:

• SQL Server Audit Object: Container that holds a single audit specification for either server or database level audits. You can define multiple Server Audits which can run simultaneously.
• SQL Server Audit Specifications: This tracks server level audits and invokes the necessary extended events as defined by the user. You can specify only one server audit per audit (container)
• SQL Server Database Audit Specifications: This object also comes under the server audit. User-defined database level events are tracked and logged. There are predefined templates which you can use to define a database audit.
• The Target: All the server objects with the server or database audit specifications are either sent to a file or the event logs (Security & Application)

SQL Server 2016 Audit Creation Process

You can either use management studio or use T-SQL scripts to create and target auditing. On a more simplistic level, you can define a database audit using the following steps:

• The first step is to develop & define an audit and the target.
• Based on user requirements, you can either create a server or database audit specification that directly maps to the audit container.
• By default, audits are disabled. You need to enable the audit specification after creation.
• Based on the defined target, you can read the audit events by using the Windows Event Viewer or Log File Viewer MMC.

Fine-Grained Auditing in SQL Server Standard Edition

As you can see from the above table, fine-grained security is not available on the Standard Edition of SQL Server 2016. However, the audit’s internal engine is run by Extended Events which is also available on SQL Server Standard. They are hundreds of events that can be tracked and not all of them are documented. You can define the events that you would like to track to get the desired results.

CREATE EVENT SESSION [Database-Tracking] ON SERVER 
ADD EVENT sqlserver.databases_log_cache_hit(
ACTION(sqlserver.database_id,sqlserver.database_name)),
ADD EVENT sqlserver.databases_log_growth(
ACTION(sqlserver.database_id,sqlserver.database_name))
WITH (STARTUP_STATE=OFF)
GO

Auditing in Azure SQL Database

Auditing in Azure SQL DB is supported across all service tiers, i.e. Basic, Standard and Premium. The audit data actually goes into a table and the data is retained based on the user defined “Retention period“. See below:

You can connect using Excel or Power BI to export or visualize your Audit Data information. Alternatively, you can download and use the Azure Storage Explorer to open and import logs. See below:

Conclusion

You will not be able to fine-tune or import more granular level events on Azure SQL Database, although the existing information should be sufficient for most users. You can find more detailed information on this MSDN blog post.