Ask the Expert

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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: