[snap] Cryptography limitations

As mentioned before SQL Server 2005 brings several security improvements – one of them being the support for cryptography. The purpose of the built-in cryptiographic functions is to enhance server and/or database security and to provide additional methods in data security (by allowing the encryption of values in columns and variables). The SQL Server encryption hierarchy is well documented in Books Online, and so are the built-in cryptographic functions. What Books Online fail to mention, however, (even after being updated for the third time since the release) is the fact that there are limitations to consider regarding encryption in SQL Server 2005.

One limitation is the size of the clear text that can be encrypted. A limitation that practically renders the built-in functions useless for large values (larger than the available maximum length based on the length of the key), unless the value is encrypted in chunks. The behaviour of the SQL built-in functions is dependent on the CryptoAPI cryptographic functions, specifically the CryptEncrypt function.

Even before the release of SQL Server 2005 these limitations have been discussed, and the author of the article also posted appropriate T-SQL user-defined functions to serve as workarounds. These user-defined functions split the clear text into digestible chunks allowing the use of the built-in functions for larger values (and vice-versa). Which is nice. And – judging by the test scripts provided in that post – reliable as well. But are these functions as efficient as they should be? One thing is almost certain – T-SQL is not as efficient as CLR when it comes to operations on strings, so appropriate SQL CLR functions will perform better. This, of course, is not meant as a critique of the article mentioned above, it's rather a simple statement of facts – the built-in functions are limited in their usability, and the workarounds come with their own limitations as well.

In this Microsoft feedback article the issue regarding the encryptByCert built-in function is resolved as "by design".

Which brings us straight to the point of this post – while the built-in cryptographic functions work well with shorter values (e.g. encryption keys, passwords, credit card numbers, salaries etc.) it would be better to let the client application handle the encryption and decryption of larger values (e.g. longer text values). The encryption keys, however, could still be safely stored (encrypted, even) on the server.

ML