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:

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

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

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:

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:

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

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:

when you run the report, you receive an error:

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:

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