SQLStarters

One of these features, and the topic of this post, is Azure Defender for SQL. While Azure Defender has been around for a while, it was only very recently it was also made available for SQL Servers running on VMs.

So what is Azure Defender for SQL and why you should be enabling this today, if you already haven’t?

So what does it do exactly?

Glad you asked! Azure Defender for SQL does two things. It identifies and reports about potential database vulnerabilities and detects, in real-time, anomalous activities that could indicate that your databases are threatened by bad actors. Like always, there’s a small price to pay for the added protection, some of it in a form of currency and some in extra compute. As for the costs, in both cases, I think they’re quite negligible compared to benefits.

How do I enable it on my SQL Server VM?

There are few requirements for the Azure Defender for SQL that need to be fulfilled before it can be enabled.

1. SQL IaaS Agent extension needs to be installed. This is highly recommended, even if you’re not using Azure Defender for some other, nice benefits. It’s also possible to enable automatic registration at subscription level these days, to keep this part easy.
2. You also need to install the Log Analytics agent to your VM
3. And finally, you also need to enable the optional plan in Security Center.

Let’s get started with Security Center

In my own test environment, I have already enabled the optional plan in Security Center, and this is what I see in the Overview.

A single subscription and already some assessed resource and recommendations. Before I go into these, let’s look at the subscription and what you can do in there.

If you hit the Edit Plan button, you can actually decide what you want to protect with more details. I really don’t know why you’d leave any of these outside, but maybe you have reasons… Or do you, really?

Let’s look at my VM status

So it looks like that my VM is in less than perfect shape. However, as I have just enabled all this nice security features, this probably means there’s still some additional actions that need to be performed.

And indeed, I can already see that the Log Analytics agent is not properly installed. As I mentioned earlier, Log Analytics is a requirement for the Azure SQL Defender, so this should be fixed first.

However, in this case, there’s not a lot to fix. The problem is that the automatic Log Analytics agent is still pending the automated installation. Looks like it’s a good idea to take a break from the writing, and come back to this later.

Do we now have some real problems?

I don’t know exactly how long does it take for the installation to happen, as that information wasn’t tracked in the Activity log. However, the Log Analytics agent was properly installed sometime overnight, and now I am seeing the issues I was expecting to see. You can get to these in a couple different ways, when you’re interested only on a single box it’s usually just easiest to get there through the VM itself. After running this only for a couple of days, I am already seeing “Suspected brute force attacks”. If this was a production system, I’d definitely be rather worried at this point.

On the other hand, if this was a production system I wouldn’t have opened RDP or SQL Server ports towards the Internet. And no one should, that’s just an invitation to get hacked.

External threats aside, what can we learn about the databases? Let’s click on the “SQL Servers on machines should have vulnerability findings resolved” and find out. This view I really like, you get all the security findings for the server.

And you can then filter by selecting one of the databases, in my case MigrationSourceDB.

And best of all, when you click any of these findings, you’ll get a detailed explanation in what the issue is and how to detect and remediate it. In my opinion, this is just perfect.

Understanding the baseline

One thing to keep in mind is, that when you first enable to solution, it’s going to look absolutely horrible. Soooo much red that it’s going to hurt your eyes just to look at it. But not everything that gets flagged is a problem that needs to be fixed, in many cases they are things that you just need to be aware of. In these cases, you should review the findings, and add them to the baseline.

A good example of this, is the ‘dbo’ user account. Depending on how database ownership and separation of duties is handled by the organization, it can be rather normal to have in this role some user account that is not a sysadmin in the instance. Of course, your applications shouldn’t be using that login, but someone who would actually need it for administering the database.

While creating the baseline takes work, the beauty of the baseline is, that it gets stored. So once you’ve done the work to set it up, you can use it to monitor the databases and to detect if they are drifting away from the baseline. It is very nice to be aware of things like someone changing your users, database owners, or permissions as these could be signs of something sinister happening in the database/instance.