Search Knowledge Base
KB #240047: Encrypted values in var char column are randomly decrypting incorrectly
| Type: | Tip |
| Summary: | |
| Values in a varchar column are, seemingly randomly, not decrypting to the proper plaintext value. This issue is associated with the TrimTrailingBlanks property for columns, or with programmatically trimming encrypted values.
|
|
| Additional Information: | |
| It is possible that a resulting encrypted value may contain a space (hex 20) at the end of the value. While this may not be significant for regular plaintext, it is a critical part of the encrypted value. If that value is trimmed then the encrypted value has changed and will not decrypt properly. The space may be trimmed programmatically using a Trim function, or automatically if the TrimTrailingBlanks property on the column is YES. This corrupted value will not decrypt to the original plain-text value.
The TrimTrailingBlanks property cannot be manually modified. But according to the following Microsoft KnowledgeBase article, the TrimTrailingBlanks property for a column is automatically set to NO if an ALTER COLUMN command is issued against the table in which the column exists. KB296559: ALTER COLUMN Sets TrimTrailingBlanks Property to NO The Encryptionizer Column Encryption API’s themselves do not automatically trim any values, nor do they have any control over the TrimTrailingBlanks property. However, if using the Encryptionizer Column Encryption Manager (Col-E) to encrypt a column, the affected table is modified and the TrimTrailingBlanks property is set to NO This only affects varchar fields and variables. nvarchar and varbinary are not affected. |
|
Last modified: 1/13/2016