Encrypting Your Valuable Data With SQL Server 2005: Part 2

After reading Part I in this series, you will have an architectural understanding of data encryption in SQL Server 2005. In this article, we will discuss the real implementation of it.

After generating a Service Master Key (SMK) and a Database Master Key (DMK), you have to build encryption keys to encrypt your data. If you can recall the encryption hierarchy, Certificates, Symmetric Keys, and Asymmetric Keys are what we use to encrypt data. Apart from these, there are two other methods for data encryption—EncryptByPassPhrase and HashBytes—discussed at the end of this article.

Certificates

A certificate follows the X.509 standard and supports X.509 V1 fields. When creating a certificate, there are many optional parameters you can provide. However, you can create a basic certificate with the following parameters.

CREATE CERTIFICATE CertificateTest1
ENCRYPTION BY PASSWORD = 'CERTP@$$w0d'
WITH SUBJECT ='This is Test certificate 1',
START_DATE = '12/1/2006',
EXPIRY_DATE = '12/31/2006'

The above script will create a certificate called CertificateTest1 with the given encryption password. It will be valid from Dec. 1 to Dec. 31, 2006, only. In addition, to create the certificate you have the option of altering it, dropping it, and backing it up by using the ALTER CERTIFICATE, DROP CERTIFICATE and BACKUP CERTIFICATE commands, respectively. Even though there is no RESTORE CERTIFICATE command, you can use CREATE CERTIFICATE to restore a backup certificate you created with the BACKUP CERTIFICATE command. When using DROP CERTIFCATE, you will not be allowed to drop the certificate if any keys are encrypted with it; instead, you will receive an error message:

The certificate cannot be dropped because one or more entities are either signed or encrypted using it.

If you don't provide an encrypted password parameter, the certificate you create will be encrypted by DMK. If you don't provide a START_DATE and EXPIRY_DATE, the certificate's START_DATE will default to the date it was created and the EXPIRY_DATE will be exactly one year from the START_DATE. An example follows.

CREATE CERTIFICATE CertificateTest2
WITH SUBJECT ='CertificateTest2'

To encrypt plaintext values, you can use the EncryptByCert and DecryptByCert functions. For both functions, you need to supply the ID of the certificate. If you don't remember the ID of the certificate, you can use the Cert_ID function to return the ID of certificate by passing the name of the certificate to it.

DECLARE @Encryptval varbinary(MAX)
SET @Encryptval = EncryptByCert(Cert_ID('CertificateTest2'),'scott')

Will return:

0xD5F7ECAC2FEFFB728494C6716B9BA324D2D087DF2D3D6F03460F39D9EF99C349F2EDF7D399695FF64762
8E924F71F147203BC194655972D84BB6CB7D0C87327991B1EE6C5FA17DDF0552D2781D6AF429D8092327E2
DB298372B281192502964421B637BBA8213F872F1B03000E2BD25A256B0937B5DA22C8B588A297368DA3B0

The above chunk of data will be stored in the password column and matches with password "scott."

Similarly, you can use DecryptByCert to decrypt the encrypted values.

SELECT CONVERT(varchar(MAX),DecryptByCert(Cert_ID('CertificateTest2'),@Encryptval ) )

If a certificate is encrypted with a password, you need to provide the password when decrypting it.

To create a certificate, you need to have permission on CREATE CERTIFICATE. You can find all the information for a certificate in the sys.certificates system view.

Symmetric Keys

A Symmetric Key is a one key that is used for both encryption and decryption. This is a fast, commonly used method for encryption. As with a certificate, many parameters are available when creating Symmetric Keys. In most cases, we only need a limited set of parameters. You can refer to Books Online for all the parameters.

CREATE SYMMETRIC KEY TestSymKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE CertificateTest2

You can specify an encryption algorithm for a symmetric key. DES, TRIPLE_DES, RC2, RC4, DESX, AES_128, AES_192, and AES_256 are the encryption algorithms available for SQL Server data encryption. AES encryption algorithms are currently supported only on Windows 2003. If you are using Windows 2000 or Windows XP, you do not have access to AES encryption algorithms from SQL Server. If you try to use an AES encryption algorithm, you will get an error:

Either no algorithm has been specified or the bit length and the algorithm specified for the key are not available in this installation of Windows.

You can encrypt a Symmetric Key using PASSWORD, SYMMETRIC KEY, ASYMMETRIC KEY, or CERTIFICATE.

The ALTER SYMMETRIC KEY and DROP SYMMETRIC KEY commands are also available to improve maintenance.

After the creation of a Symmetric Key, it is just a matter of using it. There are three functions you can use with Symmetric Keys: Key_GUID, EncryptByKey, and DecryptByKey.

OPEN SYMMETRIC KEY TestSymKey
DECRYPTION BY CERTIFICATE CertificateTest2;

DECLARE @Encryptvalsym varbinary(MAX)
SET @Encryptvalsym = EncryptByKey( key_guid('TestSymKey'),'scott')

SELECT convert(varchar(max),DecryptByKey(@Encryptvalsym))

Before using SYMMETRIC KEY, you need to open it by using the OPEN SYMMETRIC KEY command. To encrypt data you need to pass the GUID of the Symmetric Key. However, to decrypt it you don't have to pass the GUID. Instead, you have to open the encrypted Symmetric Key using the correct decryption method and value.

To increase the security of Symmetric Keys, you can add an authenticator during encryption. You have to use the same value during decryption.

OPEN SYMMETRIC KEY TestSymKey
DECRYPTION BY CERTIFICATE CertificateTest2;

DECLARE @Encryptvalsym1 varbinary(MAX)
SET @Encryptvalsym1 = EncryptByKey( key_guid('TestSymKey'),'scott',1,'SQL Server')

-- Decryption with Symmetric Keys
SELECT convert(varchar(max),DecryptByKey(@Encryptvalsym1,1,'SQL Server'))

In the above example, another authenticator besides the TestSymKey Symmetric Key was used to add extra protection to the data. You will need to remember this extra authenticator when you do an encryption. Best practice is to use a related value and not to change it frequently. For example, you can use your primary key value as your authenticator so you don't have to remember it.

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.

Other Practices

You can use Symmetric Keys to encrypt data for individual users; for example, when you have one table with two sets of user data for two different users. Since each user needs access only to their own set of data, each set of user data can be encrypted with a different Symmetric Key.

All the keys should be kept in a secure place. To put it simply, if you lose a key that was used to encrypt data, you risk losing that data.

Conclusion

Many methods are available for data encryption. You need only select the appropriate one.

I hope these articles have given you a sound understanding of SQL Server 2005 data encryption. Now practice what you learned and write your own scripts and code using these new functions. Then explore the possibilities of using SQL Server 2005 encryption in your projects to protect your valuable data.