SQL Server Security: Are your databases ready for the GDPR?

The GDRP (General Data Protection Regulation), voted in 2016, will be applied in the European Union soon (May 25, 2018). We heard a lot of this new directive.
The goal is to improve the protection and confidentiality of personally identifiable information for every European citizen. Quid of Switzerland? Read the article of Gregory here.
A personal data is information that identifies a natural person, directly or indirectly. It can be a name, a photograph, an IP address, a phone number, a computer login, a mailing address, a fingerprint, a voice recording, social security number, email, etc. Some data are sensitive as they relate to information that may give rise to discrimination or prejudice.

Microsoft reacts with a Guide to enhancing privacy and addressing GDPR requirements with the Microsoft SQL platform and gives to the DBA a new feature with the latest version of SSMS (SQL Server Management Studio) 17: SQL Data Discovery and Classification.
This feature exists on SSMS since the version 17.5. I have installed the version 17.6 for this article.
Once SSMS updated, just choose the database you want to scan, right click on the database and select Task, Classify Data…:

After few seconds, you have the result of the scan and in my case 39 columns with classification recommendations:

Click on this result to see the detail:

For each column, you will see an Information Type and a Sensitivity Label.
The Information Type gives a better granularity of the type of data:

The Sensitivity label is to have the level of sensitivity of the data:

As you can see, you have 2 level for GDPR: Confidential and Highly Confidential.
You can identify some information like for example the personal phone number and change from Confidential – GDPR to Highly Confidential – GDPR:

After reviewed all columns and change the information Type and Sensitivity Label if needed, you can check all cases or only select few columns and click on “Accept selected recommendations”:

After that save the result. Without saving the result, you cannot see the report…
If you want, you have also the possibility to add manually a classification through the button “Add Classification”.
The message “The classification changes have been updated successfully”:

At the end you can click on View Report to have a global view and see with the application owner how to protect sensitive columns with features like Dynamic Data masking, Always Encrypted or a third-part tool.

If I go to a classified column in the objects explorer, a right-click and select properties, I can see in the Extended Properties the information about the information type and sensitivity label:

As you can see the information is directly written to the column properties (Action of the save button).

Like for Vulnerability Assessment, you need to run it database per database and you don’t have the possibility to scan in one  click all databases from an instance. It is also not possible to script it… Perhaps in the next verison! 😉

It’s also available on Azure, click here for more information.