SQL Server Vulnerability Assessment using SSMS

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…

Database Vulnerability Assessment 1

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.

Database Vulnerability Assessment 2

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.

Database Vulnerability Assessment 3

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:

Database Vulnerability Assessment 4

Database Vulnerability Assessment 5

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.

Database Vulnerability Assessment 6

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.

Database Vulnerability Assessment 7

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published. Required fields are marked *