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.
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:
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:
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(
ADD EVENT sqlserver.databases_log_growth(
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.
Comments are closed.