Call now: (800) 766-1884  


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA

 SQL Server Scripts
 Scripts Menu




  SQL Server Tips by Gama and Naughter

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, one-way or two-way, 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 UFD'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: one-way encryption, two-way 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: 0-9761573-2-2
Joseph Gama, P. J. Naughter  


Burleson Consulting Remote DB Administration







Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.

Burleson Consulting
SQL Server database support


Copyright 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter