GDPR: Data Classification for on-premise SQL databases

We’ve talked some days ago about the new Azure SQL feature (actually in preview) regarding data discovery and classification, in my opinion very useful for a first assessment of your databases for the new GDPR regulation.

When talking about that, I’ve said that this feature is also available for SQL Server On Premise and many of you have asked me where to find this feature (they was not able to find it).

To have Data Discovery and Classification feature (and also SQL Vulnerability Assessment) for your on-premise SQL Server installation, you need to have the latest version of SQL Server Management Studio (now 17.5) or you will not be able to find this feature. You can download the latest SSMS version here and remember that the SSMS 17.x installation does not upgrade or replace SSMS versions 16.x or earlier. SSMS 17.x installs side by side with previous versions so both versions are available for use. If a computer contains side by side installations of SSMS, verify you start the correct version for your specific needs.

After installing SSMS 17.5 (or later) open it and connect to your SQL Server instance. Then, select the database that you want to inspect (here I’ve selected one of my NAV databases), right click on it and select Tasks –> Classify Data:

SSMSDataClass_01

Now the new Data Classification engine scans your database for columns that contains potentially sensitive informations:

SSMSDataClass_02

When the scan is completed, the engine provides you a list of recommended column classifications:

SSMSDataClass_03.jpg

As in Azure SQL, you can accept all recommendations or you can accept a recommendation for a specific column. You can also change a recommended classification by changing the Information Type and Sensitivity Label:

SSMSDataClass_04

Then click Accept selected recommendations to apply the data classification.

You can also create a manual classification by clicking the Add Classification button in the toolbar. After that, select the table and column that you want to manually classify and click Add Classification:

SSMSDataClass_05

To save your data classification (persistently tag the tables columns) click the Save button in the toolbar:

SSMSDataClass_06

SSMSDataClass_07

SSMSDataClass_08.jpg

Last step (but useful for GDPR): click on View Report and the system will generate a printable SQL Data Classification report. Regarding that, I’ve found what I think it’s a problem that must be solved:

If you have a NAV database with a case sensitive collation:

SSMSDataClass_10.jpg

when you run the report, you receive an error:

SSMSDataClass_11.jpg

I think that this error occours because the report has an embedded query like this:

select * from information_schema.columns

while on a CASE SENSITIVE collation, it must be:

select * from INFORMATION_SCHEMA.COLUMNS

I’ve yet reported the problem to Microsoft.

To answer another question I’ve received: where is the data classification stored? I’ve not see an official documentaton by Microsoft for this, but I’ve discovered that the column classifications are stored in the sys.extended_properties catalog view:

SSMSDataClass_09.jpg

In a period where GDPR is an hot topic, I recommend to launch a Data Classification for your NAV database and (as a first basic step) create a report for your applyed classification. In this way you can demonstrate to your customer that “you know where sensitive data are”.

2 Comments

  1. Hi,

    A best practices question:

    Would you mark every column with a classification, no matter the sensitivity of the data?

    For example by simply marking every column with information type / sensitivity label “Other” / “General”?

    This way it could be as easy as simply classifying the non-classified columns.

    Any thoughts?

    Cheers,

    Peter

    Like

    1. Yes, a best practice is marking every field with the appropriate classification. A possibility could be use the Wizard and from that modify the very sensitive fields.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s