Updating always encrypted column using synonyms

Question

I have the following scenario where I have to update 2 always encrypted columns in a SQL Server stored procedure.

1. One column is in the current database (insert statement)
2. The other column is in another database which is being accessed by a synonym (update statement)

The always encrypted columns' master key is in Azure Keyvault.

The stored procedure is compiling successfully with either insert or the update, but not both.

When I include both the statements in the stored procedure, it fails with the error message:

Encryption scheme mismatch for columns/variables '@variableName'. The encryption scheme for the columns/variables is (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEKName', column_encryption_key_database_name = 'DBName') and the expression near line '8' expects it to be DETERMINISTIC, or PLAINTEXT.

Stored procedure code:

ALTER PROCEDURE [dbo].[UserCreatedSPName]
AS
BEGIN
    INSERT INTO Table(columns list)
    VALUES (incoming params)            
    
    UPDATE SynonymTable  
    SET AlwaysEncryptedColumn = [@sameincomingvariable as in above insert]
    WHERE [filter condition]
END

Is this something that can be accomplished?

I have tried moving the update statement into a separate stored procedure and calling it from the main stored procedure - but I am still getting the same error.

Update: The Insert and Update are both happening the same Stored procedure. Also the 2 tables have a separate Column Master Keys in Azure key vault

Answer 1

I created a table and inserted values into that table and enable always encrypted column in by saving the master key value in azure key vault. Table before encryption:

After encryption:

I created synonym table for the above table in another database using below code:

CREATE SYNONYM syn_product
FOR [product].[dbo].[DBtable]
GO
select * from syn_product

Image for reference:

I try to insert values into DBtable which is encrypted by creating stored procedure:

Create PROCEDURE [dbo].[product]
   @id int, 
   @name VARCHAR(20), 
   @price int 
AS 
BEGIN 
   INSERT dbo.DBtable(ID,Name,Price) SELECT @id, @name, @price;
END 

Executed the stored procedure.

EXEC  dbo.product @id = 6, @name = 'P6', @price = 100

I got below the below error:

I tried to update the column by using synonym table with below code:

Create PROCEDURE [dbo].[upda]
@id INT, @name VARCHAR (20)
AS
BEGIN
    UPDATE DBtable
    SET    ID = @id
    WHERE  name = @name;
END

Executed the stored procedure.

EXEC upda @id = 6, @name = 'P1' 

I got the below error:

I disconnected the server and connected again with

Column Encrypted Setting = Enabled

in additional connection parameters. Image for reference:

I selected Query Options:

From Advanced -> Check the box ‘Enable Parameterization for Always Encrypted’

I tried to insert values in DBTable which in current database using below code:

 declare @id int = 12
declare @Name varchar(20) = 'P6'
declare @price int = 100
insert into DBtable values(@id,@name,@price)

It worked successfully, Image for reference:

Data of DBtable:

I tried to update the Synonym table using below code:

declare @id int = 6
update syn_product set ID = @id where Name = 'P1'

It worked successfully. Image fore reference:

Synonym Table data:

In this way we can modify the data in table which is having always encrypted column and its synonym table.

Note: SSMS 17.0 and above has the option to edit the Always Encrypted Column. You can get SSMS 17.1 from [here  ![Jump](https://social.technet.microsoft.com/wiki/cfs-file.ashx/__key/communityserver-components-sitefiles/10_5F00_external.png "This link is external to TechNet Wiki. It will open in a new window.")](https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms) .