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?