857
Chapter 34: Data Encryption
34
After the keys are created, they are listed in Management Studio’s Object Explorer under
the database’s Security ➪ Symmetric Keys node.To view information about the symmetric keys using T-SQL, query the sys.symmetric_keys catalog view.Keys are objects and can be altered or dropped like any other SQL Server object.Encryption Algorithms
The algorithm defi nes how the data will be encrypted using this key. There are ten pos-
sible algorithms: DES, TRIPLE_DES, TRIPLE_DES_3KEY, RC2, RC4, RC4_128, DESX, AES_128,
AES_192, and AES_256. They differ in speed and strength.The Data Encryption Standard (DES) algorithm was selected as the offi cial data encryption
method for the U.S. government in 1976, but it can be broken by brute force using today’s
computers in as little as 24 hours. DESX was incorrectly named and won’t be used in future
versions of SQL Server. When you would use a Symmetric key created with ALGORITHM =
DESX, it actually would use the TRIPLE DES cipher with a 192-bit key. The triple DES
(TRIPLE_DES) algorithm uses a longer key and is considerably stronger.The RC algorithms (such as RC2 and RC4) are a set of symmetric-key encryption algorithms
invented by Ron Rivest. They are older, dating back to the mid-’80s, and are fairly easy to
break. These will also be removed in future versions of SQL Server like the DESX. So when
doing new development, do not use the RC and DESX algorithms. Also make the appropriate
modifi cations to older applications that use those two algorithms.The National Institute of Standards and Technology (NIST) approved the Advanced
Encryption Standard (AES), also known as Rijndael (pronounced “Rhine-dahl”), in
November 2001. The 128, 192, or 256 in the algorithm name identifi es the bit size of the
key. The strongest algorithm in SQL Server’s toolbox is the AES_256.SQL Server leverages Windows’ encryption algorithms, so if an algorithm isn’t installed on Windows, then SQL Server
can’t use it. AES is not supported on Windows XP or Windows 2000.Because the symmetric key might be transported in the open to the client, the key
itself can also be encrypted. SQL Server can encrypt the key using one or multiple
passwords, other keys, or certifi cates. A key_phrase can be used to seed the genera-
tion of the key.A temporary key is valid only for the current session and should be identifi ed with a pound
sign (#), similar to temporary tables. Temporary keys can use a GUID to help identify the
encrypted data using the indentity_value = 'passphrase' option.c34.indd 857c34.indd 857 7/31/2012 10:01:46 AM7/31/2012 10:01:46 AM
http://www.it-ebooks.info