2

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459

0 Answers0