SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Worst Practices - Encrypting Data

By Steve Jones,

Worst Practices - Encrypting Data

This article continues the series on WP.


I know this one will generate some feedback. Some of you are probably extremely skeptical of t this as a worst practice just from the title. However, I would put forth that most DBAs, indeed most people I have met in this business, are not really qualified to implement an encrpytion or cryptographic schema that protects your data.

Now I am not a security expert. I do follow some trends, subscribe to security and cryptogrophy newsletters, but I definiatly am not brave enough to post to the sci.crypt newsgroups. I have at times also experimented with cryptography and seen some things that work and do not work. Yet I would be very hesitant at implementing anything. I don't consider myself qualified, though with some work, I think I could get better.

Why Not?

I've got a few arguements why encryption isn't a good idea in the database and I welcome any thoughts that readers have. First, there is the load factor. Running encrpytion requires that CPU cycles be expended to encrypt and decrpyt the data. At some point the data has to be displayed to the client and this will require some cycles to encrypt/decrypt.

Now some of you will say that we can do the encryption on the client or web server. That is correct, but what do you do when you want to search? suppose you want to query for all clients with the last name of "smith". "Smith" isn't stored in your database as Smith if you have encrpyted this data, instead it looks something like "$DH%HD". Most likely you will have the need to do the encrpytion/decryption on the server to perform this search.

Now suppose you do get past this hurdle and have the tools to handle the encrpytion and decryption. What do you do with the results? Most likely your standard tools, especially Query Analyzer, is useless in examining data and tracing problems. Everything is encrypted. All of a sudden the DBA is not as much help to clients as he used to be.

Let me even assume that you can deal with this. Perhaps you even write your own Query Analyzer that can handle the encrpytion so that you can work with the data. You still have another issue.

Key Management.

Key management is a hot topic and one which no vendors have done a great job implementing. The Kereberos protocol seems to work the best, but it still requires administrative overhead. Not only that, assume you choose a single key with some algorthm like DES. Where do you store the key? In the database? In a flat file? This is a biggest headache with encryption. It does you no good to have a strong algorithm and key if someone can steal the key. It's like having the best vault in the world in your bank, but the combination is stored in the bank manager's desk.

Suppose your key is compromised or even broken by some hacker. Now what do you do? You have to revoke this key and generate a new one. No big deal, right? Well, now you also need to decrpyt all the data in the database and reencrypt it with the new key. For lots of people, this may not be a simple or even feasable task given the size of some databases.

Now, assume that you can change the encrpytion in the database. How do you distribute keys? Keep in mind that the whole encrpytion in the database is useless if you stream unencrypted text across the wire ( across a network). Now I will assume that you have dealt with this by handling argument 1 by moving all the encrpytion CPU work to the client, but you still have the distribution problem. All of this does no good if someone can grab the new key while you send it to clients. It's like solving the bank manager's problem of storing the combination, but someone overhearing him tell another manager the new combination.

The last reason is probably the most important, especially to the readers of this article. A DBAs primary responsibility is to maintain the data integrity. Not quality, we can't control what people put in the database, but we can make sure that what gets put in remains there. What do you do if the data is encrpyted? What if you lose the key? What if a user enters data and loses their key? It is easy to explain to someone that they are responsible. However, you are the DBA. It is your database. If the data cannot be recovered, you probably will need to dust off that resume and get it updated.

So Why Encrpyt?

The two biggest reasons I see people ask for are: one, protecting intellectual property by encrypting your code and two, preventing the system administrator (or other unauthorized people) from reading sensitive data, like financial information.

The first reason is impossible to do in SQL Server. As we speak, I have decryption procedures for v6.5, v7.0, and v2000 to recover the text of any object that is created.

The second reason is difficult to achieve without some level of encrpytion support from the vendor. I am sure some people can come up with some ideas, but the reality is that most products, and especially SQL Server is built to allow the system administrator to be the SUPREME RULER in the product. Without some level of auditing support and protection that a non-technical person can monitor, there is no good method of preventing the system administrator from reading data.


Encrpytion is a tough topic and not something that any vendor has really dealt with. Instead, they have chosen to provide good security tools for restricting access, but without restricting the system administrators. Until a vendor chooses to implement some tools to help manage this, I would argue that performing encrpytion in the database is a worst practice for any SQL Server DBA.

The only caveat to this worst practice would be for items that you do not need to retrieve, but rather only use for validation or comparison. An example would be passwords. A user enters a password and you store it in an encrypted fashion. In most cases, you will just compare the value of the password a user enters against the stored value, which can be done by encrypting the new entry and comparing the encrypted values. Of course most systems usually provide the ability to "resend" the user's password back to them. Another argument against encryption.

As always I welcome feedback on this article using the "Your Opinion" button below. Please also rate this article.

Steve Jones
┬ędkRanch.net November 2001
Return to Steve Jones Home


Total article views: 8404 | Views in the last 30 days: 1
Related Articles

Worst Practices - Assigning Users Rights

Continuing with Andy Warren's series on Worst Practices for a DBA, Steve Jones joins in this week wi...


SQL Server Encryption Best and Worst Practices

In this article by Neil Weicher, he talks about encryption of data and files for SQL Server.


Worst Practices - Making Databases Case Sensitive (Or Anything Else)

Article number four in this popular series continues exposing Worst Practices! This week Andy contin...


Worst Practices - Blank Passwords

Not even worth talking about, right? Probably, but they still exist. Read about this worst practice ...


Worst Practice - Triggering External Events

Andy Warren started his worst practice series some time ago with the intention of looking at the wor...