I saw their smiling faces on the latter pages of the comic books that I enjoyed in my youth. The whole family of strange looking, and yet extremely happy, creatures called Sea-Monkeys. Next to the extraordinarily intriguing x-ray glasses it was the most coveted oddity available in kid-dom.
It was my extreme pleasure one day to discover that a packet of Sea-Monkeys were in my possession. I eagerly opened the paper envelope and dumped its dust-like contents into a glass of water. I studied carefully each day on how this mysterious cryogenic material would turn into the beings that grinned so fervently from my comic books. Excitement overcame me when I noticed that there were very tiny shrimp tossing about. I had to squint tightly; but they were there.
It is through carefully studying the nearly microscopic materials that floated within that glass to realize that they were living beings rather than random particulate. Taking the opportunity to contemplate some of the minute details of an item can increase its enjoyment. It also can reveal secrets to how something can be used in a more creative manner. The variance of hashes that are generated from SQL Server encryption methods are among these minute details.
Consider the output of encrypting the plain text value of “My Sensitive Value” by using the HashBytes method. The algorithm options that are available are MD2, MD4, MD5, SHA and SHA1. With the execution of the following statement we can view the hash values for each algorithm:
HASHBYTES(‘MD2′,’My Sensitive Value’) as MD2,
HASHBYTES(‘MD4′,’My Sensitive Value’) as MD4,
HASHBYTES(‘MD5′,’My Sensitive Value’) as MD5,
HASHBYTES(‘SHA’,’My Sensitive Value’) as SHA,
HASHBYTES(‘SHA1′,’My Sensitive Value’) as SHA1
The following are the results of this statement:
The uniqueness of these hash values are the result of the algorithm that is used. If the HashBytes method were to be executed a second time the method would return the identical hash values. This is valuable since the HashBytes method is a one-way encryption method. To reveal the contents of this hash a plain text value would have to be hashed and then compared to a previously hashed value.
If consistently returning the identical hash was not the behavior of this method one-way encryption would not be possible. This behavior is also a vulnerability to this method since patterns can be identified in the body of data that contains these hash values.
Appending your plain text with a unique value, such as a primary key value, before producing a hashed value is called salting. This method can be used to simulate a unique hash value for each individual occurrence of the plain text.
In the study of the EncryptByKey method, for the plain text value of “My Sensitive Value”, we will use a symmetric key. The algorithm options of symmetric keys are DES, TRIPLE_DES, TRIPLE_DES_3KEY, RC2, RC4, RC4_128, DESX, AES_128, AES_192 and AES_256. In the statement below we will select a symmetric key that uses the AES_128 algorithm:
OPEN SYMMETRIC KEY SymKey
DECRYPTION BY PASSWORD = ‘MyStr0ngP@ssw0rd’
SELECT ENCRYPTBYKEY(KEY_GUID(‘SymKey’),’My Sensitive Value’)
CLOSE SYMMETRIC KEY SymKey
The following is the results of this statement:
(Due to the length of the hash I have truncated them for demonstration purposes)
1st execution: 0x0082145786AF1C7DEC691ED…8A5EBF6A25523079AFAB46D4
2nd execution: 0x0082145786AF1C7DEC691ED…EB8AB300B87B81378635AA9E
Unlike the hash values produced by the HashBytes method, a different hash value is returned for the same algorithm with each execution. The specifics on how these hash values are determined and why they are different after each execution varies depending upon the algorithm selected. For this example the AES_128 algorithm performs multiple rounds of transformation before presenting its final hashed value.
The hash values produced by asymmetric keys will produce similar variances in hashed values as symmetric keys. The algorithms available for asymmetric keys are: RSA_512, RSA_1024 and RSA_2048. Understanding the hashing behaviors of these algorithms aid tremendously in the decision of which one to utilize in your encryption efforts.
While details of encryption, such as the variance of hashed values, may not be as entertaining as a glass full of Sea-Monkeys it does reveal a world otherwise unseen by the casual observer. This world, when revealed, can hold the key to more effectively understanding and utilizing the features of SQL Server.