SQL 2019-Data Classification and Vulnerability assessment

Data Classification

With GDPR security has become a high priority for a large number of organizations the cost of not implementing robust security has been significantly increased and therefore companies that previously used to think security wasn’t a key part of their architecture now has to revisit that approach. But complying with GDPR and all its nuances aren’t as easy especially for someone doing it the first time. This is where SQL server management studio shines. While technically not a new feature. Data classification and vulnerability assessment has always been available in Azure and now it is available within management studio for SQL server as well. This feature uses common Field names and data types to get an idea about the nature of the data within that column. Therefore being able to predict the security implications of that particular column. The features while not necessarily always accurate it does do the majority of the work for the developer/DBA significantly reducing the amount of time required to get started

In order to access this feature right click the database and select task as shown below:-

Upon clicking the button Classify Data you will see a new window pop up and SQL server management studio will automatically navigate through the schema metadata and identify columns that it thinks are relevant within the security context as shown below.

The DBA is expected to review the recommendations and accept or reject them as needed. He can also add additional classifications that may have been missed by the algorithm by clicking on the add classification button on the top.

  • Information type indicates the nature of information such as contact info, credentials etc.
  • Security level indicates the severity off the data on how important it is within the context of security

The DBA is provided with a helpful report that summarized the details of the classification in a dashboard format which is very useful when auditing the security landscape at a later point in time.

The DBA can manually add classifications that have been missed out as shown below:-

Vulnerability Assessment

The next thing we would need to discuss is vulnerability assessments. A feature that was introduced in Azure a long time ago. It allows the DBA to run a comprehensive scan over the infrastructure and identify security loopholes that he might have missed. It also verifies any best practices that need to be implemented considering the data being used. It not only identifies issues but often provide solutions for them as well. This makes the job of a dev op much easier as he doesn’t need to understand in detail all the different security infrastructure and implementation details that a traditional DBA had to be concerned with. Having said that the Assessment is a one size fits all solution for the most common security threats and you still need to go ahead and ensure you are monitoring and auditing the environment for potential new threats as and when the arise.

As before right click the database and go to tasks and choose vulnerability assessments.

AS with most easements a path needs to be provided for saving the results

After the test have been run you will see a report similar to the one below

In this case I have passed 53 and failed 3 Assessments of which one is a High Risk assessment. Further details and solution is available on clicking the assesment as shown below:-

You can also ignore certain warning if you feel they are part of the security baseline for the environment. For example DEV and Test Servers do not need as high security policies as Prod server. This can be achieved by clicking the buttons above the assessment.

As a best practice it is recommended to run these reports at least once in a year as part of due diligence and documentation if nothing else.

Please Consider Subscribing