How can I compare encrypted columns in SQL Server 2000?
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