Encryption Primer for SQL Server Data

June 1, 2010

As a database developer or DBA there is not a lot you can do about a legitimate user sharing confidential data. However, you can minimize the risks of someone breaking into our database and browsing around to find confidential information. This article explores how you can use SQL Server features to encrypt your confidential 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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers