SQL Server Always Encrypted: Operand type clash: varchar is incompatible with varchar(max)

SimonPrice3376
SimonPrice3376 used Ask the Experts™
on
As we have regulation changes coming in force in the UK soon a database that I am working on that needs to be updated to have any personal identifiable information encrypted.

A number of my tables have been altered successfully, however on some tables where there are triggers I am getting the following error.


Error SQL72014: .Net SqlClient Data Provider: Msg 206, Level 16, State 2, Procedure tr_Employee_Update, Line 27 Operand type clash: varchar is incompatible with varchar(max) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'xxxx') collation_name = 'Latin1_General_BIN2'

I have looked at this question here, however this doesnt solve my issue Operand type clash: varchar is incompatible with varchar(50) trying to insert in encrypted database

Same with this question too where is doesnt address my issue exactly. SQL Server Always Encrypted Operand type clash: varchar is incompatible with varchar(60) when running EXEC sproc.

I have this issue on a number of tables so would be grateful for any and all help.

Please see the SQL Fiddle here

http://sqlfiddle.com/#!18/4ac5c/3

I have had to split the table and trigger creation because the SQL length is greater than 8000 characters but this is the fullest example I can give.

I am encrypting the columns using Encryption type: Deterministic and Encryption key name: CEK_Auto1.

Not all columns in this table need encrypting and I am altering some of the other fields that have default values too where encryption does need to take place.

Any and all help on the reported issue would be gratefully received.

CREATE TABLE [dbo].[Employee] (
[EmployeeID]        INT           IDENTITY (1, 1) NOT NULL,
[EmployeeTypeID]    INT           NOT NULL,
[Title]             VARCHAR (50)  NOT NULL,
[Forename]          VARCHAR (30)  NOT NULL,
[Surname]           VARCHAR (30)  NOT NULL,
[AddressLine1]      VARCHAR (60)  NOT NULL,
[AddressLine2]      VARCHAR (60)  NOT NULL,
[AddressLine3]      VARCHAR (60)  NOT NULL,
[AddressLine4]      VARCHAR (60)  NOT NULL,
[Town]              VARCHAR (50)  NOT NULL,
[County]            VARCHAR (50)  NOT NULL,
[PostCode]          VARCHAR (20)  NOT NULL,
[Phone]             VARCHAR (20)  CONSTRAINT [DF_Employee_Phone] DEFAULT ('') NOT NULL,
[Mobile]            VARCHAR (20)  NOT NULL,
[Fax]               VARCHAR (20)  NOT NULL,
[Email]             VARCHAR (50)  NOT NULL,
[Extension]         VARCHAR (10)  CONSTRAINT [DF_Employee_Extension_1] DEFAULT ('') NOT NULL,
[SpeedDial]         VARCHAR (10)  CONSTRAINT [DF_Employee_SpeedDial_1] DEFAULT ('') NOT NULL,
[Notes]             VARCHAR (MAX) NOT NULL,
[EmployeeTeamID]    INT           NULL,
[Created]           DATETIME      CONSTRAINT [DF_Employee_Created] DEFAULT (getdate()) NOT NULL,
[OperatorIDCreated] INT           NOT NULL,
[Updated]           DATETIME      CONSTRAINT [DF_Employee_Updated] DEFAULT (getdate()) NOT NULL,
[OperatorIDUpdated] INT           NOT NULL,
[Deleted]           BIT           CONSTRAINT [DF_Employee_Deleted] DEFAULT ((0)) NOT NULL,
[EmployeeIDManager] INT           NULL,
[JobTitle]          VARCHAR (100) CONSTRAINT [DF_Employee_JobTitle] DEFAULT ('') NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([EmployeeID] ASC),
CONSTRAINT [FK_Employee_Employee] FOREIGN KEY ([EmployeeIDManager]) REFERENCES [dbo].[Employee] ([EmployeeID]),
CONSTRAINT [FK_Employee_EmployeeTeam] FOREIGN KEY ([EmployeeTeamID]) REFERENCES [dbo].[EmployeeTeam] ([EmployeeTeamID]),
CONSTRAINT [FK_Employee_EmployeeType] FOREIGN KEY ([EmployeeTypeID]) REFERENCES [dbo].[EmployeeType] ([EmployeeTypeID])
);


GO
CREATE NONCLUSTERED INDEX [IX_Employee_Surname]
ON [dbo].[Employee]([Surname] ASC);


GO

CREATE TABLE [dbo].[AuditItem](
[AuditItemID] [INT] IDENTITY(1,1) NOT NULL,
[ID] [INT] NOT NULL,
[AuditEntityID] [INT] NOT NULL,
[AuditTypeID] [INT] NOT NULL,
[Note] [VARCHAR](MAX) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
[Created] [DATETIME] NOT NULL,
[OperatorIDCreated] [INT] NOT NULL,
[ProfessionalIDCreated] [INT] NULL,
 CONSTRAINT [PK_AuditItem] PRIMARY KEY CLUSTERED 
(
[AuditItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

    ALTER Trigger  [dbo].[tr_Employee_Update] ON [dbo].[Employee] 
FOR UPDATE

AS

--Audit Entity ID for Employees
Declare @AuditEntityID int
set @AuditEntityID = 2

Insert AuditItem
    (ID,AuditEntityID,AuditTypeID, Note, Created, OperatorIDCreated)
Select
    inserted.EmployeeID,
    @AuditEntityID,
    --Update type
    2,
    'Name changed from ' + ltrim(rtrim(ltrim(rtrim(Deleted.Title)) + ' ' + ltrim(rtrim(Deleted.Forename)) + ' ' + ltrim(rtrim(Deleted.Surname)))) + ' to ' + + ltrim(rtrim(ltrim(rtrim(Inserted.Title)) + ' ' + ltrim(rtrim(Inserted.Forename)) + ' ' + ltrim(rtrim(Inserted.Surname)))),
    GetDate(),
    inserted.OperatorIDUpdated
From inserted
        Inner Join deleted on inserted.EmployeeID = deleted.EmployeeID
Where deleted.Title <> inserted.Title or deleted.Forename <> inserted.Forename or deleted.Surname <> inserted.Surname

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> however on some tables where there are triggers I am getting the following error.

Yes, it will fail for tables that have triggers and those encrypted columns are referenced..
Please find the limitations of Always encrypted below.

https://blogs.sentryone.com/aaronbertrand/t-sql-tuesday-69-always-encrypted-limitations/

Triggers are partially supported, as long as you don't reference any of the encrypted columns (and you don't even have to do so directly – something as simple as SELECT * FROM inserted; will yield the operand type clash error (Msg 206)). I did not try to see if there were clean ways to make triggers actually succeed at runtime as long as they didn't mention the encrypted columns. I did try to create a trigger with a table variable that had encrypted columns as well (this required creating the master and column keys in tempdb too), but this only yielded this slightly different Msg 206 error:
Msg 206, Level 16, State 2, Procedure TriggerName
Operand type clash: nvarchar(32) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'ColumnKey', column_encryption_key_database_name = 'EncryptTest') is incompatible with nvarchar(32) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'ColumnKey', column_encryption_key_database_name = 'tempdb')

I presume this is because the two certificates don't match exactly (try it, script them out; the ENCRYPTED_VALUE is different). This means that #temp tables, @table variables, etc. will pose difficulties.

Author

Commented:
I found that shortly after posting the question, so...  can I send the values to a stored procedure that will do the necessary? If so, what special conditions do I need to follow?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> can I send the values to a stored procedure that will do the necessary?

Kindly let me know whether you meant converting the trigger logic to a Stored procedure and then do those INSERT and TRIGGER manipulations via Procedure.. If so, then it might help..
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
If I cant use a trigger to update an audit table then I need to have stored procedures to update the necessary tables and pass through parameterised information.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Yes, that's correct. You can try with that approach..

Author

Commented:
I am getting the same issue where I am getting the type mismatch so thats not working through SQL Server.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Can you post the Procedure Script so that I can help verify it once..
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Hi Simon,

Kindly let us know whether you are able to get through the procedure or still facing issues.
If so, then please share the scripts you were working on..
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Initiating auto close as we have guided author towards the solution but haven't heard back from Author for a long time..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial