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!

SQL Server Backup Encryption

Issue 5 Number 12 December 2013

This morning I began writing an article about the new features in SQL Server 2014. I was seething with anger at Microsoft to have once more provided so many cool features for the Enterprise Edition clients and virtually none for the Standard Edition clients. Then I ran across a newly announced feature, Backup Encryption,. It was a pleasant surprise to me to find that it was available in Standard Edition.

A great number of my clients are in the healthcare arena and must comply with the government-mandated requirements of the Health Insurance Portability and Accountability Act (HIPAA). Another substantial number are eCommerce sites and must comply with PCI SSC Data Security Standards (credit card security). It is generally assumed that compliance to these standards involves encryption of the data in their databases. Backup Encryption might fit very well into their compliance plan.

The Problem With Encryption

There are a number of ways to encrypt data in SQL Server, but currently all of them will come with some level of performance penalty and have a varying degree of cost and difficulty to implement. Transparent Data Encryption (TDE) is considered the gold standard of SQL Server encryption options, but it is only available in Enterprise Edition. Many of my clients are using SQL Standard Edition which further limits their encryption options.

Backup encryption is likely to have less of a performance cost and a lower overall cost than other encryption options. It may be a viable option for them.


Neither standard clearly articulates the specific methods required for compliance, and both are open to interpretation although it is generally thought that encryption for data at rest will be the only acceptable way to meet the requirement. (I dispute that assumption)

Many of my clients, especially smaller clients running Standard Edition are pondering this problem. Because of the difficult and far reaching decisions necessary, very few have actually implemented encryption yet, but the pressure is mounting..

Thoughts on Encryption

Many people are unaware of how little protection is given by data encryption.

Encryption only protects data at rest. This means database data files, log files and database backups. If these encrypted files are stolen they cannot be used by the thief to restore and use the database data. These encrypted files can only be used on the SQL Server from which they originated.

However, encrypted data is decrypted before it is displayed by SQL Server. That means that anyone with access to the SQL Server can view data in its decrypted state. If a hacker or malicious employee gets into your database with SELECT permissions, he\she could conceivably execute a query something like the one below, capture all your credit card data, paste it into a spreadsheet and take it home.


Encryption cannot prevent this.

Levels of Risk

Of the three kinds of files that are affected by encryption, it is my opinion that database backup files carry the most risk because there are generally a lot of them. They are often stored in inadequately secured file systems and they are portable. It is much less likely that someone could penetrate your normal security to the degree that they could detach the database and copy the data and log files to portable media.

For this reason, I believe a good case can be made that encrypting only backup files is adequate due diligence in this area. This is especially true when you compare the relatively small amount of risk reduction achieved by encrypting the entire database to the financial and performance costs of doing so.

Smaller companies with limited IT and financial resources should be able to use this new encryption feature in SQL Server 2014 Standard Edition to escape the substantial performance penalties and cost of implementing other forms of database encryption.

There are better and less expensive ways to protect your data and log files. How about a good combination lock on on the server room door and reasonable caution in giving out the combination? Just because it is simple and inexpensive doesn't mean it won't work.

Afterward by the Author

I hope this information has been helpful to you. I would appreciate any feedback you can give me about this article or recommend future articles you would like to see. If you enjoyed reading this article, you might like to receive this monthly newsletter via Twitter. If so, follow me.

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 Backup Encryption

Elsewhere on this site:

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