Ever try to figure out how to track who logged into your Azure SQL database? You checked all the usual ways you might handle that with a SQL Server database, but one-by-one find out they just don’t work. Here’s one way to do it.
To track who is logging into your Azure SQL database, enable auditing (here’s how to do that) with audit entries directed to an Azure storage blob. There are two ways to do this: at the database server level and at the individual database level. Either is fine, but for the example that follows, auditing is assumed to be at the db server level. The example query can be adjusted to work with auditing at the database level, but one of the two auditing options is definitely required to be on!
Run this query to find out all the principals (users) who have logged in so far today into your Azure SQL database.
The output is something like the following, assuming if I’ve logged in 12 times so far today with my AAD account (firstname.lastname@example.org) and 1 time with a database-specific credential (myadmin):
09-Nov-2019 (Saturday) email@example.com 12
09-Nov-2019 (Saturday) myadmin 1
The query might take a while time to run, depending on how much data you are traversing. In one of my test environments, it takes nearly 20 minutes. I am sure it is sensitive the amount of data you are logging, database activity, and maybe settings on your blob (not sure if premium storage is supported, but I’m not using it and didn’t test with it).
Note: There are other ways to accomplish this, but every way I know of requires use of Azure SQL auditing. In this post we pushed them to blobs, but other destinations are available. For example, you could send to Event Hubs for a more on-the-fly tracker.
At most recent Boston Azure meeting I give (what turns out to be…) the first part of a multi-part talk on Running Azure Securely. Even though I did not cover all this content, I’ve attached the whole powerpoint deck below.
Please watch for a Part II to be scheduled.
At the 29th Boston Code Camp, I spoke about Running Securely on Azure. Thanks to all of you who waited patiently for the A/V hookup challenges to be overcome.
Slides are here:
On Tuesday evening 27-Mar-2018 I had the pleasure of speaking to the Nashville Azure group about keeping workloads safe in the Azure cloud. Was a great group with a lot of interesting questions and dialog. They even helped to answer each others’ questions when I didn’t have answers, which is the best outcome of all.
For those interested in the deck I used, please find it below.
Spoke today to a small crowd of hardy soles who braved the snow to make it to SQL Saturday 694 – Providence – held at nearby Bryant University in Smithfield.
My slides are included below.
Azure SQL DB – Not Just A Cloud Version of SQL Server – SQL Saturday RI – 09-Dec-2017
We first looked at how we might solve a random StackOverflow question using Azure Logic Apps, Azure Function Apps, the nifty PhantomJsCloud.com service, and a look at how a little Cognitive Service action could be woven in. A random walk around other Azure features followed. Some reactions were memorable – my favorite, because I completely agree: Why would I ever want to run my own SharePoint instance when the Office 365 service is available? And a bunch of other good questions.
This past weekend I participated in SQL Saturday BI Boston in Burlington, MA. I spoke about Azure SQL Database, explaining why Azure SQL Database is much more than just a hosted version of SQL Server. The slides I presented are here:
If you are local, you may be interested in learning more about Azure by checking out @bostonazure and bostonazure.org. You can find me on twitter here: @codingoutloud.