Hello Friends,
As a Database professional, we all are aware about the importance of Data (Most Valuable Asset for a Company). There are lots of things we do to protect our data and to meet the compliance standards. Microsoft is also providing various new features since many years to help us on this. Some of them are like Transparent Data Encryption, Always Encrypted, Dynamic Data Masking, Row Level Security and TLS etc. Microsoft also added lots of new features in SQL Server Management Studio to help Database Professionals. One of them is [Classify Data….] that we learned in my previous blog post. If you want to know more about that feature then click here. Today we are going to learn about one of the most important tools that Microsoft has added in SSMS i.e. Vulnerability Assessment.
Vulnerability Assessment is a tool that we can use to identify vulnerabilities. This is a very easy to use tool. This will make Database Professionals life easier. Beauty of this tool is it also provides recommendations or fixes to remediate the identified vulnerability. In this blog post we will learn this practically. For using this tool you should have SSMS 17.4 or later. You can download latest version of SSMS from here.
Steps 1 – Open SSMS and connect any local or Remote SQL Server Database Engine. Right Click on database for which you want to perform vulnerability assessment -> Tasks -> Vulnerability Assessment -> Scan for Vulnerabilities…
Steps 2- This will open a scan for vulnerability pop up window. Here you can select the location where you want to save the assessment report. Then click on OK. Now Assessment will run. Please keep in Mind that this assessment is very light weight and it executes light weight read only queries.
Steps 3 – After scan, you will get Vulnerability Assessment Results report. Here you can get various details.
- Total Security Checks – How many total security checks run against the database.
- Total Failing Checks – How many of them failed.
- For Failed Checks you will also get the further classification details like how many of them are High Risk, Medium Risk or Low Risk.
- You will get two tabs – Failed and Passed. You can click on respective tab to get more detail about failed or passes rules. By default failed rules tab will be open.
Step 4 – To get more detail about any executed rule, click on that rule. You will get lot of information at the bottom as mention below:
Here, I have selected High Risk Security Check whose ID is VA1219. Just after the selection I got lot of details about that rule as shown in above image.
- Description – this will give you complete detail about the rule.
- Impact – This is really a very helpful section to get the impact details.
- Run Query – The query that is executed under this rule on target database.
- Actual Result – result of above query from target database.
- Remediation – This will explain how to resolve this.
- Remediation Script – TSQL code that you can execute to remediate this vulnerability.
Step 4: if you think, this rule is fine in your environment due to some business justification or something else. You can also approve that as a baseline.
If I’ll approve the above rule as “Approve as Baseline” then this will not be considered as failure. In New Vulnerability Scan it will be listed as “Per Custom Baseline”. You can notice the same in my new scan as shown below.
You can check all the rules and can take appropriate action as per your organization security policy or requirements.
You can also open any previously executed and saved scan using “Open Existing Scan…” as shown in top image.
HAPPY LEARNING!
Regards:
Prince Kumar Rastogi
Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook
