Where is that Mask?

It is always a pleasantly alarming situation when all of a sudden a client calls because an automated routine is broken. No matter what the routine is, it’s not the best of news from a client – especially not if it is a routine you helped them migrate to a new server.

I am sure you can hear the klaxon like sound in your head mimicking the panic and shrill anxiety of a department head saying “it has always worked in the past!”

Not the best of news to receive, but also not the least bit cause of panic for you. You remain calm, cool, and collected as you get ready to start troubleshooting the problem.

During a recent client engagement, I had the pleasure of helping them solve a similar problem. Shortly after a server migration and after having proven the process to be properly working, the automated routine started doing something entirely different and new.

It’s Hidden

You see, what once was working suddenly started masking data for a critical component of the process. Due to this change in the data, bank accounts were no longer properly syncing and it was causing a problem with the accounting department who could no longer balance the books. Luckily, the issue was found within a day and the resolution started promptly else it could have been a nightmare.

First order of business, after discovering that the process was indeed running as expected and it was just a change of data, was to figure out what had changed. Was there a code change maybe? Maybe somebody with too many permissions decided to enable a feature on the database side?

We went through and validated everything we could think of. There was no code changes in the related procs, views, or otherwise. Then we went through and validated any sort of database changes such as the enabling of encryption for this key field. There was no encryption or any sort of database feature enabled, but the behavior was eerily similar to some sort of data masking feature having been enabled.

I set out to confirm if data masking was enabled to any extent within the database. The easy way to do something like that is through the use of the following script.

SELECT obj.name AS ObjName,
       c.name,
       c.is_masked,
       c.masking_function
FROM sys.masked_columns AS c
    INNER JOIN sys.all_objects AS obj
        ON c.[object_id] = obj.[object_id]
WHERE c.is_masked = 1
AND obj.is_ms_shipped = 0;

In this particular case, the result of the query was an empty set. There was nothing on the database side enabled that could have caused this sudden change in data behavior. That said, at least we were able to truly confirm that data-masking was not enabled within the database (or any database on the server for that matter.

As it turned out, somebody from the finance team enabled the masking feature from within the application. Since it was entirely application driven, the data was entirely replaced with a masked version of the data so the critical data was overwritten and could not be un-masked. Due to this, a new revision to the process had to be created on the fly to get them back in business.

Put a bow on it

There may be times that data can be changed which will have negative effects on down stream automated processes. When these changes occur, how equipped are we to figure out the root cause? Today, I shared one easy script that could help eliminate one database feature as a culprit to an anomalous data masking suddenly getting enabled. The more tools you have at your fingertips, the better data professional you can be. Add this one to your tool belt!

Interested in more Extended Events articles? Check these out!

Want to learn more about your indexes? Try this index maintenance article or this index size article.

This is the fourth article in the 2019 “12 Days of Christmas” series. For the full list of articles, please visit this page.