SQL Server 2005: How to determine the size of a column that will hold encrypted data

This issue has been addressed before on forums, but with the heavy traffic, it can be hard to find the proper post. So, I’ll provide some explanations here as well.

Note: This article is written with symmetric encryption in mind, but the actual technique would work for asymmetric encryption too. 

When encrypting a piece of data, the encrypted version will always be larger than the original data. This happens because encryption doesn’t compress the data and, in addition to that, SQL Server prefixes the encrypted blob with some data, the most significant being the GUID of the symmetric key used for encryption. It is possible to compute in advance what will be the length of the encryption for a given piece of data, but rather than working with a formula, a safer and simpler method is to just perform the encryption using EncryptByKey and pass the result to the datalength() builtin function. While the contents of the encrypted blob are non deterministic due to the use of a random IV, the length of the blob will be constant if the same method is used for encryption; in fact, the length of the encryption doesn’t even depend on the data that is encrypted – it only depends on the length of that data.

So, when trying to decide the size of a column that is supposed to hold encrypted data, you only need to determine, as you would normally do in the absence of encryption, what is the maximum size of unencrypted data that you are willing to store; once you know this, you can just generate a piece of data of that size, encrypt it, and get the length of the result using datalength() – that will be the maximum length that you should define for the encrypted column.

Although I remember several posts on this topic, I could only find one with a quick search. Fortunately, it’s a very good post from Raul:


Update (later same day): This is the original post from Raul that explains in more detail the formula you could use to determine the length of an encrypted blob without actually encrypting. The blog on which it is posted has been discontinued, but there are some good posts made to it that are worth checking out. And if you are wondering about the blog name, Yukondoit was one of the slogans used on T-shirts around the launch of SQL Server 2005, whose code name was “Yukon”.

Comments (0)