Simple Talk is now part of the Redgate Community hub - find out why

Encryption Alternatives

Databases often contain sensitive information and cell-level encryption is a very effective method to protect this information from those who should not have access to it. Although, encryption is not without its challenges. There is a performance cost. There also is additional administrative tasks required with its introduction into the database. That is why there are some instances where encryption is restricted, by the powers that be, from being utilized.

When the DBA or Developer finds themselves faced with the requirement of protecting sensitive data while not being able to utilize encryption they must seek alternatives to this feature. There are many alternatives available and some, when appropriately implemented, can be as effective as encryption itself. Below are a few examples of these alternatives:

Store Only The Data You Need
In this day and age storage media is relatively inexpensive. This is a good thing, but it does enable lax storage strategies. When determining the schema of your table consider the use of the columns that are being added, especially ones that contain sensitive data. Try to avoid the “We may need it someday” mind set.

For example: The Social Security Number is an identifying number provided by the Federal Government for individuals in the United States. This piece of data is often found in tables that contain other information about an individual. If this piece of data is not being used functionally by the applications that access the database its inclusion in the schema should be reconsidered and challenged.

If it is determined that the sensitive data must be stored in the table, consider only storing a portion of the data rather than the entire data. For example: If you must store the Social Security Number, storing the last four digits may suffice for its use in your database.

Masking Plain Text 
We are all familiar with the standard method of displaying the text of a password as it is being typed into a text box; all letters are replaced with an asterisk (*). Most are also familiar with the masking methods used on a credit card receipt: the last four digits of the credit card number are displayed in plain text while the remainder of the number are replaced with an asterisk.

These two examples demonstrate how sensitive data within the database is hidden at the point of disclosure from the eyes of those who should not see it. While this approach is very effective it does not necessarily suggest that the sensitive data that is stored in the database is similarly masked. According to PCI Compliance Standards, Requirement 3.4, credit card numbers (PAN) cannot be stored in a database in its entirety. Masking this information within your database is a valid means of meeting these requirements.

When implementing masking, please remember that the storage of data that is entirely masked is rather useless since it cannot be read or searched by anyone regardless of the security level. Determining the extent of masking that is to be applied to the sensitive data is a fine balance between maintaining the value of the data for authorized users while rendering it useless to those who are unauthorized for its use.

Coding Data
Utilizing numeric or alphanumeric values to represent the real values of data is often a practice that can be found in normalization efforts. Placing a value of “16” to represent the State of Indiana or “01” for the State of Delaware is a very efficient means of storing this data. It also allows for the represented text to change without having to modify all of the rows that are associated with that value.

This same practice can be utilized to protect sensitive data. Consider a financial planner who maintains records of their client’s financial history in a database. Knowing that certain aspects of their information is sensitive the financial planner might store the value of “1250” to indicate that his client has declared bankruptcy or “3427” to indicate the beneficiary of the client’s assets. Without access to the data that provides the interpretation of this code, the sensitive data remains a mystery.

SQL Server offers the HashBytes method. This method uses a specified algorithm to turn plain text into a binary value that then can be stored in the database masking the sensitive data it represents. The specific syntax of using the HashBytes method can be found at:

There is a fine line between hashing data and encrypting data. Encrypting data involves the process turning plain text into cipher text as well as decrypting the data back into plain text when needed. Hashing converts the plain text into a binary value and is only compared with other hashed values to produce validation of plain text; thus removing the key management aspect of encryption as well as a portion of the performance affects.

Here is a great blog entry that talks about using the HashBytes method to secure sensitive data:

While these options have their own benefits and vulnerabilities they do offer some creative approaches to protecting the sensitive data that has been entrusted to your organization.

How you log in to Simple Talk has changed

We now use Redgate ID (RGID). If you already have an RGID, we’ll try to match it to your account. If not, we’ll create one for you and connect it.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.


Simple Talk now uses Redgate ID

If you already have a Redgate ID (RGID), sign in using your existing RGID credentials. If not, you can create one on the next screen.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.