Encrypt Sensitive Data in SQL Server

Data security is one of the top priorities for a database administrator.

Hackers are always on their toes to grab any bit of customers' personal details,

be it his account number or his contact details. Leakage of such information can

clout a doom for any DB administrator.

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 set =


EncryptByPassPhrase('password', )

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



select


,name,


convert(bigint,convert(varchar(100),


decryptbypassphrase('password',))) as


from

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 set =


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.