4

I have a SQL Server 2016 database with "Always Encrypted" set to on. We have multiple encrypted columns and a stored procedure that handles updates. 

CREATE PROCEDURE UpdateRecord
    @ID INT,
    @EncryptedParameter01 NVARCHAR(255) = NULL,
    @EncryptedParameter02 NVARCHAR(255) = NULL,
    @EncryptedParameter03 NVARCHAR(255) = NULL,
    @EncryptedParameter04 NVARCHAR(255) = NULL,
    @EncryptedParameter05 NVARCHAR(255) = NULL,
    @EncryptedParameter06 NVARCHAR(255) = NULL,
    @EncryptedParameter07 NVARCHAR(255) = NULL,
    @EncryptedParameter08 NVARCHAR(255) = NULL,
    @EncryptedParameter09 NVARCHAR(255) = NULL,
    @EncryptedParameter10 NVARCHAR(255) = NULL,
    @EncryptedParameter11 NVARCHAR(255) = NULL,
    @EncryptedParameter12 NVARCHAR(255) = NULL,
    @EncryptedParameter13 NVARCHAR(255) = NULL,
    @EncryptedParameter14 NVARCHAR(255) = NULL,
    @EncryptedParameter15 NVARCHAR(255) = NULL,
    @EncryptedParameter16 NVARCHAR(255) = NULL,
    @EncryptedParameter17 NVARCHAR(255) = NULL,
    @EncryptedParameter18 NVARCHAR(255) = NULL,
    @EncryptedParameter19 NVARCHAR(255) = NULL,
    @EncryptedParameter20 NVARCHAR(255) = NULL
AS
BEGIN
    UPDATE myTable
    SET Field01 = CASE WHEN @EncryptedParameter01 IS NULL THEN Field01 ELSE @EncryptedParameter01 END,
        Field02 = CASE WHEN @EncryptedParameter02 IS NULL THEN Field02 ELSE @EncryptedParameter02 END,
        Field03 = CASE WHEN @EncryptedParameter03 IS NULL THEN Field03 ELSE @EncryptedParameter03 END,
        Field04 = CASE WHEN @EncryptedParameter04 IS NULL THEN Field04 ELSE @EncryptedParameter04 END,
        Field05 = CASE WHEN @EncryptedParameter05 IS NULL THEN Field05 ELSE @EncryptedParameter05 END,
        Field06 = CASE WHEN @EncryptedParameter06 IS NULL THEN Field06 ELSE @EncryptedParameter06 END,
        Field07 = CASE WHEN @EncryptedParameter07 IS NULL THEN Field07 ELSE @EncryptedParameter07 END,
        Field08 = CASE WHEN @EncryptedParameter08 IS NULL THEN Field08 ELSE @EncryptedParameter08 END
    ,   Field09 = CASE WHEN @EncryptedParameter09 IS NULL THEN Field09 ELSE @EncryptedParameter09 END
    ,   Field10 = CASE WHEN @EncryptedParameter10 IS NULL THEN Field10 ELSE @EncryptedParameter10 END
    ,   Field11 = CASE WHEN @EncryptedParameter11 IS NULL THEN Field11 ELSE @EncryptedParameter11 END
    ,   Field12 = CASE WHEN @EncryptedParameter12 IS NULL THEN Field12 ELSE @EncryptedParameter12 END
    ,   Field13 = CASE WHEN @EncryptedParameter13 IS NULL THEN Field13 ELSE @EncryptedParameter13 END
    ,   Field14 = CASE WHEN @EncryptedParameter14 IS NULL THEN Field14 ELSE @EncryptedParameter14 END
    ,   Field15 = CASE WHEN @EncryptedParameter15 IS NULL THEN Field15 ELSE @EncryptedParameter15 END
    ,   Field16 = CASE WHEN @EncryptedParameter16 IS NULL THEN Field16 ELSE @EncryptedParameter16 END
    ,   Field17 = CASE WHEN @EncryptedParameter17 IS NULL THEN Field17 ELSE @EncryptedParameter17 END
    ,   Field18 = CASE WHEN @EncryptedParameter18 IS NULL THEN Field18 ELSE @EncryptedParameter18 END
    ,   Field19 = CASE WHEN @EncryptedParameter19 IS NULL THEN Field19 ELSE @EncryptedParameter19 END
    ,   Field20 = CASE WHEN @EncryptedParameter20 IS NULL THEN Field20 ELSE @EncryptedParameter20 END
    WHERE ID = @ID
