Azure SQL Diagnostics & Event Hub

Auditing & Monitoring is one of the strong pillar in any SDLC process and in today's cloud arena it became even more important. In this article we would learn how to enable auditing capability in Azure SQL database and would also witness some LIVE stream of audit data. When it comes to auditing in Azure SQL, the platform gives you three choices for log destination. You can choose as per your requirement

 

• You can enable auditing either at the server level or at the database level. For this article we would go ahead and enable it at the database level. So, go to your Azure SQL database instance, select Auditing from the left pane under Security and enable auditing.
  

• We are going to use Event Hub for this article to stream all telemetry data from Azure SQL. So, go ahead and select that option and provide the required details. Make sure you have created your Event Hub resource and namespace beforehand in the same region where your Azure SQL service is running. Hit Save.

This will go ahead and add a new log category named "SQLSecurityAuditEvents" in the Azure SQL Diagnostic setting. This is the LIVE telemetry information you need to capture for all your SQL compliance and security audits. For example

1. What are all SQL statements getting executed and on which database, from which IP address those request came and at what datetime, how many rows got affected and much more.....
2. Successful logins 
3. Failed logins

 That's it. You are done with the required configuration.

Now lets run few SQL queries to generate some audit logs. I have used Azure Data Studio to connect and run the queries on my database but you can use any tool of your choice. Make sure you have whitelisted your IP to satisfy the firewall rules.

• Now there's no way you can view the logs by directly going into your Event Hub. So, inorder to view the logs we will take help of Azure Service Bus Explorer desktop application. So, open the explorer, connect to your event hub and fire up a listener to witness live feed the logs.

• Browse through the events and select one as per your requirement. Copy the EventText and view it in any JSON Format Viewer.  Your query and many other valuable information will show up in the logs. 

• Stop the Listener when done.

In real time scenario you won't be using Service Bus Explorer to view and monitor live events. There will be some Big Data analytic service sitting at the other side of Event Hub which will do all the big data processing, monitoring and reporting on the Event Hub logs data.

Hope this article will give you an understand on how to log and monitor telemetry data from Azure SQL.