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.