Encrypting a column in SQL Server 2000 database

Question

I've been tasked with encrypting several years worth of sensitive data (don't ask) stored in a SQL Server 2000 Database. I need to encrypt both historical data and come up with a process to encrypt and decrypt new data as it flows through the system.

Edit: The processes that produce the data are not the same as the process that will consume the data, so I cannot easily implement a solution outside of SQL Server.

Most of the solutions out there seem to use extened stored procedures that call dlls that must be installed on the server.

The best I've seen so far is here, the tools are free but you have to register at the site.

Are there any better solutions out there?

Answer 1

use .net or whatever your process is that reads/writes to your database

otherwise if I steal your hardrive I have everything I need to decrypt, it should NOT live on the same box

last year there was an incident in a data center in Chicago where over 20 servers were stolen!

Answer 2

Encryption pre-SQL 2005 was quite limited as you've found and I think tools like that one you've found will be your best bet for SQL 2000.

If you can upgrade to SQL 2005+ you'll have more options open to you: i.e.

http://dotnetslackers.com/articles/sql/IntroductionToSQLServerEncryptionAndSymmetricKeyEncryptionTutorial.aspx

(database level, column level encryption)

Answer 3

Encrypting the devices that the database file resides on in most cases is a better solution, TrueCrypt, EFS.

There are a lot of ways to mess up encryption especially on columns with very little data. Unless someone on the team has a complete understanding of ECB modes, rainbow attacks, and key protection the device solution is the safest and probably most efficient performance wise.

Answer 4

You can also use Netlib Encryptionizer (http://www.netlib.com/). They'll encrypt the whole database on the drive, or they can do column encryption using stored procedure or an API.

It works fine with SQL Server 2000.

Answer 5

Security was somewhat more limited in SQL 2000. SQL 2008 (enterprise ed) introduces Transparent Data Encryption which can be used to secure data-at-rest (DAR), if upgrading is an option. Another option is to use full disk encryption (FDE) such as BitLocker to encrypt the entire volume on which the SQL data files reside.

I believe there are 3rd party XPs (extended stored procs) that you can use, but I don't have add'l info on them.