Data classification with Microsoft SQL Server and Azure SQL DB (GDPR, PII, HIPPA...etc)

On-premise or IaaS SQL Server

• The Data Classification feature is available for SQL Server 2008 +
• The Data Classification feature requires SQL Server Management Studio (SSMS) 17.5 +

1. In order to access the Data Classification feature, we need to open the database section and right click on the database we want to use. Then click Tasks and Classify Data.

2. Next, it will display a blank report as we have not yet determined the classification of our columns. In order to do this, Microsoft attempts to discover and classify the columns on our behalf. We MUST review the recommendations and confirm the classification of the columns. We can do this by clicking on this message on top of the screen. We can then go through each column to check that the information type and sensitivity level is correct. Once we are happy with classification, we can click Accept selected recommendation button. Once that is complete, we just need to click the Save button.

3. Alternatively, we can classify columns manually by clicking on the Add Classification button and filling in the details as required.

4. Once we have accepted and saved the recommendations, we have successfully classified our data. We can view a nice report by clicking on the View Report Button. The report gives us some summary information regarding how many classified columns we have, how many tables we have that hold sensitive data and how many distinct information types we have. The report also gives us some attractive graphs to display the data. If needed, we can drill down and find column information in more detail towards the bottom of the report. We can also right click on the report and export to PDF, Word or Excel, which is super useful for presenting to executives.

Azure SQL DB

1. First, we need to enable the Advanced Threat Protection for the database server. Please be aware there is a cost associated with this but it does offer a few benefits such as data discovery and classification, vulnerability assessment and threat detection capabilities. More information can be found here. We can enable this by clicking on the Enable Advanced Threat Detection button in the database blade of the Azure portal.

2. Next, we need to click on the Data Discovery and Classification blade.

3. Similar to SSMS, we must review and save the recommendations by clicking on the recommendation bar at the top of the blade.

4. Now we can review, make any changes and accept the recommendations as required. After accepting the recommendation, we will need to save the classifications using the Save button.

5. Alternatively, we can add our own classifications by clicking on the Add Classification button.

6. After clicking on Save, the report is ready to be viewed and can be accessed by clicking on the Overview tab. It can also be exported using the Export button.

Conclusion