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
- An Azure Account
- 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.