sql server consulting logo
sql server consulting is our only business

Free SQL Server Consultation

Sometimes you only need the answer to a question or two. If 15 minutes of our time can help you solve a SQL Server problem, we are happy to help. No charge, no strings attached.

Call 503.914.6442

Using SQL Server in the Enterprise - Archives

Sign up for your FREE subscription to this SQL Newsletter

Give this article a Google +1!

Encryption Aarghh!

Issue 4 Number 11 November 2012

I have many readers in the healthcare industry and even more in the e-commerce arena. This newsletter should be of special interest to them. But any company that accepts credit cards should be equally interested.

Both of these groups have a formal set of standards with which they must comply in handling sensitive data. For healthcare, HIPAA is the federally mandated standard. For e-commerce or any other enterprise that handles credit card transactions, the standard is PCI DSS, created by the Payment Card Industry Security Standards Council.

Both of these standards are vague and seem open to different interpretations. It is not completely clear what you must do to be in compliance with these regulations. Despite this fuzziness, there is general agreement that sooner rather than later you will be required to encrypt your sensitive data.

Later on in this article we will suggest that compliance for data at rest might be attained without data encryption, but for now let’s assume that you will soon have to be encrypting sensitive data in your SQL Server databases.

What Are Your Choices for Encryption in SQL Server?

There are really no good choices for encryption in any common database platform. The choices in SQL Server are as good or better than in other platforms. The best option is, you guessed it, only available in the Enterprise Edition of SQL Server. But no matter what edition you have, encryption will be an expensive struggle in terms of time, money and database performance.

There are essentially three viable choices for encrypting SQL Server data. We describe the options below.

Cell Level Encryption in SQL Server

If you have very little data to encrypt or you do not have the budget for Enterprise Edition, cell level encryption might be a good option for you. This form of encryption is available in Standard Edition. Other than that, its biggest advantage is that it is very granular. It allows you to encrypt only the data that needs to be encrypted. i.e a single column in a specific row or rows. You can also encrypt entire columns. Compare this to the other options that waste a lot of CPU encrypting and decrypting a large quantity of your data that does not need to be encrypted.

However, there are a number of negatives to cell-level encryption:

  • You must alter your application code. Encrypted data is stored as varbinary data. Your code must cast the unencrypted data to its proper data type. It can be a prohibitive amount of work to change existing application code.
  • The retrieve-and-convert process is necessarily slower than direct access of non-encrypted data.
  • The encryption process randomizes the encrypted data. Indexes are not useful on these columns and that can result in poor performance for queries that filter or join on encrypted columns.

Transparent Date Encryption (TDE)

This is very often the best choice to solve your encryption problem but it is only available in the Enterprise Edition of SQL Server. TDE requires no changes to your code. It encrypts the whole of the data and log files. The performance impact of TDE is moderate. If it weren’t for the price, everyone would probably choose TDE.

Encrypting File System (EFS)

EFS is encryption at the file system level. You can encrypt at the file, directory or disk level. You may have other types of files where EFS would be a viable option for encryption, but don’t go there for database encryption.

I could go into grisly detail about performance problems and other reasons not use EFS to protect your database data but I will ask you to trust me on this. Don’t go there.

Other Considerations

Encryption requires a DBA with a high level of expertise. Encryption can be a bewildering experience unless you are comfortable with a mountain of certificates and a proliferation of keys. One consequence of getting those things wrong is that you can lose all of your data. Enough said on that point.

A Solution Looking for a Problem

In my opinion, no one in their right mind would encrypt their databases unless someone bigger was standing over them with a club. Encryption is a complex and expensive solution to a simple problem.

Encryption only protects data at rest in the data and log files. Data that is in active use, moving across a network or the internet is not protected by encryption.

If the server, the encrypted database files or an encrypted backup file are stolen, they would be useless to the thief. However, there are many lest costly ways to keep these physical objects from being stolen.

How about a passcode lock on the server room and care in selecting the employees who can enter it?

In contrast, if a hacker or dishonest employee connects to your encrypted database, they can retrieve the data in an unencrypted state and do anything they want with it.

A Straw to Grasp

A client of mine sent me the URL below. It is a link to a video about complying with the “Data at rest” requirements of HIPAA specifications without encrypting your data.

About 20 minutes into this video the narrator discusses how one might accomplish this. I am neither a bureaucrat nor an attorney, so I will leave it to you to decide whether his case would convince a HIPAA compliance officer.


As always, if you have questions or comments give me a call at 503.914.6442 or send me email.

Kurt Survance, SQL Consulting, Inc.

Return to Top

Go to SQL Consulting Home

On this page:

SQL Server Encryption

Elsewhere on this site:

Call SQL Consulting
mcse microsoft certified systems engineer
mcdba microsoft certified database administrator