Encryption Primer for SQL Server Data

More
and more frequently, you hear about some company that has leaked confidential
data to someone that should not have it. Sometimes this happens because someone
that is authorized to see the confidential data mistakenly provides it to
someone that should not have it, but other times a database is compromised and
the confidential information is stolen. As a database developer or DBA there is
not a lot you can do about someone sharing confidential data, if they have
legitimate access to the data, except to maybe suggest that they go to a
confidentiality training session. However, we can help minimize the risks of
someone breaking into our database and browsing around to find confidential
information like credit card information, social security numbers, etc. In this
article, I will explore how you can use SQL Server features to encrypt your
confidential data.

Defining your Encryption Requirements

Before you go off and start encrypting
data in your database you need to define your requirements for securing your
confidential data. To do to this you need to ask yourself a few questions. You
should probably ask many different questions, but here are two of the most
important questions:

• How do you plan to manage encryption keys?
• Who should be able be able to decrypt your confidential data?

The key that you use to encrypt your
data is very important to secure. You do not want people to be able to browse
code and find it, as well as you should probable not pass it in clear text
where it can be found. In addition, you do not want to lose it. If you lose the
key, then you have lost the mechanism to decrypt your encrypted data.

Normally when you talk to Security
Officers, they will say only the people with a "Need to Know Basis"
should be able to see unencrypted confidential data. This means if you are a
DBA or a database owner of a database that contains confidential data you most
likely should not be able to decrypt the data. If this truly is your
requirement then you need to hide the encryption keys from the DBA and database
owners.

Keep your encryption requirements in
mind while you read the rest of this article. Encrypting data using SQL Server
functionality is not as good as it could be, but it is better than having no
encryption mechanism at all.

I am going to show you two different
ways to encrypt a column in a database. Keep in mind these are not the only
ways to encrypt data using SQL Server; these are just a primer for encrypting
SQL Server data. In order for you to gain a complete understanding of
encryption options in SQL Server, I suggest you review all the encryption
documentation in Books Online.

Encrypting Data with a Key/Passphrase

In order to encrypt data you need to
have a key that will be used to encrypt the data. This first example will show
how you can use a key or passphrase when encrypting and decrypting data in SQL
Server.

CREATE DATABASE EncryptDemo2010;
GO
USE EncryptDemo2010;
GO
CREATE TABLE CreditCardInfo1 (
CardId int,
CardHolderName nvarchar(30),
CardNumberEncrypt varbinary(400),
CardNumber nvarchar(50)
);
INSERT INTO CreditCardInfo1 VALUES (
1, 'Joe Cardholder',
EncryptByPassPhrase(N'Sup3rS3cr3tP@ssw0rd', '1111 2222 3333 4444')
,'1111 2222 3333 4444');
INSERT INTO CreditCardInfo1 VALUES (
2, 'Jane Cardholder',
EncryptByPassPhrase(N'Sup3rS3cr3tP@ssw0rd', '9999 8888 7777 6666'),
'9999 8888 7777 6666');
SELECT CardId, CardHolderName, CardNumberEncrypt,
convert(varchar, DecryptByPassPhrase(N'Sup3rS3cr3tP@ssw0rd',CardNumberEncrypt)) as CardNumberEncrypt,
CardNumber
FROM CreditCardInfo1
DROP TABLE CreditCardInfo1;
USE master;
GO
DROP DATABASE EncryptDemo2010;

The first step to encrypting your data
with a passphrase is to come up with a passphrase that you want to use to
encrypt your data. That passphrase is then used with the
"EncryptByPassPhrase" function to encrypt your data. In the script
above I just hardcoded that passphrase, but you could have coded this script
where this passphrase was passed as a parameter. Once the data is encrypted with
a passphrase using the "EncryptByPassPhrase"function, it can then be
decrypted using a passphrase. To decrypt the data you use the
"DecryptByPassPharase" function passing the same passphrase used to
encrypt the data.

Note that passphrase will have to be
passed as a variable, or hardcoded the way I did in the above script. This
creates a large security hole. When you store a passphrase in clear text
whether it is in a script or some program code someone can see it. Therefore,
this is probably not the most secure method to use to encrypt your confidential
data, although it better than not encrypting your confidential data at all.

One of the things you need to be
worried about when you encrypt data only using a key, as I did above, is how
you make sure the key does not fall into the wrong hands. The key is what
allows you to read the encrypted data. Therefore, if someone gains access to
the key that should not have it, he will be able to decrypt data, which was encrypted
using the key. If you are going to use the above method, then you need to
determine how you are going secure the key, to minimize the risk of it be
exposed to someone that should not have it.

Encrypting Data Using a Certificate

