Encrypted search

Question

In moving to an SQL Server 2016 Always Encrypted database, we have some search stored procedures that find records based on a full or partial match on name columns that will be encrypted. Is there any workaround or options we can consider? A sample SELECT statement is below and FirstName and LastName columns will be encrypted.

    SELECT P.FirstName, 
            P.LastName, 
            P.CaregiverLogin, 
            O.Organization, 
            O.OrgUserName
      FROM dbo.tblPatients AS P INNER JOIN
           dbo.tblOrganization AS O ON P.OrgID = O.OrgID
     WHERE (CASE WHEN @SearchType = 0 AND P.CaregiverLogin = @SearchText THEN 'T'
                 WHEN @SearchType = 1 AND (P.LastName = @SearchText OR P.FirstName = @SearchText) THEN 'T'
                 ELSE 'F'
                 END = 'T')
    ORDER BY LastName, FirstName;

Answer 1

Hi @David Chase ,

Could you share with us any search stored procedures you have tried?
This thread may be useful to you：SQL Encrypted Columns in WHERE Clause

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

In this example, you are making point lookups on equality. In that case, you can use the code above if you use deterministic encryption, so that the a value always results in the same encrypted value. (Which is a weakness from a security standpoint.)

If you are using LIKE, this is possible if you set up Always Encrypted with Enclaves, which I have told you about a few times already. (And also warned you that it is complex.)