3

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
Simon Price
  • 3,011
  • 3
  • 34
  • 98
  • On behalf of Youfah Mizzum - When you alter the tables, try disabling the triggers before you alter. Once altered, are the triggers able to be reenabled? – WhatsThePoint May 15 '18 at 07:35
  • You have linked other questions and said they don't work, can you elaborate? – WhatsThePoint May 15 '18 at 07:36
  • Just trying this now – Simon Price May 15 '18 at 07:36
  • @WhatsThePoint, yes, the do not address my issue in the way that they are for existing stored procedures, where this is a trigger on the table(s) that I am working with. So the encryption would have already have been able to take place. The Triggers are being hit at the point of encryption. – Simon Price May 15 '18 at 07:38
  • Disabling the Triggers didnt work BTW – Simon Price May 15 '18 at 07:38
  • `varchar(max)` means `text blob`, not `large text field`. You'd need it only if you wanted to store, eg 2GB of text – Panagiotis Kanavos May 15 '18 at 09:35
  • @PanagiotisKanavos this is an existing database in the comany I am working with and I cant change the datatype or length – Simon Price May 15 '18 at 09:37
  • I have also updated the fiddle to show the audit item table that the trigger is trying to update – Simon Price May 15 '18 at 09:38
  • @SimonPrice then post your code. Text blobs aren't simple text. Typically you need to use streaming operations. If Always ON encryption is sensitive to the field size, you *do* need to specify the correct parameter size. Simply passing `varchar` without a size is typically the same as passing `varchar(30)` – Panagiotis Kanavos May 15 '18 at 09:38
  • @PanagiotisKanavos its in the referenced SQL fiddle in the question, but here is the link http://sqlfiddle.com/#!18/4ac5c – Simon Price May 15 '18 at 09:39
  • @SimonPrice post the *code* in the question itself. The error was raised by ADO.NET. How did you execute the query? How did you specify the parameters, especially the `@Notes` parameter? – Panagiotis Kanavos May 15 '18 at 09:39
  • @PanagiotisKanavos the code is in the fiddle, and this is all through SQL Server Management Studio at the moment, as I need to encrypt the columns on the Employee table right now, but this is stopping me because of its trigger. – Simon Price May 15 '18 at 09:40
  • I can happily post the code here, but I am forever being berated for not providing a SQL fiddle – Simon Price May 15 '18 at 09:41
  • The fiddle doesn't do anything. – Ben May 15 '18 at 09:41
  • Yes, I have just seen that the fiddle isnt keeping all the values, will edit the question – Simon Price May 15 '18 at 09:42
  • @PanagiotisKanavos I have added the SQL for the two tables in this example part of the trigger. Its only part of the trigger because it does a lot of inserts into the same table its going to be the same column each time that has the issue – Simon Price May 15 '18 at 09:47
  • @Ben the fiddle is now up, and the always encrypted line is partially commented because SQL fiddle wont have all the keys and certificates it needs – Simon Price May 15 '18 at 09:49
  • There's no point having the fiddle if it doesn't demonstrate the problem. – Ben May 15 '18 at 11:08

1 Answers1

3

After much research into this today it is unfortunate at the moment that triggers are not supported to update Encrypted Columns regardless of the data type. So anyone who stumbled across this question and is having the same issue you will need to complete your updates through stored procedures but they will need to be called via application code.

Although the two linked questions in my question above do not directly address my question or help me, you may need to follow the answers in the questions to help you should you need to pass parameterised values to a stored procedure and have issues.

Simon Price
  • 3,011
  • 3
  • 34
  • 98