I am experimenting and exploring always encrypted feature in SQL Server 2016 in local database. As part of the experiment, I tried to apply the feature in SQL Server 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.
Scripts used are shown below:
Users Table :
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
AppUsers Table:
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
Stored procedure:
CREATE PROCEDURE [dbo].[TestStoredProcedure]
@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 stored procedure via query window, it returns the expected result with Column Encryption Setting=enabled and in Query Options checked in Enable Parameterization for Always Encrypted.
Declare @firstName nvarchar(50) = 'JohnDoe'
EXEC dbo.TestStoredProcedure @firstName
But when it is scheduled as job as below script, produces below error.
USE TestDatabase
GO
Declare @name nvarchar(50) = 'JohnDoe'
EXEC dbo.TestStoredProcedure @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 = 'MyDatabase') (or weaker). (Microsoft SQL Server, Error: 33299)
Can someone help find a solution for the case?