Forum Discussion

jamesarivazhagan's avatar
jamesarivazhagan
Copper Contributor
Sep 24, 2024

Store Proc Execution Failed When Column Is Encrypted With AlwaysEncrypted

I faced the below problem when I tried to execute a store proc

Msg 33277, Level 16, State 6, Line 27
Encryption scheme mismatch for columns/variables '@SSN1'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '0' expects it to be DETERMINISTIC, or PLAINTEXT.

 

I followed the below link to create a store proc 

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-refresh-parameter-encryption-transact-sql?view=sql-server-ver16

 

1. I created the below table 

CREATE TABLE [Patients] (
[PatientID] INT IDENTITY(1, 1) NOT NULL,
[SSN] CHAR(11),
[Name] NVARCHAR(50) NULL,
CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED ([PatientID] ASC)
);

insert into Patients(ssn, name) values(1,'test1')
insert into Patients(ssn, name) values(2,'test2')
insert into Patients(ssn, name) values(3,'test3')

 

2. I created the below SP

CREATE PROCEDURE [find_patient1] @SSN CHAR(11)
AS
BEGIN
SELECT Name FROM [Patients]
WHERE SSN = @SSN
END;


CREATE PROCEDURE [find_patient2] @SSN CHAR(11)
AS
BEGIN
SELECT Name FROM [Patients]
WHERE SSN = @SSN
END;


CREATE PROCEDURE [find_patient3] @SSN CHAR(11)
AS
BEGIN
SELECT Name FROM [Patients]
WHERE SSN = @SSN
END;

 

3. When I executed 

   

declare @SSN1 as CHAR(11) = '1'
EXEC find_patient1 @SSN1

declare @SSN2 as CHAR(11)
set @SSN2= '1'
EXEC find_patient1 @SSN2

 

I could see the output : test1

 

4. I encrypted the SSN Column using always encrypted

 

5. Executed 

EXEC sp_refresh_parameter_encryption [find_patient1];
EXEC sp_refresh_parameter_encryption [find_patient3];
EXEC sp_refresh_parameter_encryption [find_patient2];

 

6. When I tried to run 

   

declare @SSN as CHAR(11)
set @SSN='2'
EXEC find_patient1 @SSN

declare @SSN1 as CHAR(11) = '1'
EXEC find_patient1 @SSN1

 

I am getting the below error : 

Encryption scheme mismatch for columns/variables '@SSN1'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '0' expects it to be DETERMINISTIC, or PLAINTEXT.

 

Am I missing something here? 

I follow the same procedure as in 

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-refresh-parameter-encryption-transact-sql?view=sql-server-ver16

 

Also, I restarted the Sql-Server service still not working.

 

The same issue even if I have only one stored proc

Am I missing anything ?

 

 

   

 

  • Column encryption setting = enabled is set in the connections in query for the above scenario

Resources