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.