Encrypting Your Valuable Data With SQL Server 2005: Part 2

Asymmetric Keys

An Asymmetric Key consists of a private key and a public key. Each key can decrypt data encrypted by the other key. Even though these keys are provided with a high level of security, they are resource intensive. Hence, Asymmetric Keys are not for routine use. An Asymmetric Key can be used to encrypt a Symmetric Key for storage in a database because it is not a routine operation and because it needs a higher level of security.

To create this key, you will use the CREATE ASYMMETRIC KEY function.

CREATE ASYMMETRIC KEY asyKey1
WITH ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = 'sqlserver'

If you didn't specify a password, it will be encrypted using the Database Master Key. Encryption and decryption can be done as follows.

DECLARE @Encryptvalasym varbinary(MAX)

SET @Encryptvalasym = EncryptByAsymKey(AsymKey_ID('asyKey1'), 'EncryptedData')

SELECT CONVERT(varchar(max),DecryptByAsymKey(AsymKey_ID('asyKey1'),
@Encryptvalasym,N'sqlserver') )

Encryption and decryption of an Asymmetric Key is costly compared to a Symmetric Key. It is not recommended when working with large datasets such as user data in tables.

 EncryptByPassPhrase

Apart from the above mechanisms, there are some other simple encryption methods. One of them is EncryptByPassPhrase. This function will encrypt data using a supplied pass phrase. A pass phrase is a password that includes spaces. This method has the advantage of letting you use a meaningful phrase or sentence that is easier to remember than a comparably long string of characters.

DECLARE @Passphrase varchar(128), @Mytext varchar(128);
DECLARE @passphasekey as varbinary(max)
SET @Passphrase = 'This is my PassPhrase Text for Encrypting';
SET @Mytext = 'My Clear Text'
SET @passphasekey = EncryptByPassPhrase(@Passphrase,@Mytext)

-- Decrypting Data by DecryptByPassPhrase
Select convert(varchar(max),DecryptByPassPhrase(@Passphrase,@passphasekey) )

The above code will give you the encrypted value as well as the previous value.

HashBytes

Another important encryption method is HashBytes. You cannot decrypt the value that was encrypted using this method, but you can use it to save passwords with the encrypted value. When you want to verify it, you can encrypt the entered text against the saved value. This method supports the MD2, MD4, MD5, SHA, and SHA1 encryption algorithms.

SELECT HashBytes('SHA1', 'Clear Text')

Limitations

When you are selecting an encryption method, you need to consider two things:

1. Performance.
2. The length of the data that is going to be encrypted.

Whichever encryption method you use, you will have to forgo performance to encrypt data. Nevertheless, you can minimize the adverse effects by selecting the appropriate technique for data encryption.

There is a limit to the length of the data that can be encrypted. A blogs.msdn.com posting titled "SQL Server 2005 Encryption — Encryption and Data Length Limitations" discusses this issue in detail. In fact, the article suggests not using the RC4 algorithm. If the length of the data you want to encrypt exceeds the limitation of SQL Server 2005 encryption, you can use a workaround. To encrypt the value, partition the data field into several parts, encrypt each part separately, then combine and save them in a single field. To decrypt the value, separate the encrypted parts and decrypt each one individually, then combine them again to get the original value. You can write a user-defined function to achieve this.