Azure SQL Database - Auditing Walkthrough

I enjoyed writing my post on the new Dynamic Data Masking feature in Azure SQL Database so much that I thought I'd take a look at another recently added feature - Auditing.

Getting setup

I'm going to use the same AdventureWorks sample database from last post for this one too.

Firstly, I'm going to initiate some changes at the actual Server level which individual Databases can inherit. So I'm going to open the Database Sever and switch to the Auditing & Security tab as shown below.

On this page I'm going to make the following settings and select a storage account (which I've blanked out below). The Auditing feature utilises Azure Table Storage which is a great choice as it (theoretically) supports up to 500TB of storage per Subscription and as Troy Hunt blogged, easily handles millions of rows.

Note: while here make sure to download the report template (an Excel workbook) which we'll use later.

Now that we have the server configured we'll need to make a few minor tweaks at the Database level too. I am going to disable the use of unsecured connections to this database by flipping the following setting on the Database's Auditing & Security tab and then saving.

Testing it out

For this testing I'm going to use SQL Server Management Studio again... so let's login...

D'oh! I forgot - I'm doing this on a 4G connection and the source IP I'm at isn't in the firewall for the Database so my login failed. Let's go and fix that and then try again. We'll check later to see if that failure was logged or not.

I'm going to start by using the free Azure Storage Explorer to allow me to view the raw data in the Azure Storage Table into which my Azure SQL audit logs are being written. A sample is shown below.

This is all very nice, but as you can see this will quickly become difficult to manage and roll-up reporting will be a challenge. This is where the spreadsheet we downloaded comes into play and we can leverage the Power Query to provide useful reporting.

If you open the spreadsheet you will see that it has instructions on how to set it up (hint: I'm not going to cover those again here).

After you have updated the Power Query data source to point to your Audit Logs you have a dynamic workbook tied to your logs which will support slicing and dicing of your audit data (and that you can publish to a PowerBI dashboard if you so chose). Note this isn't a one-time data sample - I can refresh my data as required as long as I have an Internet connection. A sample from the Excel workbook is shown below that highlights the Login Failure information on my server.

As with the other recently announce Azure SQL Database features such as Dynamic Data Masking, this offering is very much a "v1" implementation. Expect to see changes and increasing in depth of this offering over time because if anyone needs more information it's the guys in the tinfoil hats!