Azure Vulnerability Assessments – SQLServerCentral

Regarding managing databases in the cloud, security should never be an afterthought. That’s where Azure Vulnerability Assessments, officially named Microsoft Defender, come in—they help you spot weak spots in your database configurations before they become real problems.

But do you always need them? Not necessarily. A vulnerability assessment might confirm what you already know. On the other hand, if you’re managing multiple environments or collaborating across teams, these tools can catch things you might miss, like forgotten test accounts or relaxed security rules.

They’re not a silver bullet, but can be a smart way to stay proactive. Ensure you understand what you are trying to achieve, not just blindly accept what they tell you.

How to configure

For more on how to set it up/configure it, this will guide you through setting it up for Azure SQL, Postgres, and other types of database resources. Where I work, it is enabled at the subscription level, so it’s set there rather than at the database level. Be aware that there is a cost for this, regardless of where you enable it.

How to find your recommendations

To see all your results, search for Microsoft Defender for Cloud in the Azure portal, which will bring you to a dashboard. Unfortunately, there are two places to look for issues. If you have Azure SQL and Azure PostgreSQL, it doesn’t seem like you can get them all in one place.

Recommendations for Azure SQL

Under General, there is a Recommendations option.

At this point, I will filter because I only care about the database resources. In this case, it will be a resource type of SQL database and SQL server.

You will naturally see your own resource names (mine are blacked out), but then you can click on the title to get more information. Once you click on the title, click “View recommendations for all resources.”

This brings you here.

And then you can click on the server’s name to get the findings, or minimize that and see the security checks.

Either way, if you go through the affected resources or the security checks, you wind up in a place where you get actual details on what you could do to remediate or add to a baseline so it can ignore it.

If you go in through the server name, you get a dashboard like this and can click through the issues on each database.

If you go through the security findings, you get a different view. It’s not a dashboard but a panel that comes up on the side with a query to get those results and a list of the resources that have this issue below.

I would probably pick it off by server anyway, as I would start in dev/QA and work my way up to the upper environments, so I favor the server dashboard method. With that in mind, I would work my way through each db. For each db, you will have a listing of issues.

Click on that issue and inspect it to see what you need to do to remediate it or add to the baseline.

I won’t remove everyone from every high-impact db role, but I might remove some and then add the others to the baseline. You need to think about what to do, not just add them all to the baseline or drop them from the role. The point is to think about what you need, not blindly follow what you find, and remove or add things to the baseline you may not need.

Recommendations for other data/database-related resources

I will go into Inventory instead for my Databricks, Azure Postgres, and some server-level stuff with Azure SQL. You can get some of the Azure SQL server-level stuff in that recommendations area, so if you don’t have any other database resources, you can do that there.

I did a resource type filter on Postgres Flex, Databricks, Azure SQL pools, database, and SQL Server. Then it filters and shows you something like this. I had to block out the names and scopes, but you will see yours listed there.

You could click on each one for more details. For example, here are some findings on one of my SQL servers, which is really for my Azure SQL databases.

I’m not going to do those, or maybe I will eventually. It remains to be seen. For example, I’m not using only AD auth; I will probably never change that. As for private endpoints, that’s a whole thing that will require more people than me. Private endpoints are a great idea, but it’s a lot of work, and you will have to use a VM “jumpbox” to get into your Azure SQL/PG databases after you set that up – just as a word of warning for those that might not know what it means for you as a data person.

Here’s an example of a finding for one of my Flex servers. Again, it may not be something you want to remediate; in that case, you could exempt it.

If you go into a specific recommendation, it will give you options for how to remediate it.

In the case of the “Azure SQL Database should have Azure Active Directory Only Authentication enabled,” the problem is that these things linger in your recommendations, lowering your score. Not that life is only about a number, but management has a goal of 85%, so there’s that. You can exempt it if you have perms. DevOps can most likely exempt it or grant you permission to do so.

Also note, if you look at Azure SQL databases in this inventory, they will show nothing to remediate, which isn’t necessarily true. That’s why I showed you the Recommendations area for the Azure SQL database recommendations; they live there.

Here’s an example. These are the recommendations for a specific Azure SQL db in the Recommendations page.

Then, in the Inventory page. Same database and same server. And they are all like that.

Thank you, Microsoft, for making this so NOT easy. At first, I thought I was going nuts because we were getting emails with recommendations, but you have to go to the recommendations page. I don’t know why they can’t be included in the recommendations for everything else in the inventory. It’s just like life: Nothing is terribly easy to deal with.

Querying recommendations with Azure Resource Graph Explorer

Maybe you don’t want to pick through all those dashboards and just want a listing of the recommendations for everything in one place. You can do this with the Azure Resource Graph Explorer. There are a couple of ways to access it. One way is from Microsoft Defender under Recommendations by clicking Open query. That will open a query with whatever is on the recommendations page, but I wouldn’t use that query.

The other way to get to Azure Resource Graph Explorer is to search for it in the portal and then go directly to it. In this case, there is no default query. I personally like this better because I want to use my own query. Here is the challenge with these assessments: the Azure SQL databases are in a subassessment, whereas the Azure SQL server, Postgres, and Databricks are in assessments.

