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

Symmetric Encryption / Decryption

Symmetric cryptography ciphers are the most common type of encryption algorithm. They are called ‘symmetric’ because the same key is used for both encryption and decryption. This key is often referred to as a session key. Symmetric algorithms can be divided into two categories, ‘Stream Ciphers’ which encrypts data, one bit at a time and ‘Block Ciphers’ which encrypt data in discrete units (called blocks), rather than as a continuous stream of bits.

Block ciphers due to their nature can produce encrypted data, which is larger than the data to encrypt. You should be aware of this issue which declaring SQL variables or column sizes for storing encrypted data. The MS CryptoAPI automatically handles removal of this padding when you perform the decryption process. This size will usually be the next largest modulus of the block size of the cipher chosen. In block ciphers, because data is encoded a block at a time there is the potential for ‘Block Replay’. This is where an adversary can monitor previous encrypted data passing by which it may know some history about. Then unless other special precautions the intruder can simply reinsert another copy of any previous block in the middle of a message, and it will be accepted as ok by the receiver. When a block cipher operates in this mode, it is called ‘Electronic Cookbook Mode’ or ECB. To avoid this problem, a block cipher is usually operated in a so-called ‘Cipher Block Chaining’ or CBC mode. This is where the next block of data to encrypt is XORed with the previous encrypted block before it is encrypted. The previous value is stored in a sample variable, which is feed back into the algorithm. This is normally called a ‘feedback register’. If the data you are encrypting contains a common header each time, then even standard CBC may not be enough, as each encrypted block for the header will encrypt to the same data. To prevent this you can initialize the feedback register with some random data such as a timestamp. This is called an ‘Initialization Vector’. This ‘Initialization Vector’ would then also be included in the data sent to the receiver. The XP’s we implement in this Chapter which use the MS CryptoAPI use CBC mode, but do not allow ‘Initialization Vector’s’ to be set. We leave this as an exercise for the user.

A stream cipher as we have mentioned already operates on a bit at a time. The simplest implementation of a steam cipher is where each incoming bit of the data to encrypt is XORed with a stream of bits from a so-called ‘Keystream Generator’. The receiver can then decrypt the data, by performing the same XOR operation with the encrypted bit stream and the same stream of bits from the keystream generator. In fact if we could produce a completely random stream of bits both in sync at the sender and receiver, then we have a completely secure encryption algorithm. This is called a ‘One-Time Pad’. The problem with this mechanism is that it is very hard to produce truly random data, plus the sender and receiver must have the same random stream available to them, plus none of the bits of the pad can be reused. This need for synchronisation limits its usefulness to low bandwidth connections. In reality the bits generated by the keystream are pseudo-random and are dependent on the session key used.

Each encryption algorithm implemented in this chapter uses the syntax ‘XP_AlgorithmName_ENCRYPT’ and the corresponding decryption XP uses the syntax ‘XP_AlgorithmName_DECRYPT’.

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