Problem solve Get help with specific problems with your technologies, process and projects.

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!

Dig Deeper on SQL Server Security

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.