SQL Server 2016 TDE Encryption on big text column

Question

We implemented SQL TDE (SQL 2016) successfully on columns smaller in text size, but will give error on large columns

Table Structure

ID - value
1 - wanted to confirm her order for the install, adv that the activation date was set 
2 - Edward called to have his order processed. Order processed for 20M, leased modem and self install. Due date for the above to be renewed in end of the year
3 - was unable to transfer, no one answered, customer said that they have mpvs' number 
4- Placed order for modems

ALTER TABLE [TextValues]
add    value_encrypt varbinary(max)
go

UPDATE [TextValues]
SET value_encrypt=EncryptByKey (Key_GUID('SymKey_Encryption'), value)
FROM [TextValues];

"ERROR: "String or binary data would be truncated. The statement has been terminated".

Why do you think TDE is the cause of this T-SQL error? TDE is low-level storage encryption and should not introduce such an error. Add the relevant T-SQL and/or app code that causes the error along with the table DDL. — Dan Guzman, May 06 '20 at 10:35

score 0 · Accepted Answer · answered May 06 '20 at 12:09

Your question is not related to TDE. TDE is a low-level storage encryption option that encrypts data at rest (i.e. in database files) transparently without application code changes.

The ENCRYPTBYKEY T-SQL function encrypts data programmatically. This function returns a varbinary(8000) result. You will get a truncation error when the encrypted value exceeds 8000 bytes so this cannot be used to encrypt large values.

If your goal is to protect data at rest, TDE is the easiest to implement as no code changes are required. Data can be further protected over the wire with TLS encryption.

SQL Server 2016 TDE Encryption on big text column

1 Answers1