SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Azure DWH part 25: Auditing and threat detection

By Daniel Calbimonte,

Introduction

Auditing allows to store database activities in the audit log in the Azure Storage Account. This feature is simpler than the SQL Profiler or the extended events. In this example we will connect and check the Auditing and threat detection option using Azure Portal and Excel.

Requirements

  1. An Azure Account 
  2. An Azure SQL Data Warehouse (ASDW) Installed

Getting Started

In the Azure Portal, go to databases. Click the ASDW database and select Auditing & Threat Detection. Change the Auditing option to ON and the threat and detection also to ON:

Threat detection costs $15 per month, but it is free for 60 days:

The audit log is stored in an Azure Storage Account. You can configure the number of days to retain the log. The first time, you will need to create the storage account with the option Create new. You can have a primary and secondary access key and it is possible to modify the name of the table where the log will be stored:

Once created the audit logs and threat detections, open the database using the data explorer or any tool of your preference to generate activity in the database:

Try to login using a wrong password and then login with the correct password:

Return to threat detection and configure the option to Send alerts to a specific email:

Open the option Audit Records. You can check all the database activity:

Press the filter icon. YOU can specify the end time of the events to see:

We will now try the Open in Excel option for audit records:

An Excel file named Azure SQL DB Audit Logs Report Template will be downloaded. The Excel will ask for your account key:

You can find the account key in the Azure Portal, in Storage accounts in the Access Keys section:

Copy the access key to Excel and press connect. Once connected, the Excel file will ask for privacy levels for the current workbook and the Azure URL:

You can optimize the performance in Data and selecting New Query. In new query, select Query Options:

In Query Options select Ignore Privacy Levels and potentially improve performance:

You will need to go to Data and select the Option Refresh All:

You can now check and filter the Audit log in Excel with all the Advanced Excel features:

Conclusion

ASDW offers and Audit and threat detection. You need to enable this option which is by default OFF. The information is created in a blob storage account. You can view the information in Azure using a Web Browser, but the filters are not so good. You can save the audit log and view the information using Excel. MS Excel provides great feature to filter and search all the Audit entries.

References

 
Total article views: 223 | Views in the last 30 days: 4
 
Related Articles
FORUM

Database Options

Database Options

FORUM

Auditing Account creation/changes

Trying to audit account creation and permission changes

ARTICLE

Easy Auditing a Shared Account

Despite the major advances made with Profiler in SQL Server 2005, auditing changes isn't one of the ...

BLOG

Blob Auditing for Azure SQL Database

In February 2017, Microsoft announced the general availability of Blob Auditing for Azure SQL Databa...

FORUM

Database Option

Checking for database option in Mgt Studio

Tags
audit    
azure    
data    
sql    
threat    
warehouse    
 
Contribute