Encryption builtin functions in SQL Server have no known issues and, if used properly, they will produce the expected results. However, if they are used incorrectly, it can be hard to figure out what exactly is the problem, so in this post I am going to collect some hints about what to look for in the malfunctioning code.
There are two main types of encryption code related issues that I have seen popping up in forums; they usually are described as "encryption errors", which is not what they really are.
Issue 1: Corruption of encrypted value
This issue happens when the output of encryption is corrupted when stored or passed around by code. This happens because the encryption blob is truncated during a conversion or because the column in which it is stored is shorter or has the ANSI_PADDING option set to OFF. For determining the size of a column that should hold encrypted data, see this post.
Symptom: Decryption of corrupted encryption blob will return NULL.
How to debug this? Print the output of the encryption function, before it is processed in any way - this is the original encrypted blob value. Then also print the blob after each conversion or assignment; if it is inserted into a column, select back the inserted value. Any difference from the original blob value will indicate where the error happened. Most often, you don't even need to do a byte by byte comparison and you can instead just compare the lengths of the blob at different stages - you can get the length using the datalength() builtin.
Issue 2: Incorrect conversion of the decrypted value
This issue happens when encrypting a Unicode string and when later, after decryption, we try to convert the binary decryption output to a non-Unicode value. Or vice-versa. There is nothing wrong with the encryption or decryption in this case, which can be determined by following the debugging steps for Issue 1. It is easy to get this issue because the difference between nvarchar (Unicode) and varchar (ASCII) is the single letter n, which you may fail to type in.
Symptom: Decrypted data is displayed as garbage.
How to debug this? Compare the type of the data you compress and verify that the decompressed blob is appropriately converted back to that type.