END

Our actual stored procedure is more complex, this just gives you an idea. If this is called with only a handful of parameters it works fine. However if it is called with more than 10 the procedure fails.

The error message is

Msg 206, Level 16, State 2, Procedure UpdateRecord, Line 0 [Batch Start Line 0]
Operand type clash: nvarchar is incompatible with nvarchar(255) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'MyNamedKey', column_encryption_key_database_name = 'MyDatabase')

This is not down to individual parameters failing, rather the number of characters being passed. Also this procedure was working fine before the encryption was added.

Any further info on why this is happening and work arounds gratefully received.

Many thanks.

Edited to include call:

I am seeing this both through VB and T-SQL. Simplest case being a direct call from SSMS. I do have "Enable Parameterization for Always Encrypted" checked. The parameters are being passed in correctly, and functions correctly, provided 10 or less parameters are sent. Example call:

DECLARE @PassedEncryptedParameter01 NVARCHAR(255) = 'Updated value 01'

EXEC UpdateRecord @Id=12345, @EncryptedParameter01 = @PassedEncryptedParameter01

As you can see I have the declared the type so it matches. The issue here isn't how the parameters are defined, rather the number of parameters being sent.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Matthew Baker
  • 2,637
  • 4
  • 24
  • 49
  • 1
    Does the client code explicitly declare a parameter data type and max length that matches the proc definition (e.g. `command.Parameters.Add("@EncryptedParameter01", SqlDbType.NVarChar, 255)`)? – Dan Guzman Jun 25 '18 at 11:42
  • 1
    Always encryption not supporting many type conversions. So source and destination column datatype and size must be same. This should be issue here. – Dinesh Jun 25 '18 at 11:57
  • Thanks guys, please see edit to the main question. All good points though. +1 to both, I'm sure these will help someone. – Matthew Baker Jun 25 '18 at 12:27
  • Is the number of parameters definitely the issue, or the aggregate number of bytes the parameters potentially require? How about testing with a procedure that takes `NVARCHAR(100)`s? – Jeroen Mostert Jun 25 '18 at 12:33
  • Also, as an "out of the box" workaround, if you have a super-generic procedure with tons of essentially untyped parameters (since it's all strings), you could consider using an XML or JSON string (`NVARCHAR(MAX)`) containing your row data that the sproc then further unpacks, as the table "structure" isn't adding much. (That said, I seem to recall that there may be limits with encryption where the `MAX` types aren't accepted, although I can't find details on this.) You can also consider using table-valued parameters (though, again, don't recall how that interacts with AE.) – Jeroen Mostert Jun 25 '18 at 12:39
  • @JeroenMostert Thought that myself, but no. In the full procedure call, I pass in a lot more data than here, provided the encrypted parameters are in the first ten sent, the procedure works, outside of the first ten, it fails. Same amount of data just where in the list the parameter come. Same amount of data, different order. I only found this because during testing I moved the encrypted parameter to the top of the call to ease changes, and it worked. Took some experimentation to test internally, and then posted my simplified version here. Would be an easy fix if i didn't have more than 10. – Matthew Baker Jun 25 '18 at 12:44
  • @JeroenMostert unfortunately the way always encrypted works, combining data together becomes impossible. Even basic string concatenation falls over. – Matthew Baker Jun 25 '18 at 12:46
  • Oh right, sorry. I'll blame it on Monday. AE means you can't process the data in any way, beyond comparing for equality. (Even so, if your destination table could potentially be changed to have JSON/XML it would still work, but then it's no longer much of a table.) – Jeroen Mostert Jun 25 '18 at 12:48
  • Yeah, the TVP option was interesting, but again the AE doesn't allow. Nice idea though. – Matthew Baker Jun 25 '18 at 12:50

0 Answers0