Forum Discussion
Running SQL Agent Jobs with Always Encrypted Column
I am experimenting and exploring always encrypted feature in MSSQL server 2016 in local database. As part of the experiment, I tried to apply the feature in SQL Jobs. I did below steps, but I am getting error.
I have written a stored procedure, inside that I am trying to insert an encrypted column of one table to an another encrypted column of another table.
Created table(source) with the below script where the FirstName column is encrypted.
CREATE TABLE [dbo].[Users](
[FirstName] [nvarchar](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
GO
Created another table(destination) with the below script where the FirstName column is encrypted.
CREATE TABLE [dbo].[AppUsers](
[FirstName] [nvarchar](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
) ON [PRIMARY]
GO
Then I created a Stored Procedure like below where the first name is passed as a parameter to the SPROC, searched for that record in Users table and inserting the FirstName value into FirstName column of AppUsers table.
CREATE PROCEDURE [dbo].[TestSproc]
@name NVARCHAR (50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @testValue AS NVARCHAR (50) = (SELECT FirstName
FROM Users
WHERE FirstName = @name);
INSERT INTO AppUsers
VALUES (@testValue);
SELECT *
FROM AppUsers;
END
Now while executing the SPROC, gives expected result.
Declare @firstName nvarchar(50) = 'JohnDoe'
EXEC dbo.TestSproc @firstName
But when it is scheduled as job as below script, produces below error.
USE TestDatabase
GO
Declare @name nvarchar(50) = 'JohnDoe'
EXEC dbo.TestSproc @name
Error:
Encryption scheme mismatch for columns/variables '@name'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '0' 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 = 'CPG142_LT1277') (or weaker). (Microsoft SQL Server, Error: 33299)
Can someone help to arrive solution for the case?