In some instances I encrypt data and store it in my SQL Server 2000 database
along with an intialization vector. For example, this is a great way to
store Social Security Numbers. The encryption is handled by the software
(VB.NET).

However, what if I need to find a record based on a user supplied Social
Security Number? I'm not aware of a means of doing this without unencrypting
every single social security number and comparing it with what the user
entered. That would be very time consuming and inefficient.

It is my understanding that SQL Server 2005 would handle this very easily -
allowing for data to be encrypted and allowing for searches on those
encrypted columns.

Is my understanding of the shortcomings of SQL Server 2000 and the abilities
of SQL Server 2005 accurate here? Is this an actual issue which exists in
SQL Server 2000 that is fixed in 2005?

[quoted text, click to view]

If you are performing full string matches, then you might want to take a
look at http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx. If you
want to allow arbitrary substring searches, then you pretty much have no
choice but to search against the decrypted data and, yes, this is expensive.


[quoted text, click to view]

Not without decrypting them (at least for substring searches), although the
decryption is easier to perform.


[quoted text, click to view]

I'm not sure what you mean by "fixed" here. What SQL Server 2005 alters is
the ease of encryption and decryption, but I haven't seen anything to
suggest that any of the new functionality is intended to help with search
scenarios.