How to fix SQL vulnerabilities using baselines?

Question

How to fix SQL vulnerabilities using baselines?

Najam ul Saqib 400

I have many SQL vulnerabilities in my subscription but when I open them the remediation is very unclear and obscure. It says to add to baseline but I need to know where can I edit the security baseline and what needs to be added/removed from it for each particular issue. For example, "Database owners are as expected" gives two option, either to add all or remove all. User's image

What if I want to remove some or add some? I have read a lot on remediating these issues using baselines in Azure but couldn't get anything satisfactory. Remediation steps in Azure are very obscure as well unlike those in other recommendations that mentions proper steps.

Oury Ba-MSFT 20,906 Reputation points Microsoft Employee Moderator

2024-02-07T17:41:02.3066667+00:00

Najam ul Saqib Pease see https://learn.microsoft.com/en-us/answers/questions/1526302/how-to-fix-baseline-mismatch-in-microsoft-defender

Accepted answer

0 additional answers

Your answer

Oury Ba-MSFT 20,906 Reputation points Microsoft Employee Moderator

2024-02-07T17:41:02.3066667+00:00

Najam ul Saqib Pease see https://learn.microsoft.com/en-us/answers/questions/1526302/how-to-fix-baseline-mismatch-in-microsoft-defender

Answer 1

Timmy Malmgren 1,521

Hello Najam

Ill start with clarifying what these baseline are and what's their purpose with an example.
Lets say we have created a new database, we have added 3 users with some roles in the database, we have configured some firewall rules on the resource firewall of the SQL.

These will all be considered a vurnabillity since they are not part of your "baseline". What happens when you use the "add to baseline" is that Defender will save the current configuration (their are different baselines for firewall and user roles, but the idee is the same). Once this configuration is saved you will be compliant again with this in Defender.

The purpose of this is to ensure you have control over these setting, if someone add a new user/new firewall rule (after you added your current configuration to baseline), this new configuration will no longer match your baseline and it will show you what rule is not in baseline. Now you can easily evaluate if the new rule is correct, and if it is just add it to baseline.

Why it works like this is since Defender cant really know if a firewall rule or a User role is correctly configure, but it wants to you to be completely aware of these configurations and make a decision about them and also notify you when something is changed :)

Hope this is helpful,

Best Regards,

Timmy Malmgren

---If the Answer is helpful, please click "Accept Answer" and upvote it as it helps others to find what they are looking for faster!

Najam ul Saqib 400 Reputation points

2024-02-06T10:10:33.67+00:00

Where can I access these baselines or set them from portal?
Timmy Malmgren 1,521 Reputation points

2024-02-06T11:03:46.07+00:00

This is a little unpractical if you ask me, but since its a database scan and Query result you see each baseline under the specific vulnerability recommendation on each database. The query you se is the one listing the baseline and results.

VA2065: This is the active rule I'm looking at for ONE specific database atm.

Status: This is the status for each firewall Rule configured on that database (they correspond to the ones you see in the azure portal under "Networking" on your Azure SQL).

The results are collected from your database so if you click add all results to baseline they will have a status "In Baseline" and the green logo. If however they are not in your current baseline they will have a red X and "not in baseline".

You can also manage them with Powershell or JSON, here is a link where there are a few example for managing with JSON and Powershell

https://github.com/MicrosoftDocs/azure-docs/blob/main/articles/defender-for-cloud/sql-azure-vulnerability-assessment-manage.md
Najam ul Saqib 400 Reputation points

2024-02-06T11:40:38.6+00:00

I think I couldn't convey my question properly here, what I meant to ask is, where are these baselines set in Azure? Where do I need to go in portal (or somewhere else) and see what baselines are available in my database and how can I modify them?

Pressing "add/remove all results as baseline" is not a good solution here obviously
Timmy Malmgren 1,521 Reputation points

2024-02-06T13:06:00.5566667+00:00

The baseline is "blank" from the start, its not a baseline that Microsoft pushes to your database, its your own blank template that you manage for control over your database configuration.

Lets take the "VA2065 - Server-level firewall rules should be tracked and maintained at a strict minimum.

When you first setup a database this baseline for this server is empty, it contains nothing, so each firewall rule you add to that database will make you non compliant with this baseline. You yourself are the person adding the firewall rules to the baseline with the "Add all result as baseline" as your initial picture.

Lets say you have a new database "MyDatabase", the VA2065 is now empty.

You configure 3 firewall rules on the resource firewall of "MyDatabase".

When defender makes it next scan it will match the empty VA2065 template against what firewall rules are configured, in this case all 3 firewall rules will be marked as RED and not compliant, since non of them are in the baseline. You then can select add all results to baseline (you will se all 3 result status as RED X with "not in baseline" as in my image here

Newly created test database with 2 firewall rules and "AllowAllWindowsImages" setting enabled manually on the resource by me.

Remediation above shows you the remediation that you can execute in SQL to remove these entries. But If all configuration are correct you would like to add them to baseline since that will be your correct network configuration for this database.

Using the "Add all result as baseline" will add all the result in the list as your default configuration. If you don't add all result your database will never be compliant with defender for cloud. The purpose as i state is not that defender wants to push a baseline for network settings or user security rights to your database, it wants you to create your configuration and your own baseline :)

As to my knowledge you can only see the baselines you have configured with query, PowerShell or looking at the "Defender for cloud SQL" for each individual database.
Timmy Malmgren 1,521 Reputation points

2024-02-06T13:08:28.6866667+00:00

So for example this is where you find the server-level firewall rules "baseline" in the portal
Najam ul Saqib 400 Reputation points

2024-02-13T03:04:00.3766667+00:00

First of all, thank you @Timmy Malmgren for such a detailed response and apologies for late reply.

You have explained the above scenario in good detail, and things have started to clear out for me. What I understood from the firewall rules scenario is that you've to make changes to the database firewall using portal, and it will reflect in the defender, so there you can add it to baseline, so that if in future some other firewall rule is added it will appear as vulnerability and it wont match with the baseline. Makes sense! I am still not able to find out the existence of this baseline, obviously it is being stored somewhere in some form. How can we query it and see our baselines for particular database? It should be some file on SQL server.

The above mentioned scenario was good enough to be handled using Azure portal, but what if we want to remediate some issue that can't be handled directly from Azure portal e.g. "VA2108 - Minimal set of principals should be members of fixed high impact database roles", "VA1054 - Excessive permissions should not be granted to PUBLIC role on objects or columns", "VA1258 - Database owners are as expected", etc.
Timmy Malmgren 1,521 Reputation points

2024-02-18T18:01:43.52+00:00

Hi again, np :) sometimes life is busy ;)

So the rules themself and all configuration is stored inside the SQL database, so the query shown in each recommendation is a query to list it from inside the SQL database as shown below :)

However the baseline itself is located in the Log analytic workspace that the SQL is connected to as shown in the picture below.

You can also have it pointed at a storage account. To be honest I am not sure how to retrieve this information in another way, i tried some of the methods in the links here, but all are giving me kind of "obscure" errors like, "resource cant be found" or "results are not available yet".

https://learn.microsoft.com/en-us/powershell/module/az.security/get-azsecuritysqlvulnerabilityassessmentbaseline?view=azps-11.3.0

https://learn.microsoft.com/en-us/powershell/module/az.sql/get-azsqldatabasevulnerabilityassessmentrulebaseline?view=azps-11.3.0

https://learn.microsoft.com/en-us/rest/api/defenderforcloud/sql-vulnerability-assessment-baseline-rules/get?view=rest-defenderforcloud-2023-02-01-preview&tabs=HTTP

Another way to do this might be to create your baseline according to the link below and push them onto the "SQL:s" so you already have them exported somewhere.

https://learn.microsoft.com/en-us/rest/api/defenderforcloud/sql-vulnerability-assessment-baseline-rules/create-or-update?view=rest-defenderforcloud-2023-02-01-preview&tabs=HTTP#create-a-baseline

Besides this I am unfortunately kind of out of idees :/
Najam ul Saqib 400 Reputation points

2024-09-04T08:04:12.37+00:00

Thank you for your assistance.
Jacinta E 0 Reputation points

2025-04-08T02:39:59.7333333+00:00

Can you automatic this process so that you don't have to going through each individual dbs to add users to baseline? Thanks.
Oury Ba-MSFT 20,906 Reputation points Microsoft Employee Moderator

2025-06-20T21:53:01.1633333+00:00

Jacinta E Please do check if this is this something that can help you https://learn.microsoft.com/en-us/azure/defender-for-cloud/powershell-sample-vulnerability-assessment-baselines

Share via

How to fix SQL vulnerabilities using baselines?

0 additional answers

Your answer