September 2, 2016 at 9:43 am
We are implementing SQL column level encryption (that has been around since SQL 2005) to encrypt a few columns in our SQL 2014 database.
There are two email fields that were originally defined as varchar(300). When I attempt to save the encrypted value into the new varbinary field, it always stores a NULL value if the original unencrypted text length is greater than 198. I had originally defined the new varbinary field as varbinary(512), but the length of the encrypted output was 514, so I enlarged it to varbinary(max), and it still stores the value as NULL.
Here is an update statement applying the output of the encryption to the varbinary(max) field (field is defined as: EmailAddressEnc (varbinary(max), null)).
UPDATE Member
SET EmailAddressEnc = 0x00AF7BD106CBB848A589F7663612E335010000001A839B0A7064E80A65412B814EFE728455F36E07CC6688C9CACC6AFB80850F4522ADF7B62C9F1C1194F3B2027D027175C73190F9E89D808A1A24F88232D390A6494C15DC01921ACF8D53C19BC3AFE9CA06E9A5829A94659D0A0D80B17E5D7E75A9B2E0E8E98BDE89159DCF40A9B9EEACF6B028061B4CA10D42C6ED8E28748A11E3179CB9978CFC1DEEEEB6C9838A496307000019A016F978B894B3917BF5EAAD9FEE059459110169C90C137841578E0B23EFFBAADF0F4E440CD976B33BEAF3A92B07FAE0359B8B867BDF5087F041AF0AF128864CC3FDD9591BF31B57CBE3F1C57B0C02B956BF6145C4886A88
WHERE MemberNumber = 9999
Can anyone explain to me why this statement stores a NULL?
Thanks in advance for any assistance.
September 2, 2016 at 10:35 am
Please disregard this question (delete if possible).
September 2, 2016 at 1:09 pm
Was the problem a certificate access one?
September 6, 2016 at 7:36 am
No, it turned out that the data was not storing as NULL after all, it was returning as NULL when I do the decryption. Works fine if the original value is less than 198 characters, but decrypts as NULL if it is greater.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply