Q

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 last published in January 2006

Dig Deeper on SQL Server Security

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close