SQLServerCentral Article

Worst Practices - Encrypting Data


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



You rated this post out of 5. Change rating




You rated this post out of 5. Change rating