How can I compare encrypted columns in SQL Server 2000?

SQL Server security expert Steven Andres offers third-party apps to help compare encrypted columns.

I have a question about comparing encrypted column values. Here is the logic:

1. Data in column A in table T1 is encrypted 2. Data in column B in table T2 is normal text. Its size is varchar(6)...

3. One of the conditions in our WHERE clause is to compare first 6 characters of column A in table T1 with column B in table T2

How do I acheive that? Is there a SQL Server built-in function for the encryption/decryption process I could use here? The query is part of a stored procedure. Triple DES is the algorithm used for data encryption. We are using SQL Server 2000.

Unfortunately, there is no built-in encryption/decryption for SQL Server 2000 (from all accounts, that will be a big new feature in 2005). There are plenty of third-party apps that do the same things (see www.activecrypt.com/products.html). You can do the lookup with a temp table by decrypting all values and then doing the query on a temp table. This is going to have a huge performance impact but that's the price of encryption!
This was first published in January 2006

Dig Deeper on SQL Server Security

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.



Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: