How to Perform Vulnerability Scan on MS-SQL Databases.

SQL Vulnerability Assessment is a new feature embedded in some of the most recent versions of SQL Server Management Studio (SSMS).

This feature is relatively very easy to use and it will show you all the security vulnerabilities and anomalies in your SQL database. Following best practice, it is recommended to apply strict security practices thereby ensuring that client’s data are not compromised.

If you currently do not have SQL Server Management Studio (SSMS) installed on your PC or Server, kindly follow this link to have it downloaded. https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

Click on Download SSMS as shown in the link above.

In this example, I will be performing Vulnerability Assessment of one of my databases named “SolarWindOrion” as shown below.
Note: Orion is a performance management and fault management application that allows you to view the real-time statistics of your network directly from your web browser. This Db is actually used for this task in my laboratory and I wish to remediate and protect the Database.

-Right-click on the database "SolarWindOrion" in SSMS, 
- Navigate to “Tasks”, 
- Then to “Vulnerability Assessment” and 
- Click on “Scan for Vulnerabilities” as shown below

Select the right location where the report will be saved as shown below.

Now Click on Okay and This will execute and prompte any found vulnerability below. See associated errors from my scans below.

Now you will be able to view the scan reports in SSMS. The details of the performed security checks such as failed checks and other information are available in a readable format.

Vulnerability Scan Exported to Excel

Click on one of the errors displayed. Let’s click on the first error as shown above. See the new image of the detailed checks below.

Since these details are self-explanatory, I will proceed and attached a new image showing the fix (remediation) as suggested by Microsoft in order to fix this issue.

Note: The Suggested remediation can be opened in the Querry Editor on a new SQL Querry Window and executed as shown below. In this way, we can have our database protected as suggested by Microsoft (Baseline)

Note: It is recommended to review the scan report, perform the necessary actions and run the scan again to ensure that all security risks are mitigated.

Differences between various Editions of MSSQL Server

When asked to run projects that utilize MSSQL as the database engine with the least cost, then you should be able to select from the various Editions of MSSQL that serves your need. This decision is based on your environment and what the project (task entails). See the following explanations below for the right Edition to use.

Five editions of MSSQL

  • Express: This is the most basic of all SQL Server editions. It’s free to use in production, which makes it the best choice for independent software vendors, whose clients can’t afford the cost of a SQL Server license.
  • Developer: This edition has all the features of the Enterprise edition, but cannot be used in production environments.
  • Web: This edition is between the Standard and Express editions. It has features that Web hosting companies and Web VAPs can offer their customers to provide scalability, affordability, and manageability capabilities for small to large scale web properties.
  • Enterprise: This is the most complete edition of all. With this edition, you have unlimited virtualization and high service levels for the mission-critical workload.
  • Standard: This edition enables database management with minimal IT resources.

Possible MS SQL Errors

Error 1: Cannot connect to the localhost, connection timed out.

Solution:
Simply increase the timeout error in the MS Management Studio connection settings (increase from 30 t0 120 and remember to turn this back again to the default after the first connection). Fix the initial timeout connection and this can be changed afterward. Increased connection timeout to 120.

Error 2: Cannot connect “An exciting connection was forcefully closed by the remote host Sql Exception with error number 10054

Solution: Try to identify and understand your environment in order to resolve this issue.

  • This error clearly shows that the problem is not the firewall or the remote connections, but the login process.
This was not resolved because connection seems to work but the authentication method I tried to use is not supported by the database instances and therefore did not work. I was trying to connect using Domain Account and this was not supported.