SP alternate with column encryption

Question

We have been using the stored proc below to search a table named tblPatients. However, now that we have encrypted the name fields the sp fails. Is there a workaround like temp table or something?

ALTER PROCEDURE [dbo].[kd_selPatientSearch]
    @SearchText     nvarchar(150),
    @SearchType     int = 0

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    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

Since I have seen your table in a different thread, I can tell you that this procedure is a dead case in its current shape. The columns LastName and FirstName are encrypted, whereas CaregiverLogin is not. That information should by now be enough to tell you why this can't work, so I say no more.

So you will have to split @SearchText in two parameters - at least. One for CaregiverLogin, and one for the other two. However, FirstName is nvarchar(20) and LastName is nvarchar(30), so I am not sure that you can use the same variable with both - Always Encrypted is, as you have noted, quite picky about data types.

Answer 2

Also, I tried the following and got error below.
Msg 33299, Level 16, State 6, Procedure kd_selPatientSearch, Line 8 [Batch Start Line 7]
Encryption scheme mismatch for columns/variables '@Firstname '. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '18' expects it to be (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'kdctest') (or weaker).

ALTER PROCEDURE [dbo].[kd_selPatientSearch]  
	@SearchText		nvarchar(150)  
  
AS  
BEGIN  
	-- SET NOCOUNT ON added to prevent extra result sets from  
	-- interfering with SELECT statements.  
	SET NOCOUNT ON;  
  
	DECLARE @FirstName nvarchar(20) = LEFT(@SearchText,20);  
	DECLARE @LastName nvarchar(30) = LEFT(@SearchText,30);  
  
	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 P.LastName = @LastName   
	    OR P.FirstName = @FirstName;  
	  
END  
  

Answer 3

Hi @David Chase ,

Thank you so much for posting here in Microsoft Q&A.

You could have a try with DECRYPTBYKEY if you used TDE encryption.

First, open the symmetric key with which to decrypt the data.

OPEN SYMMETRIC KEY KEYNAME    
   DECRYPTION BY CERTIFICATE CERTIFICATENAME  

Then you could list the encrypted and decrypted ciphertext. If the decryption worked, both of them would match.

SELECT  COLUMNNAME     
AS 'Encrypted COLUMN',    
CONVERT(nvarchar, DecryptByKey(COLUMNNAME))     
AS 'Decrypted COLUMN'    
FROM TABLE  

Finally you could use CONVERT(nvarchar, DecryptByKey(COLUMNNAME)) to replace the encrypted column in your SP.

Or you could create a view as below and replace the encrypted column and table name with Decryptedcolumn and view in your SP.

CREATE VIEW dbo.TestView AS SELECT CAST(DecryptByKey(COLUMNNAME) AS VARCHAR(30)) AS Decryptedcolumn FROM TABLE  

You could refer a complete example from here.

If you encrypted column with Always Encrypted using SSMS, you could refer this article for more detials.

Best regards
Melissa

---

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 4

Below is the table schema for table searched.

CREATE TABLE [dbo].[tblPatients](
    [PatientID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](20) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    [MiddleName] [nvarchar](20) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    [LastName] [nvarchar](30) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    [DOB] [smalldatetime] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
    [StatusID] [smallint] NOT NULL,
    [DietID] [int] NOT NULL,
    [StartDiet] [smalldatetime] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    [EndDiet] [smalldatetime] NULL,
    [Email] [nvarchar](100) NULL,
    [OrgID] [int] NOT NULL,
    [Gender] [char](1) NULL,
    [CaregiverLogin] [nvarchar](20) NULL,
    [CaregiverPW] [nvarchar](20) NULL,
    [LastLogin] [smalldatetime] NULL,
    [MedRecordNo] [varchar](100) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
 CONSTRAINT [PK_tblPatients] PRIMARY KEY NONCLUSTERED 
(
    [PatientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblPatients] ADD  CONSTRAINT [DF_tblPatients_StatusID]  DEFAULT ((1)) FOR [StatusID]
GO

ALTER TABLE [dbo].[tblPatients] ADD  CONSTRAINT [DF_tblPatients_DietID]  DEFAULT ((0)) FOR [DietID]
GO

ALTER TABLE [dbo].[tblPatients] ADD  CONSTRAINT [DF_tblPatients_UserID]  DEFAULT ((0)) FOR [OrgID]
GO

Answer 5

Hi @David Chase ,

Thanks for your update and providing DDL.

Please refer below steps:

Step1: In SSMS, you do that when you’re connecting to the instance in the “Connect to Server” dialog. Select “Options”, and go to “Additional Connection Parameters” and enter into the box “Column Encryption Setting = Enabled” – as per the image below:

Step2: Open a new query window against our database, and then we have to actually enable “Parameterization for Always Encrypted”. Right-click over the query and select “Query Options”, then “Advanced” and enable the option as shown below:

Step3: Get this to work is that you have to rewrite your original query to use variables rather than literal values in the insert. This helps SSMS to be able to manage the rest of the parameterization process.

Please refer my example from below:

--insert into table   
DECLARE @fname nvarchar(20)  = 'Kula'  
DECLARE @ename nvarchar(20)  = 'Kalle'  
DECLARE @date smalldatetime='2020-10-10'  
  
 insert into [dbo].[tblPatients] ([FirstName],[LastName],[dob]) values  
 (@fname,@ename,@date);  
  
 select * from [dbo].[tblPatients]  

Output:

--query the value in where condition  
DECLARE @fname nvarchar(20)  = 'Kula'  
select * from [dbo].[tblPatients]  where [FirstName]=@fname  

Output：

Note: You have to remove 'ORDER BY LastName, FirstName;' from your procedure otherwise you will get the 'Encryption scheme mismatch' errors.

You could refer more details from below links:
Working with Data in Always Encrypted
Query columns using Always Encrypted with SQL Server Management Studio

Best regards
Melissa

---

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.