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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters
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.
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.
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:
Tonight I spoke at the North Boston Azure group and covering serverless concepts by looking at Logic Apps, Function Apps, and a couple of powerful/easy features of Web Apps (authentication via AAD/Twitter & CD via Github).
Before I posted the slides (see below), I needed to clean up the 1000+ emails I spammed myself with from my Logic App demo. (Doh!)
I did not have time to show it, but the Slack => Email process did succeed. I saw this later when I checked my email. Subject: bill-tux-full.png, Body: 1504739903F6YKE996C bill-tux-full.png bill-tux-full.png. And you can see the photo for yourself in the Boston Azure slack #demo channel.