Execute this to get a list of all subassessments for Azure SQL database that are available to you. This gives you an idea of what recommendations might be available, but not what is currently unhealthy.

securityresources
| where type =~ "microsoft.security/assessments/subassessments"
| extend assessmentKey = extract(@"(?i)providers/Microsoft.Security/assessments/([^/]*)", 1, id)
| extend resourceIdTemp = iff(properties.resourceDetails.id != "", properties.resourceDetails.id, extract("(.+)/providers/Microsoft.Security", 1, id))
| extend resourceType = tostring(split(resourceIdTemp, "/")[6])
| where resourceType in ('Microsoft.Sql') // remove this to see everything
| extend displayName = tostring(properties.displayName)
| summarize count() by assessmentKey, displayName, resourceType
| order by resourceType asc, displayName asc

Execute this to get a list of all assessment IDs for Postgres, Databricks, Cosmos, and Azure SQL server. Again, this is a list of what’s available to recommend, not what’s currently unhealthy.

securityresources
| where type =~ "microsoft.security/assessments"
| extend assessmentKey = extract(@"(?i)providers/Microsoft.Security/assessments/([^/]*)", 1, id)
| extend resourceId = iff(properties.resourceDetails.id != "", properties.resourceDetails.id, extract("(.+)/providers/Microsoft.Security", 1, id))
| extend resourceType = tostring(split(resourceId, "/")[6])
| where resourceType in~ ("Microsoft.Sql", "Microsoft.DBforPostgreSQL", "Microsoft.Databricks", "Microsoft.documentdb") // remove to see everything
| extend displayName = tostring(properties.displayName)
| summarize count() by assessmentKey, displayName, resourceType
| order by resourceType asc, displayName asc

Use this query for the subassessment recommendations. It gives you a list of the actual issues you need to address.

securityresources
| where type =~ "microsoft.security/assessments/subassessments"
| extend assessmentKey = extract(@"(?i)providers/Microsoft.Security/assessments/([^/]*)", 1, id),
         subAssessmentId = tostring(properties.id),
         parentResourceId = extract("(.+)/providers/Microsoft.Security", 1, id)
| extend resourceIdTemp = iff(properties.resourceDetails.id != "", properties.resourceDetails.id, parentResourceId)
| extend resourceId = iff(
    properties.resourceDetails.source =~ "OnPremiseSql",
    strcat(resourceIdTemp, "/servers/", properties.resourceDetails.serverName, "/databases/", properties.resourceDetails.databaseName),
    resourceIdTemp
)
| extend 
    serverName = extract(@"/servers/([^/]+)", 1, resourceId),
    databaseName = extract(@"/databases/([^/]+)", 1, resourceId)
| where assessmentKey =~ "82e20e14-edc5-4373-bfc4-f13121257c37"
| project resourceId,
          serverName,
          databaseName,
          assessmentKey,
          subAssessmentId,
          name = properties.displayName,
          description = properties.description,
          severity = properties.status.severity,
          status = properties.status.code,
          cause = properties.status.cause,
          category = properties.category,
          impact = properties.impact,
          remediation = properties.remediation,
          benchmarks = properties.additionalData.benchmarks

To get all your assessment recommendations, use this query. It’s the same as above, and it has the actual things you need to deal with.

securityresources
| where type =~ "microsoft.security/assessments"
| extend assessmentKey = extract(@"(?i)providers/Microsoft.Security/assessments/([^/]*)", 1, id)
| extend resourceId = iff(properties.resourceDetails.id != "", properties.resourceDetails.id, extract("(.+)/providers/Microsoft.Security", 1, id))
| extend resourceType = tostring(split(resourceId, "/")[6])
| where resourceType in~ ("Microsoft.Sql", "Microsoft.DBforPostgreSQL", "Microsoft.Databricks", "Microsoft.documentdb")
| extend displayName = tostring(properties.displayName)
| extend status = tostring(properties.status.code)
| where status == "Unhealthy"
| extend serverName = 
    case(
        resourceType == "Microsoft.Sql", extract(@"/servers/([^/]+)", 1, resourceId),
        resourceType == "microsoft.dbforpostgresql", extract(@"/flexibleservers/([^/]+)", 1, resourceId),
        resourceType == "microsoft.documentdb", extract(@"/databaseaccounts/([^/]+)", 1, resourceId),
        resourceType == "microsoft.databricks", extract(@"/workspaces/([^/]+)", 1, resourceId), 
        ""
    )
| project resourceType, serverName, displayName, status
| order by resourceType asc, displayName asc

You can’t remediate them here or get all the necessary details, but at least you have a list to work from to put it in a ticket. Also, you can click on See details in the results.

Getting recommendation alerts

You can get email alerts for scans and their checks.

You can follow the instructions here on how to do that. It’s not super dynamic and seems like an all-or-nothing proposition, so if you want just the database-related ones, that seems impossible.

I don’t get the email alerts because I don’t want to get them for all the other stuff. I periodically check for recommendations.

In short, Azure Vulnerability Assessments can be a valuable safety net—but like any tool, their value depends on how thoughtfully you use them. They work best with a solid understanding of your architecture and security goals. Remember: they highlight risks, but it’s up to you to decide what matters and what actions to take.

The post Azure Vulnerability Assessments appeared first on sqlkitty.