


SQL Server Tips by Burleson 
Applications of XP_CRYPTOAPI
XP_CRYPTOAPI has 57 functions for cryptography. The best choice of
an encryption algorithm depends on the requirements for security,
symmetric vs. asymmetric, oneway or twoway, speed, block size, key
size, etc. Having a high number of encryption algorithms to choose
from is a tremendous help because it makes it easier to pick the
best fit. And quite often, more than one encryption algorithm is
used in one database because the block size might be larger than the
size of some data types.
Wrapping each function with a UDF is the simplest way to deal with
so many functions but there are scenarios that require more
versatility. Another option is to have the functions for encryption
and decryption for a certain algorithm in the same UDF and one
parameter to decide which one to apply. This solution would reduce
the number of UDF's to handle these functions to half. A more
compact solution would be to group the algorithms by type and have
one parameter for choosing the algorithm to use. Another parameter
for choosing encryption or decryption could also be added.
The following examples are based on the idea of grouping algorithms
in three categories: oneway encryption, twoway encryption
(symmetric), asymmetric encryption and message authentication. For
practical reasons the groups were split in six subgroups: encrypt,
decrypt, hash, HMAC, asymmetric encryption, sign and verify. This
will avoid an extra parameter for encryption/decryption or
signing/verifying. The Public Key Management functions (XP_DSA_EXPORT,
XP_RSA_EXPORT_ENCRYPTKEY and XP_RSA_EXPORT_SIGNKEY) should be called
directly because they return one value and have no input parameters.
The above book excerpt is from:
Super SQL
Server Systems
Turbocharge Database Performance with C++ External Procedures
ISBN:
0976157322
Joseph Gama, P. J. Naughter
http://www.rampantbooks.com/book_2005_2_sql_server_external_procedures.htm 