An alternative to encrypting data with
passphrase key is to use a certificate. By using a certificate, you do not have
to pass the encryption key every time you encrypt or decrypt a column of data.
Below is an example of how to encrypt data with a certificate.

CREATE DATABASE EncryptDemo2010;
GO
USE EncryptDemo2010;
GO
CREATE TABLE CreditCardInfo2 (
CardId int,
CardHolderName nvarchar(30),
CardNumberEncrypt varbinary(400),
CardNumber nvarchar(50)
);
CREATE MASTER KEY
ENCRYPTION BY password = 'Sup3rS3cr3tP@ssw0rd';
CREATE CERTIFICATE CreditCardCert WITH SUBJECT = 'CreditCard';
CREATE SYMMETRIC KEY CreditCardKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE CreditCardCert;
OPEN SYMMETRIC KEY CreditCardKey
DECRYPTION BY certificate CreditCardCert;
INSERT INTO CreditCardInfo2 VALUES (
1, 'Joe Cardholder',
EncryptByKey(Key_GUID('CreditCardKey'), '1111 2222 3333 4444'),
'1111 2222 3333 4444');
INSERT INTO CreditCardInfo2 VALUES (
2, 'Jane Cardholder',
EncryptByKey(Key_GUID('CreditCardKey'), '9999 8888 7777 6666'),
'9999 8888 7777 6666');
SELECT CardId, CardHolderName, CardNumberEncrypt,
convert (varchar, DecryptByKey(CardNumberEncrypt)) as CardNumberEncrypt,
CardNumber
FROM CreditCardInfo2
CLOSE SYMMETRIC KEY CreditCardKey;
DROP SYMMETRIC KEY CreditCardKey;
DROP CERTIFICATE CreditCardCert;
DROP TABLE CreditCardInfo2;
USE MASTER;
GO
DROP DATABASE EncryptDemo2010;

The first step to encrypting your data
with a certificate is to create a database master encryption key. This key will
be used to encrypt data in the database. The "CREATE MASTER KEY"
statement only needs to be issued once, and is normally created by the database
administrator. It is important not to lose this key. This key should be written
down and stored in a safe place in case you should need it when recovering your
database. You should also backup the database master key, but that is another
topic outside the scope of this article. To understand more about the database
master key and managing it, read Books Online
documentation on the subject.

The next step is to create a
certificate using the "CREATE CERTIFICATE" statement. I my case I
created a certificate named "CreditCardCert". The next step is to
create a symmetric key, which will encrypt by this certificate. The symmetric
key is created using the "CREATE SYMMETRIC KEY" statement. Once the
database master key, certificate and symmetric key have been created you are
ready to encrypt your data.

In order to encrypt and decrypt data
with a certificate you need to first open the symmetric key for decryption by
the certificate. This is done with the OPEN SYMMETRIC KEY statement above. To
allow normal database users or database roles to be able to issue the OPEN
SYMMETRIC KEY STATEMENT you need to grant VIEW DEFINITION rights to the
symmetric key and certificate name, and CONTROL rights to the certificate name.
If the symmetric key is not opened you will get an error when trying to encrypt
data, or the data returned when decrypting it will be NULL.

Once the symmetric key is opened for a
session, the INSERT statements above can use the "EncryptByKey"
function to encrypt the credit card information when that data is stored in the
database. In addition, as long as the symmetric key is open you can SELECT data
from the table and use the "DecryptBykey" function to decrypt the
encrypted data stored in the database. Once the symmetric key is closed, the
underlying encrypted data cannot be decrypted. Although if you do not want to
write code that requires you to open and close the symmetric keys you can use
the "DecryptByKeyAutoCert" SP to automatically open and close the key
like so:

SELECT CardId, CardHolderName, CardNumberEncrypt,
convert (varchar, DecryptByKeyAutoCert(cert_id('CreditCardCert'), NULL, CardNumberEncrypt)) as CardNumberEncrypt,
CardNumber
FROM CreditCardInfo2

Other Encryption/Decryption Consideration

There are many different options for
encrypting data inside of SQL Server using passwords, symmetric keys,
asymmetric keys, and certificates. I have only presented two options here.
Since there are so many different ways to encrypt data in SQL Server, you need
make sure you understand how the encryption keys and certificates are used and
managed so you understand the risks associated with encrypting data using one
way verses another. I would suggest that you review the encryption
hierarchy in Book Online prior to determining what encryption strategy will
work best for your environment. Lastly,
prior to implementing an encrypt/decryption approach into your production
environment, make sure you understand how to backup and recover your encrypted
data should your database and/or server become corrupted.

»


See All Articles by Columnist
Gregory A. Larsen