As a product owner, you always worried about the different security aspects of your application and SQL Server Database is one of the main parts for which you might worry.
And you always think there should be some kind of checklist which you or your team have to check whether your database is secure or not and find all the vulnerabilities.
And obviously, you might have purchased different tools to this assessment as well which will provide you security loopholes but when we talk about Database, the option is limited and some options are very costly.
With SQL SERVER 2017 latest SQL Management Studio, one of your problems will be resolved cross-check your database vulnerability.
You heard it right. Although this feature is already available in SQL Azure, now you can do this assessment of your database using SQL Server 2017’s Management Studio.
This vulnerability assessment report can be generated on the database with few simple clicks and you will get different High, Medium, Low risks of your database.
The vulnerability assessment report not only provides risks details but also helps you to identify which category of it and this will not stop here. You will get a recommendation as well to fix those problems. Sometimes, you will get direct scripts which 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, make sure you have SQL Server 2017 Management Studio’s latest version.
Step 1
Once you opened 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 for the first time, click on Scan for Vulnerabilities option. and you will get the following screen where you can provide the location of scan file.
Step 3
Just click on OK button to proceed further and wow, you will get all the loop holes of your database.
You can easily check what are the different points on which your Database is failed with risk Assessment.
As shown in the above figure, we have 6 check points on which our database failed in which 1 is on high risk, 3 medium risk and 2 low risk.
And if you see carefully, there are different categories as well like data protection, Authentication and Authorization, Surface Area Reduction, etc.
Here as the name suggests, Data Protection is mostly related to encryption of your sensitive data like SSN, DOB, etc. or TDE.
Authentication and Authorization is more in relation to login access of 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 in the below image, when we click on data protection, it suggests the column names which come under extra care and to one which we might think of applying encryption.
Step 5
The story does not end here, for some of the problems, this assessment report provides 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.
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!
