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”.
Advertisement
Answer
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.