Anyone have tips or tricks for searching encrypted data in an SQL database? If it was 1,000 rows or less then I would just pull them all into a temp CF query and parse them, but there are 130,000+ rows and that is likely not practical. At least one field would require a partial search so I wouldn’t be able to just search fully encrypted strings.

Does this help? I've never done it. TBH, searching and encryption seem incompatible to me on a gut level, since the results reveal at least some of the encrypted data. https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-query-columns-ssms?view=sql-server-ver16

@Dave Merrill That looks cool. And the example table name matches my existing table name. Thanks, will dig in.

Yeah, that's the point. But just to be clear, your app might encrypt the data, instead of using the built-in db encryption. Gotta check the code, but if it doesn't, that db must be.

I also think that MS Always Encrypted looks cool. Rule of thumb my whole career was you don't encrypt anything you want to search. If you think about it encryption has failed if you can search it. @jakobward if you are ever successful searching encrypted data I'd love to hear about it.

Same, but it has to be HIPAA compliant.