As a part of the robust security options provided by SQL Server, it also provides fine-grained auditing both on-premises and on the cloud. In this post, we would be exploring the new and enhanced auditing features in SQL Server 2016 and Azure SQL Database. Read along to know more.
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 track.
SQL Server audit comprises of several review components. These are audit objects which define 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 important extended events as specified by the user. You can define 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 identify 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 build & set 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 in the Standard Edition of SQL Server 2016. However, the audit’s internal engine is run by Extended Events which is also available for 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 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:
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.