Sensitive Data Management – Working With Sensitive Data in Regulated Industries

John Morehouse

Published On: November 4, 2021

Categories: 0

Let’s face it—the harsh reality, seen often in news stories today, is that security breaches are going to happen. In today’s world of cybercriminals and nation-state attackers, it’s not a question of “if” but “when” your organization is attacked. If you don’t take appropriate steps to ensure sensitive data is protected properly, it could devastate the organization financially. Whether it’s GDPR fines, lawsuits from affected parties, or loss of cyberinsurance benefits, you could suffer numerous financial blows if you’re not managing your security properly.

In general, there are two ways to help ensure the safety of your data: access controls and encryption. Essentially, these determine who has access to the data and whether they have the right to read the data. If done properly, those two conditions must be met before any individual can see a particular data element.

Both access controls and encryption require careful thought and planning before implementation. If you don’t have these controls in place and your organization is attacked, you face the worst-case scenario: a broad-scale data breach.

Some industries and data elements need a higher level of security than others. For instance, healthcare and financial services might require additional layers of data privacy to align with industry laws.

Tagging the Data

Before beginning sensitive data management, start by classifying and identifying what data elements need securing. One strategy for doing this is tagging your database data.

Within the United States, the most common piece of sensitive data is a Social Security number, which is a key element of identity theft. In previous years, organizations would use a person’s Social Security number as a unique identifier within their own respective systems. Due to an increase in identity theft and data breaches over past years, many organizations have migrated away from this identifier in favor of either a tokenized value or unique identifier with meaning only within that organization or system of record.

Even with this change, Social Security numbers are still present within organizations, especially in systems with tax reporting requirements such as payroll. This data still needs to be secured to help prevent unauthorized access.

Depending on how the data is structured and stored, there are various ways to tag data as sensitive. For example, if data is stored in a key-value type of structure, you could simply add a key-value pair of “sensitive: yes” as an attribute of the data element.

Applications can tag data for you. SQL Server Management Studio (SSMS) for SQL Server 2012 or higher and SSMS 17.5 or higher can identify and classify data as confidential (Figure 1).

Figure 1:  SSMS can recognize and tag sensitive data in databases.

Figure 1: SSMS can recognize and tag sensitive data in databases. © 2021 Microsoft Corporation. All rights reserved.

(NOTE: There is a minor change in how this is implemented in SQL Server 2019. In earlier versions, the data about confidentiality is stored as extended properties of an object, but now the data is stored in its own dedicated catalog view.)

Implementing Proper Access Controls

The concept of access controls spans both the physical and logical space when it comes to data breaches. Both physical and logical access are essential components to any thief who gains access to data. Some organizations have good access controls on one aspect of security but not the other—and both aspects must be considered when evaluating proper security measures for sensitive data.


Physical access controls help prevent someone from physically getting to the data. For example, your data center is most likely secured by door locks for which you must have the appropriate combination (usually a code or badge) to gain access. Without the key, you cannot physically get into where the data is located without damaging the environment.


Logical access controls are defined within the context of the data itself. This would include things such as access control lists, network security, and individual file permissions.

Logical access control can be difficult to implement due to the volume of options that can be potentially configured. These access layers, however, can sometimes be too restrictive, unnecessarily hindering end users. A balance must be found between proper security and organizational productivity.


Proper security includes encryption, which is the process of encoding data by using a mathematical algorithm along with a predetermined value, called a key, that allows for decryption of the encoding. The key could be a password, a certificate, or keys such as symmetric and asymmetric keys. The level of encryption required for the industry dictates which method is most suitable.

Encryption at Rest

For encryption at rest, the data contained on a disk storage sub-system is encrypted. However, when the data is read off the disk, the data becomes unencrypted and is considered to be “clear text” for the remainder of its journey to the application. Once the data is no longer needed, it’s once again encrypted while at rest on the disk.

Modern storage systems offer native hardware encryption by default. If you purchase a storage array or use cloud storage, the disk is encrypted immediately, with no further intervention from administrative staff. Encryption at rest is usually considered the bare minimum in any industry where encryption is required. Additional security requires the data to be fully encrypted in flight.

Transparent Data Encryption (TDE), a feature of SQL Server, provides encryption at rest. Utilizing certificates and symmetric keys, SQL Server can encrypt data as the data pages are written to disk. As the pages are read, they’re subsequently decrypted via the same certificates and keys.

Encryption in Flight

Data encrypted in flight is encrypted through its entire journey, from the disk to memory to its final destination. This helps prevent “man-in-the-middle” attacks, which happen when an attacker gains access to the flow of data from one point to another. If the flight path is not encrypted, it can be easy for the attacker to see—and perhaps even alter—the data. Encrypting the data in flight helps ensure if an attacker’s able to see the data flow, the data itself is unviewable.

Just as SQL Server provides for encryption at rest via TDE, “Always Encrypted” is a native solution for encryption in flight. This protects the data even further by removing the potential for database administrators to view decrypted data, which TDE doesn’t prevent.

The encryption process occurs in conjunction with the application, which requires encryption keys to be stored outside of SQL Server, such as in Azure Key Vault. Always Encrypted helps ensure the data is encrypted at rest as well as in flight, from the disk storage system all the way to the calling application.

Security by Obfuscation

Some will say security by obfuscation is encryption, but it really isn’t. Encryption is a mathematical method to encode the value of the data, whereas obfuscation hides the value of the data with some overlaying mask to make the true value of the data hard to guess. Obfuscation can be reversed-engineered, whereas an appropriate level of encryption offers much better protection from the encoded value being reversed-engineered or cracked.

 Encrypt Your Data at the Start

The best time to implement encryption is before you store a single data point. After data is stored and used in a production environment, implementing encryption for sensitive data becomes more difficult. It’s certainly not impossible but is more of a challenge.

As explained in this article, encryption is crucial for protecting your data. But it can also be a time-consuming process. There are ways to make it easier, and as you begin to examine the possibilities, look at SolarWinds® Database Mapper. Database Mapper can help you identify and document data elements that might be considered sensitive, informing your efforts at protection. You can try Database Mapper free for 14 days.

John is a Principal Consultant with Denny Cherry & Associates Consulting holding Microsoft Data Platform MVP and VMware vExpert awards. He specializes in deploying SQL Server related solutions to solve business needs for organizations.


Fighting Downtime and Slowdowns