Worst Practices - Encrypting Data
Part 1 - Worst Practices - Part 1 of a Very Long Series!
Part 2 - Worst Practices - Objects Not Owned by DBO
Part 3 - Worst Practices - Not Using Primary Keys and Clustered Indexes
Part 4 - Worst Practices - Making Databases Case Sensitive (Or Anything Else)
Part 5 - Worst Practices - Assigning Rights to Users
Part 6 - Worst Practices - Depending on the GUI
Introduction
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.
Conclusions
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
