Even if a firewall and database security have been employed, it's always a good practice to store sensitive information in an encrypted format. With SQL Server 2005, there are functions and methods provided for data encryption and decryption. These methods were not provided with the earlier version i.e. SQL Server 2000, where you had to write your own functions for encrypting data.

Data encryption in SQL Server 2005 can be done either by using password mechanism or by making use of keys and certificates. In this article, we will look at both mechanisms for data encryption for storage, and then the decryption of the encrypted data for information retrieval.

Encryption by PassPhrase
Doing encryption by passphrase is the easiest approach. But let's first create a table in a demo database, say demodata, for employing encryption/decryption. Name the table as 'users', and create four columns-id, name, card_num and encrypt_num in it. The following SQL script creates the table:

create table users
(
id varchar(16) not null primary key ,
name varchar(50) not null,
card_num varchar(50) not null,
encrypt_num varbinary(MAX)
);

After encrypting the card_num column, we can see the result of encrypted card number by running the select query

Now, with the EncryptByPassPhrase() method we can encrypt the data in card_num column. While using this method we require a "PassPhrase", which is nothing but a password that is required to encrypt the data. During decryption the same password has to be passed for data retrieval. The syntax for the method is EncryptByPassPhrase('password','data'), where password is the passphrase and data is the information that is to be encrypted. Suppose we have entries for uuid, name and card_num in the users table, we can run the following script to update the table to encrypt the card_num, and store the converted data in the encrypt_num column. The encrypt_num column is of type varbinary, which holds the data that is encrypted from varchar datatype.

use datademo
update [users] set [encrypt_num] =
EncryptByPassPhrase('password', [card_num])

The above script converts the data from the card_num column and stores the result in encrypt_num column.

During encryption we used the password as the PassPhrase. Now for decryption, we will have to pass the same PassPhrase.

Supplying a different PassPhrase would result in non-retrieval of the result.

Using DecryptByPassPhrase() method, we will decrypt the data in the column encrypt_num to retrieve the original data. The following script shows a new column, which has decrypted the data from the encrypt_num column, and displays that in the new Decrypted Card Num column.

Decrypted' shows decrypted info from the 'encrypt_num'. The same symmetric key is used for encryption and decryption

use [datademo]
select
[id],name,
convert(bigint,convert(varchar(100),
decryptbypassphrase('password',[encrypt_num]))) as
[Decrypted Card Num] from [users]

In real scenarios, we would be employing the EncryptByPassPhrase() method directly through data access object codes either in Java or .NET, and we won't be saving original card number in the table as we did here in this article. Similarly, for decryption the DecryptByPassPhrase() method will be done at program level and not at the database level. This mechanism is convenient for data encryption and decryption, but each time we require PassPhrase for both the processes. This means that PassPhrase is still vulnerable, as we have to store it in some procedure for data
access objects.

Encryption by Keys
The limitation of encryption by passphrase methods is that we have to supply the password or passphrase each time the data has to be accessed. But, if we encrypt our symmetric key with a certificate then we won't have to pass the passphrase each time. To create a key or its certificate, we must first create or open the master key for the database. The following command creates a master key:

create master key encryption by password = 'password';

Now, we can create a certificate and then a symmetric key that is attached to that certificate. The following SQL script creates the certificate 'DemoCert' and a key 'DemoKey' associated with that certificate.

create certificate DemoCert with subject = 'Demo Certificate';

create symmetric key DemoKey with
algorithm=AES_256 encryption by certificate DemoCert;

Now that we possess a key, we can do encryption using the EncryptByKey() method. First of all delete contents from the encrypt_num column in the 'users' table that we earlier used for storing the encrypted data of the column card_num. Once we have deleted the contents, we can again encrypt the data from the card_num column and store the result in encrypt_num column by using the EncryptByKey() method.

open symmetric key DemoKey decryption by certificate DemoCert;
use datademo
update [users] set [encrypt_num] =
EncryptByKey(Key_GUID('DemoKey'), card_num)

The above script opens up the symmetric key 'DemoKey' that is associated with the certificate DemoCert. While updating the column for encryption we pass the same key as a parameter to the Key_GUID variable, which is one of the parameters of the EncryptByKey() method. Now while decrypting, all you have to do is to open a session for the key DemoKey, and decrypt the encrypted data. Same key is needed for the encryption and the corresponding decryption process.

open symmetric key DemoKey decryption by certificate DemoCert;
use datademo
select ID,
encrypt_num,
cast(DecryptByKey(encrypt_num)
as varchar(16)) as "Decrypted"
from users;

This is a better method, as we do not have to pass the password for the process of encryption/decryption. All we have to do is to add an 'open symmetric key' phrase to each data manipulation command while programming, i.e. to each update, insert, or select statement. The key remains open till we explicitly close it. Here also, in real world scenario, we won't be saving any original data in the database table, but will be saving the encrypted data by use of keys. The DB admin may have created these keys.

Data encryption is the key to data security. With SQL Server 2005 you can secure your data by using the encryption or decryption functions that it provides.