Category: Blog

One of the areas that Redgate is working on is making data classification easier. Microsoft added some capabilities to SSMS 17.5 and Redgate has an EAP out for the next version of our data catalog tool.

Azure SQL Database has had some advanced options they were building into the database engine, and we get our first look in the on-premises version with SQL Server 2019 CTP 2.0.

The ADD SENSITIVITY CLASSIFICATION and DROP SENSITIVITY CLASSIFICATION DDL is now available, and here are few examples of how this works.

Let’s look at a database that has some potential data to classify. I’ve got a sample database with a few tables. In fact, if I look at the data classification suggestions in SSMS, I see 7 columns.

I can accept any of these, but if I do, these are written to extended properties, which isn’t the best way of storing this data.

However, the ADD SENSITIVITY CLASSIFICATION syntax works well. If I take that dbo.Contacts.Email column and decide this is Confidential according to the GDPR, I can do this:

ADD SENSITIVITY CLASSIFICATION TO
  dbo.Contacts.Email
  WITH (LABEL = 'Confidential - GDPR')

If I then query my meta data table, I’ll see this:

There are other items I can add, such as the information type and then IDs for the label and type. I can, however, update that data like this:

ADD SENSITIVITY CLASSIFICATION TO
   dbo.Contacts.Email
   WITH (INFORMATION_TYPE = 'Contact', INFORMATION_TYPE_ID = '5BFAE3B8-4549-4989-BEB6-F9BF6434DAD1')

Note I still haven’t given the Label_ID a value, but that’s OK. This allows me to add human readable metadata to columns, as well as add IDs that I might get from some external auditing system.

This feels primitive, but it’s slightly better than extended properties, and it’s somewhat built into the engine, so we can code this as a part of development and ensure classification is added to our sensitive data.

If this is an area you’re interested in, we’d love to have you try the Redgate tool and give us feedback. We’re working on this problem and trying to find ways that are both useful and sustainable over time.