AzureEnterprise TechSQLAuditing Features in SQL Server 2016 and Azure SQL Database July 8, 20166351 views0Share By IG ShareTable of ContentsNote: If you buy something from our links, we might earn a commission. See our disclosure statement. ToggleAuditing Features in SQL Server 2016 and Azure SQL DatabaseSQL Server 2016 Audit Creation ProcessFine-Grained Auditing in SQL Server Standard EditionAuditing in Azure SQL DatabaseConclusionAuditing Features in SQL Server 2016 and Azure SQL DatabaseSQL 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 ProcessYou 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. Create a New Audit Select “Target” Define Server or DB Spec Select Events to TrackFine-Grained Auditing in SQL Server Standard EditionAs 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 XE Session Event Name Select Events Define TargetCREATE 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) GOAuditing in Azure SQL DatabaseAuditing 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: Export to Excel Export to Power BIConclusionYou 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. Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0
Enterprise TechList of the Best Threadripper Pro Workstations – W-3100 Xeon W-3200 & W-1200 Alternatives By IGMay 25, 2021
AzureBest Industrial IoT Routers & Gateways For AWS and Azure IoT ServicesIn this article, we are going to list out the Best Industrial IoT Routers for professional ...
AzureDownload Backup and Restore a Azure SQL Database Locally for TestingAzure SQL Database is Microsoft’s Database as a service on the cloud. While your production ...
AzureStep by Step Deployment for SQL Analysis Services PAAS on Azure CloudSQL Server Analysis Services (Both Multi-Dimensional and Tabular) only supports Windows Authentication. However, Analysis Services ...
AzureMove & Load Data to Azure SQL DataWarehouse using Data Platform StudioThere are multiple ways to populate data into Azure SQL DataWarehouse PAAS (Platform as a ...