SQL Server data masking

Question

SQL Server data masking

Admingod

SSCertifiable

Points: 5997
More actions
February 26, 2020 at 6:17 pm

#3729020

Can you able to mask data in sql server 2012? If yes, would it be easy to mask few tables or the complete database?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90257 More actions · Answer 1

There isn't any native way to mask data - you either have to build it yourself of purchase a product. You should take a look at: https://www.red-gate.com/products/dba/data-masker/

I would also recommend reviewing this article: https://www.red-gate.com/hub/product-learning/data-masker/masking-data-practice

Whether or not masking is worth the time and effort you spend on it will depend on your systems and what actually needs to be masked - and whether or not it can actually be masked without breaking the system.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 2

Maybe the native Dynamic Data Masking feature first made available in 2016 would be a good enough reason to update 2012 to 2019 sooner than later?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Admingod SSCertifiable Points: 5997 More actions · Answer 3

I was looking at native option. So you saying using sql server native method data masking can be done from SQL server 2016 onwards?

Jeffrey Williams SSC Guru Points: 90257 More actions · Answer 4

Jeff Moden wrote:

Maybe the native Dynamic Data Masking feature first made available in 2016 would be a good enough reason to update 2012 to 2019 sooner than later?

From what I have read - this is more of a security setting. You define - for each column - the masking function and any users that do not have the UNMASK permission will only see the masked data.

This does not change the actual data - just masks the data on output. I guess it depends on what you mean by masking the data...

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 5

Absolutely agreed. To me, the term "mask" or "data masking" actually has nothing to do with changing the underlying data. It's a poorly chosen term (IMHO) except when it's used like (for example) ***-**-1234 to "mask" a social security number from view on a GUI.

To me, changing the underlying data is "Desensitizing through wholesale replacement" (which isn't masking to me) and I'm not talking about the feeble attempts that people make with the equivalent of "magic decoder rings" that a lot of people use in an improper attempt to desensitize things like SSNs,.

What's really bad (and sad) is when people think they've encrypted data when all they've really done is mask it.

And so that does bring up the question... what does the OP mean by "mask"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 6

Admingod wrote:

I was looking at native option. So you saying using sql server native method data masking can be done from SQL server 2016 onwards?

If you look at my definition of data masking in my previous post above, yes. I don't, however, recommend the use of such data masking if the data is actually sensitive as PII or anything else. Reportedly, a proverbial first year cadet can break it.

What do you intend to do with what you're calling "data masking"? Do you really mean "data encryption"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 7

Admingod wrote:

I was looking at native option. So you saying using sql server native method data masking can be done from SQL server 2016 onwards?

When you say "mask the data" what do you mean? That is a big determining factor for whether or not something within SQL Server can get the job done for you or not.

Let's say you want to mask information within your production server such that only certain roles have access. That is what the native Dynamic Data Masking is all about. It hides data based on security. What this doesn't do, is hide data in less secure environments.

So, let's say you want to hide data from your developers because of the GDPR or similar compliance requirements. If you use Dynamic Data Masking in your non-production servers, and the people there have elevated privileges, which they most certainly will in most cases, they'll be able to see the "masked" data. This is why, when talking about masking in non-production environments, the term gets overloaded. It means, change the data because you can no longer simply hide the data.

If you clarify your goals, it'll be easier to point you to a viable solution.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning