SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Data Classification in SQL Server 2019

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.

2018-10-23 11_56_48-Data Classification - SimpleTalkDev_Steve - Microsoft SQL Server Management Stud

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:

2018-10-23 12_01_04-SQLQuery4.sql - Plato_SQL2019.SimpleTalkDev_Steve (PLATO_Steve (61))_ - Microsof

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.

2018-10-23 12_04_01-SQLQuery4.sql - Plato_SQL2019.SimpleTalkDev_Steve (PLATO_Steve (61))_ - Microsof

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.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...