Data Classification in SQL Server 2019

Steve Jones, 2018-11-09 (first published: 2018-10-24)

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:

  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:


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.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis


1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren


1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren


360 reads