0

My [User] table which is having column [Email] encrypted using Always-Encrypt.

I need to limit [Email]'s length from max to MaxLength(250), I do this by add MaxLength(250) on the email property. 

public class User
{    
    [Key, Required]
    public Guid Id { get; set; }
    [Required, **MaxLength(250)**]
    public string Email { get; set; }
    ...
}

but when I run migration scripts, I got following exception: 

Operand type clash: nvarchar(max) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK1', column_encryption_key_database_name = 'Identity') is incompatible with nvarchar

and the migration scripts is: 

DECLARE @var0 nvarchar(128)
SELECT @var0 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.User')
AND col_name(parent_object_id, parent_column_id) = 'Email';
IF @var0 IS NOT NULL
    EXECUTE('ALTER TABLE [dbo].[User] DROP CONSTRAINT [' + @var0 + ']')
ALTER TABLE [dbo].[User] ALTER COLUMN [Email] [nvarchar](250) NOT NULL
INSERT [dbo].[__MigrationHistory]([MigrationId], [ContextKey], [Model], [ProductVersion])
VALUES (N'201804250659054_12345678', N'Concordya.PWC.Verify.DataAccess.Migrations.Configuration',  0x1F8... , N'6.2.0-61023')

I manually run the scripts in DB, same error.

Does that mean once the column is encrypted, the only way to change property is decrypt, modify, then encrypt?

Thanks,
Cheng

ChengWhyNot
  • 120
  • 1
  • 11

1 Answers1

1

I came across a similar error when trying to increase the size of an Always Encrypted NVARCHAR column. The problem was that the ALTER COLUMN statement still needs to include the encryption parameters. So for example you could alter this -

ALTER TABLE [dbo].[User] ALTER COLUMN [Email] [nvarchar](250) NOT NULL

To this (or whatever you originally set the Always Encrypted parameters to) -

ALTER TABLE [dbo].[User] ALTER COLUMN [Email] [nvarchar](250) NOT NULL
ENCRYPTED WITH (
      ENCRYPTION_TYPE = DETERMINISTIC
    , ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
    , COLUMN_ENCRYPTION_KEY = [**YOUR KEY**]
) NULL
Parrybird
  • 800
  • 11
  • 18