Saltear al contenido principal

Finding sensitive data in SQL Server

ES: Encontrar información confidencial en SQL Server

From that moment we need to protect classified data in our databases, a new challenge begins. Not always we know where is that information but we need to separate and classify. SQL Server Management Studio comes with a great choice for finding sensitive data in SQL Server.

Where is your sensitive data?

First ask yourself (or ask whoever is in charge) «what do we call sensitive data?».

Some companies have (or they should have) guides to classify sensitive data which must be protected.

You, as a professional in charge of the data, your mission is give your life for it.

Why should I protect data?

Compliance. We need to ensure high levels of compliance and we need to have all our procedures and rules working based on national and/or international standards.

Every country has its own regulation and some of them are very strict and very hard to make everybody follow all the rules. You can take a look at HIPAA, SOX, GDPR, well, there are a lot others and also depend on the area you work. The best part is that Microsoft works with a lot of them and you can see it on that link.

And so, now we get Data Discovery and Classification

Microsoft didn’t want to leave us alone with this task and gives us this functionality starting with SQL Server Management Studio 17.5 that can be used from SQL Server 2012.

A useful help to discover, classify and tag our information inside our databases.

All from Management Studio

Select your database, right click, tasks and let’s begin with «Classify Data».


SSMS takes a look at what do you have in there and can give you some recommendations. Watch this example, nine columns that could have sensitive data were found.

If you click on the red highlighted area, you’ll see the results of this search.

I have again highlighted some of them for you to see that the SSMS itself tells you the Information Type that then you can tag.

As you can see at the top left section, you have a button to accept the SSMS’s recommendations after you choose from the list which of them you accept as correct.

In this case I chose three and I can still change the classification according to what I need.

Finding sensitive data in SQL Server is done. Is information protected?

It is not as easy as telling that after all this process my data is safe.

As the title of this post says, with this steps we «find» sensitive information. Maybe not in the way we expect or we wish but this is because Data Discovery has some weak points.

The most important here is that you know that it’s really important that you can tell where your data is, where is that sensitive data, secret, confidential that you must protect.

Don’t forget that you can also Mask Data in SQL Server to prevent unauthorized users see information they are not supposed to see.

Protect information in SQL Server is a whole process. I think everything in security needs a whole process. You can also obtain a report of sensitive data and work permanently with it.
I love working on SQL Server Performance Tuning and finding the origin of the problems. Music and SQL Server passionate.

Volver arriba