Running SQL Agent Jobs with Always Encrypted Column

%3CLINGO-SUB%20id%3D%22lingo-sub-828588%22%20slang%3D%22en-US%22%3ERunning%20SQL%20Agent%20Jobs%20with%20Always%20Encrypted%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-828588%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20experimenting%20and%20exploring%20always%20encrypted%20feature%20in%20MSSQL%20server%202016%20in%20local%20database.%20As%20part%20of%20the%20experiment%2C%20I%20tried%20to%20apply%20the%20feature%20in%20SQL%20Jobs.%20I%20did%20below%20steps%2C%20but%20I%20am%20getting%20error.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20written%20a%20stored%20procedure%2C%20inside%20that%20I%20am%20trying%20to%20insert%20an%20encrypted%20column%20of%20one%20table%20to%20an%20another%20encrypted%20column%20of%20another%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECreated%20table(source)%20with%20the%20below%20script%20where%20the%20%3CSTRONG%3EFirstName%3C%2FSTRONG%3Ecolumn%20is%20encrypted.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ECREATE%20TABLE%20%5Bdbo%5D.%5BUsers%5D(%0A%5BFirstName%5D%20%5Bnvarchar%5D(50)%20COLLATE%20Latin1_General_BIN2%20ENCRYPTED%20WITH%20(COLUMN_ENCRYPTION_KEY%20%3D%20%5BCEK_Auto1%5D%2C%20ENCRYPTION_TYPE%20%3D%20Deterministic%2C%20ALGORITHM%20%3D%20'AEAD_AES_256_CBC_HMAC_SHA_256')%20NOT%20NULL%0AGO%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ECreated%20another%20table(destination)%20with%20the%20below%20script%20where%20the%20%3CSTRONG%3EFirstName%3C%2FSTRONG%3Ecolumn%20is%20encrypted.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ECREATE%20TABLE%20%5Bdbo%5D.%5BAppUsers%5D(%0A%5BFirstName%5D%20%5Bnvarchar%5D(50)%20COLLATE%20Latin1_General_BIN2%20ENCRYPTED%20WITH%20(COLUMN_ENCRYPTION_KEY%20%3D%20%5BCEK_Auto1%5D%2C%20ENCRYPTION_TYPE%20%3D%20Deterministic%2C%20ALGORITHM%20%3D%20'AEAD_AES_256_CBC_HMAC_SHA_256')%20NOT%20NULL%0A)%20ON%20%5BPRIMARY%5D%0AGO%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThen%20I%20created%20a%20Stored%20Procedure%20like%20below%20where%20the%20first%20name%20is%20passed%20as%20a%20parameter%20to%20the%20SPROC%2C%20searched%20for%20that%20record%20in%20%3CSTRONG%3EUsers%3C%2FSTRONG%3Etable%20and%20inserting%20the%20%3CSTRONG%3EFirstName%3C%2FSTRONG%3Evalue%20into%20%3CSTRONG%3EFirstName%3C%2FSTRONG%3Ecolumn%20of%20%3CSTRONG%3EAppUsers%3C%2FSTRONG%3Etable.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ECREATE%20PROCEDURE%20%5Bdbo%5D.%5BTestSproc%5D%0A%40name%20NVARCHAR%20(50)%0AAS%0ABEGIN%0A%20%20%20%20SET%20NOCOUNT%20ON%3B%0A%20%20%20%20DECLARE%20%40testValue%20AS%20NVARCHAR%20(50)%20%3D%20(SELECT%20FirstName%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20FROM%20%20%20Users%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20%20FirstName%20%3D%20%40name)%3B%0A%20%20%20%20INSERT%20%20INTO%20AppUsers%0A%20%20%20%20VALUES%20(%40testValue)%3B%0A%20%20%20%20SELECT%20*%0A%20%20%20%20FROM%20%20%20AppUsers%3B%0AEND%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ENow%20while%20executing%20the%20SPROC%2C%20gives%20expected%20result.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EDeclare%20%40firstName%20nvarchar(50)%20%3D%20'JohnDoe'%0AEXEC%20dbo.TestSproc%20%40firstName%20%20%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EBut%20when%20it%20is%20scheduled%20as%20job%20as%20below%20script%2C%20produces%20below%20error.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EUSE%20TestDatabase%0AGO%0ADeclare%20%40name%20nvarchar(50)%20%3D%20'JohnDoe'%0AEXEC%20dbo.TestSproc%20%40name%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CSTRONG%3EError%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EEncryption%20scheme%20mismatch%20for%20columns%2Fvariables%20'%40name'.%20The%20encryption%20scheme%20for%20the%20columns%2Fvariables%20is%20(encryption_type%20%3D%20'PLAINTEXT')%20and%20the%20expression%20near%20line%20'0'%20expects%20it%20to%20be%20(encryption_type%20%3D%20'DETERMINISTIC'%2C%20encryption_algorithm_name%20%3D%20'AEAD_AES_256_CBC_HMAC_SHA_256'%2C%20column_encryption_key_name%20%3D%20'CEK_Auto1'%2C%20column_encryption_key_database_name%20%3D%20'CPG142_LT1277')%20(or%20weaker).%20%20(Microsoft%20SQL%20Server%2C%20Error%3A%2033299)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ECan%20someone%20help%20to%20arrive%20solution%20for%20the%20case%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-828588%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eagent%20job%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EEncryption%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESQL%20Database%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESQL%20Server%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESQL%20Server%202016%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

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?

0 Replies