Lately, we’ve had an interesting conversation on how could developers begin to sort of categorize various data stored within SQL databases. As our customer is located in a EU country, they are deeply concerned and have to strictly comply with GDPR, along with a custom security pattern that combines ISO27001 and PCI DSS.
Understanding your data across numerous databases can be a challenging and demanding process, especially when there are multiple developers working on the same thing.
At this point, we were fortunate enough to just having completed a big migration project, having migrated all customer’s SQL Servers, from 2008R2 to 2017/2019.
What we proposed, was to use SSMS (v.18.5.1) in order developers to begin classifying data through Object Explorer as shown in the screenshots below:
From Object Explorer, select the database and right-click on it. Then, navigate to
Tasks > Data Discovery and Classification > Classify Data...
Microsoft has done quite an admirable and extensive work there, adding options to Set IPP, export or reset Information Policy, even generate a report (you can generate a report through Data Classification page as well).
By Selecting, Classify Data, there is a good chance that you’ll face this empty screen, with a blue indication on top, suggesting you a number of columns that subject to Information Classification. By click this, you’ll get to the following screen, or, if you are advanced on this, simple click on Add Classification to begin adding your own specific categories.
At this point, you can start categorizing your data into Information Types and Sensitivity Levels.
You can generate a report, at any point (mentioned few paragraphs above), to check on your work done or just keep a copy after you are complete.
This is the main form of the report (of course there is no data here as I have selected no columns to classify due to customer’s data protection), but you will be getting a good amount of information over the time.
Data classification is an ongoing process, meaning that developers or whoever is assigned to this job has to keep updating this as databases expand.
Important Notice regarding security:
By using the SQL Data Discovery & Classification tool, security concerns could potentially arise so it is important to follow the below recommended steps, in order to mitigate any issue:
- Monitor access to the catalog view “sys[.]sensitivity_classifications, which has the location of the sensitive data.
- Monitor executions of SQL statement “Drop Sensitivity Classification,” which deletes the classification label.
- Verify that only authorized accounts can execute the SQL statement “Drop Sensitivity Classification.”