SQL Tips - A Simple Way To Check Vulnerability Status Of Your SQL Server Database

Introduction

As a product owner, you are constantly worried about the different security aspects of your application, and the SQL Server Database is one of the main important parts about which you might worry.

And you always think there should be some checklist which you or your team have to check whether your database is secure and find all the vulnerabilities. And obviously, you might have purchased different tools for this assessment that will provide you with security loopholes. Still, when discussing Databases, the option is limited, and some options are very costly.

With SQL SERVER latest SQL Management Studio, one of the problems will be resolved by cross-checking your database vulnerability.

You heard it right. Although this feature is already available in SQL Azure, you can assess your database using SQL Server management studio.

This vulnerability assessment report can be generated on the database with a few simple clicks, and you will get different High, Medium, and Low risks of your database.

The vulnerability assessment report not only provides risk details but also helps you identify which category of it, which will not stop here. You will get a recommendation as well to fix those problems. Sometimes, you will get direct scripts that you can run to fix those issues, and sometimes you will get the links on how to implement those.

Let’s understand this by step-by-step action.

Before starting, ensure you have SQL Server Management Studio’s latest version.

Step 1

Once you open the SQL Server management studio right, click on the database which you want to cross-check. In this example, I am using the AdventureWorks database. As shown in the below figure.

Here you have 2 options either Scan for Vulnerabilities or Open Existing Scan.

Step 2

Now, as we are doing it the first time, click the Scan for Vulnerabilities option. And you will get the following screen where you can provide the location of the scan file.

Step 3

Just click on the OK button to proceed further; wow, you will see all the loopholes in your database.

You can quickly check different points on which your Database failed with risk Assessment.

As shown in the above figure, we have 6 checkpoints on which our database failed 1 is on high risk, 3 on medium risk, and 2 on Low risk.

And if you see carefully, there are different categories as well, like data protection, Authentication, Authorization, Surface Area Reduction, etc.

As the name suggests,, Data Protection is mostly related to the encryption of sensitive data like SSN, DOB, etc., or TDE.

Authentication and Authorization are more related to login access to the database.

Surface Area reduction is more related to what extra option you have opened.

Step 4

Now, move a step further and click on any row in the grid. You will find the details of the row just below the grid. As you can see below the image,, when we click on data protection,, it suggests the column names which come under extra care and which we might think to apply encryption.

Step 5

The story does not end here; for some of the problems, this assessment report provides a script as well, and if the script is not possible, then provide a reference link to resolve that issue.

As you can see in the below screen, we are getting recommendation scripts to apply.

Summary

Isn’t it cool and simple to assess your database’s vulnerability in a few clicks and secure your database?

Share your thoughts.

Happy learning!