Infrastructure at your Service

Stéphane Haby

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…:

gdpr_sql_01

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

gdpr_sql_02

Click on this result to see the detail:

gdpr_sql_03

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:

gdpr_sql_04

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

gdpr_sql_05

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:

gdpr_sql_06

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”:

gdpr_sql_07

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”:

gdpr_sql_09

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.

gdpr_sql_08

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:
gdpr_sql_10
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.

 

Leave a Reply

Stéphane Haby
Stéphane Haby

Delivery Manager