Encryption techniques in SQL Server 2005

Eric Brown is the author of SQL Server 2005 Distilled, recently published by Addison-Wesley. This is an original article rather than excerpt, but is based on material in this book.

In this article, we take a look at how database encryption, a new feature in SQL Server 2005, can be used to protect database objects as well as your data. The first question is why would you bother with encrypting the database? From the perspective of Microsoft product development, providing database encryption is a checklist item for being certified by the common criteria organization. From the perspective of the end user, one can simply scan the recent news to find articles about databases that have been compromised, thru either stolen computers or hacking- database encryption is the last line of defence.

The SQL Server product team didn’t re-invent the wheel to implement encryption in SQL Server; the feature is based on the Windows encryption API, which is robust and powerful.

Cryptography primer

To understand how encryption works we should do a little primer on the mechanics of cryptography. When data is encrypted, it is stored on disk as scrambled text. The application of a key – which is simply an algorithm for decoding the scrambled text – converts the data into something usable.

NOTE:
For a really good source of encryption information, see http://en.wikipedia.org/wiki/Encryption

There are two kinds of cipher algorithms, symmetric and asymmetric. The symmetric method has a private key pair that is owned by both the message sender and recipient. There is no public key. And both parties have a priori knowledge of the other. They both use the same key to encrypt and decrypt messages. In the sample later in this article, we use a symmetric key to illustrate a possible use of encryption. It is not recommended for use on web facing applications, but perfectly acceptable for applications that transmit very sensitive data where you know who is connecting to the data source. We have used this functionality in applications that expose data to SharePoint Portal server through custom web parts and in server to server applications.

When a key is asymmetric, the message sender encrypts the message with a private key that is not shared with the message recipient. Instead, the recipient has a public key that can be used to unlock the message. The public key cannot be used to deduce the private key; thus, the private key holder has a significant level of trust. The advantage of the public key/asymmetric model is that the private key is unknown to the client. This is more secure because the key doesn’t need to be sent to the client. By contrast the symmetric key model requires the key to be sent to the recipient-and usually some kind of password for opening the key. . The current TLS and SSL models employed on socially acceptable websites use the public key model. Wikipedia has an excellent article on it. In the end, the SSL model works on the internet because the server doesn’t necessarily know “who” is connected.

SQL Server 2005 supports the following asymmetric cipher algorithms: RSA, RC2 (Ron’s Code 2), and RC4 (Ron’s Code 4). The RSA protocol has been widely adopted and is used heavily in e-commerce-type transactions. I recommend reading about the protocols in some depth and having company-wide agreement about their usage. The RC2 algorithm has been exposed to the Internet in the form of a Usenet group post.

The symmetric cipher algorithms used in SQL Server 2005 are Data Encryption Standard (DES), Triple DES, DES-X, and Advanced Encryption Standard (the U.S. government adopted standard).

Getting started with database encryption

SQL Server employees an organizational structure that enables encryption right across the database platform. At the root level, is the Service master key. This can be used to encrypt the database master key, which in turn can encrypt certificates and asymmetric keys. This illustration from the SQL Server Book Online illustrates the hierarchy better than I could explain it:

In this article we’ll illustrate how to use and setup each of these, as well as some key management techniques.

Service master key

On any particular server instance, the encryption hierarchy starts with the service master key. The service master for a particular instance is generated automatically at installation time. Note that SQL Server does not include the database master key “auto-magically” in a database backup or recovery plan.

Database master key

SQL Server uses a database master key as the master key for the SQL Server instance- each instance will have it’s own master key. This is the level in the hierarchy where applications “interact” with encrypted data.

The database master key is not generated or turned on by default. It is a symmetric key that must be created explicitly with the CREATE MASTER KEY DDL statement. In order to execute this statement you will need at least the CONTROL permission:

/*create a master key password*/
USE AdventureWorks
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Mas#w0rd9!0F'
GO

Regardless of whether the database master key is encrypted with the service master key, it is always also encrypted by a password – this is the default behaviour. If you delete the database master key or lose the password, the service master key can recover the database master key and decrypt the data.

Within a given database, the database master key allows decryption of all data encryption keys that were originally created with that database master key. In other words, if you use the master key as the skeleton key for any subsequent cryptography, you will always be able to recover the key and the data.

Consequently, all data encrypted with a key is, by default, accessible when the database master key has been unencrypted, either via password or by the service master key. A database master key that is encrypted with the service master key does not need to be opened explicitly – it will be loaded implicitly if the current server instance possesses that key. Conversely, a database master key that is encrypted only by password and not by the service master key must be opened explicitly by the user before it can be used to decrypt data encryption keys. The explicit opening of keys using a password is done with the OPEN MASTER KEY DDL command. Subsequently, the database master key and all the keys decrypted using it can be closed by using the CLOSE ALL KEYS DDL command. If the database master key was decrypted implicitly using the service master key, it is unnecessary to issue the CLOSE ALL KEYS command:

/*open master key*/
USE AdventureWorks;
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Mas#w0rd9!0F'
GO
/*the following code allows the service instance to open the master
key*/
GO
USE AdventureWorks
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO
/*close your master key*/
USE AdventureWorks
CLOSE ALL Keys
GO

Use of database encryption

There are two basics types of information you can protect in a database: the metadata (meaning the DDL of the objects) and the data itself. We’ll start with some brief coverage of securing DDL. There are a number of use cases for this, especially in scenarios where knowledge of the DDL may tell something about what’s in the data and/or open the system to attack.We’ll then move on to the main focus of the section: securing the data.

Encrypting database objects

Here’s a simple example of encryption in action. With the encryption technology in SQL Server you can obfuscate the DDL used to create stored procedures, views and other database object. Why is this useful? For those whose applications contain sensitive data, the ability to make the DDL unreadable via encryption adds one more layer of protection to the data. Imagine a power user “happening” upon the stored procedure that updates HR records for employee payroll. It would be like hacking the college registrar database and suddenly you are an A student.

We can encrypt DDL simply by appending an encryption clause to the CREATE statement:

CREATE PROCEDURE sp_update_salary
WITH ENCRYPTION
AS
DECLARE @perIncrease AS INT
DECLARE @employeeid AS INT
UPDATE [AdventureWorks].[HumanResources].[Employee]
SET salary= salary *@perincrease
WHERE employeeid =@employeeid  

Now let’s test the encryption by running sp_helptext on the sproc:

EXEC sp_helptext ‘sp_update_salary’;

The result is this:

Another way to get at the object DDL is via the sys.catalogs view. Once again, the encryption prevents us for seeing the code:

USE AdventureWorks;
GO
SELECT definition FROM sys.sql_modules
WHERE OBJECT_ID = OBJECT_ID('sp_update_salary');

Again, the result:

Encrypting data

OK, so we’ve created objects and protected them but how do we protect data? This is when encryption gets interesting. SQL Server provides three different paths to encrypting data:

• Using an asymmetric key directly – the danger here is the password is exposed in the T-SQL
• Using a certificate with a symmetric key – the issue here is performance. It is not well suited for internet facing applications
• Using a certificate with an asymmetric key – this is highly secure and fine for internet facing applications.

The most common and robust method for using encryption is thru the use of a certificate. In the following sample, we’ll use a certificate to encrypt and decrypt data. Additionally we’ll use a symmetric key pair, to illustrate how this can be used. I recommend confining the use of the symmetric key model to intranet applications. In our example, we are modifying the salaries of employees at the fictitious Adventureworks company – a likely application inside the firewall.

Certificates

At the database level, SQL Server uses certificates, like those generated for websites that use Secure Socket Layers. In SQL Server 2000 one had to manually set up the certificate relationship with SQL Server. With SQL Server 2005, this functionality is native. In the following example, we’ll use a certificate to broker communication between a client and the SQL Server database. This approach obfuscates the data and provides a scalable and reliable means for communication. The client in this instance is the SQL Server Management Studio.

To start, let’s create a certificate:

USE ADVENTUREWORKS
OPEN MASTER KEY DECRYPTION BY PASSWORD =’a7e-33p8)P3roz&%3′;
CREATE CERTIFICATE CERT1A WITH SUBJECT= ‘CERTDESCRIPTION’

Looking at the code, you will notice that we have to issue the Open Master Key command before we can take any action. With all the encryption techniques, the opening and closing of keys needs to be accomplished verbosely. Once the key is open, the T-SQL that follows can take advantage of the “open” state. Remember to explicitly close the key when you are done. To close the master key and thus the certificate:

CLOSE MASTER KEY  

Certificates and symmetric keys

To create a symmetric key that will be used by an application we execute the following:

CREATE SYMMETRIC KEY SmptlkUserKey WITH ALGORITHM = DES
    ENCRYPTION BY CERTIFICATE CERT1A;

We use the symmetric key to hide the password and prevent users from having access to the database master key- which is very powerful. In fact, the database administrator could keep the key private from developers! The symmetric key is encrypted using the CERT1A which we created above. We chose the DES alogorithm. SQL Server 2005 supports the following alogorithms: DES, TRIPLE_DES, RC2, RC4, DESX, AES_128, AES_192, and AES_256.

With our symmetric key in hand, we can employ it in an application:

OPEN SYMMETRIC KEY SmptlkUserKey
   DECRYPTION BY CERTIFICATE Cert1A;

update [AdventureWorks].[HumanResources].[Employee]
set salary_encrypted= EncryptByKey(Key_GUID(‘SmptlkUserKey’)
    , cast(salary as varbinary), 1, HashBytes(‘Sha1’, Convert( varbinary
    , employeeid)));
GO
Close ALL Keys;

The interesting part of this query is the use of the EncryptByKey SQL function to encrypt our chosen column (Salary) with the symmetric key. To use the function, the symmetric key must be open for the session. With the session open, calling the EncryptByKey function requires:

1. The GUID value that identifies the symmetric key, which is obtained from the Key_GUID T-SQL function (Key_Guid(Name_Of_Key))
2. The name of the column to be encrypted.

The basic function looks as follows:

encryptbykey(key_guid(MySymmetricKey”, ColumnToBeEncrypted)

In the case of the query above, there are a couple of things happening:

1. We need to cast the float column, salary, into varbinary so it can be encrypted
2. The value 1 says we are using an authenticator
3. The HashBytes function is called to scramble the data into an unreadable format for human eyes The Hash bytes function takes one of several algorithms – MD2 | MD4 | MD5 | SHA | SHA1 – along with the input column.

This isn’t always required – but encrypted data is stored as text and, in this case, I had to convert both the salary and employeeid columns to this format. Let’s check the encryption:

select salary,salary_encrypted from
[AdventureWorks].[HumanResources].[Employee]

The values will look like:

Now to open the real values, simply open the certificate and execute:

select salary,salary_encrypted AS ” Encrypted salary”,
convert(int,DecryptByKey(salary_encrypted, 1, HashBytes(‘Sha1’, Convert( varbinary
    , employeeid)))) AS “Decrypted salary”
FROM [AdventureWorks].[HumanResources].[Employee];

And the results should look as follows:

Just as we used the EncryptByKey function to encrypt the column data, we use DecryptByKey to open it back up again. Simply, feed in the encrypted column and the cipher text, via the HashBytes function – in the example above we used the employeeid as the cipher text. When I wrote this code, I simply copied the EncryptByKey call and mapped the functions.

Key management in SQL Server 2005

With this basic understanding under your belt, we should look at some hard questions. SQL Server “automagically” generates a service master key. How do you protect that key? The answer, according to Microsoft, is to manually backup the certificates. Each organization develops a methodology for managing certificates and encryption. It seems logical that you should back up the certificates on a server at each interval where the server’s state is changed. Taking a realistic approach – dumping the certificates to an unsecured medium and then locking that medium in a physically secure location is impractical. Here are some questions that most organizations must consider:

• How do we generate/regenerate keys?
• What mechanism do we use to share the keys among humans and servers?
• What do we do if a key is compromised?
• What should our general approach to key management be?

As you become familiar with SQL Server 2005, I recommend developing a corporate standard based on both current regulatory rules and a general need to know.

Here’s the DDL for most encryption Admin functions:

–BACKING UP A CERTIFICATES–
USE AdventureWorks
GO
BACKUP MASTER KEY TO FILE = ‘C:\CERTS_BU’
ENCRYPTION BY PASSWORD = ‘a7e-33p8)P3roz&%3’

–RESTORE the master key
RESTORE MASTER KEY FROM FILE = ‘C:\CERTS_BU.txt’ 
    DECRYPTION BY PASSWORD = ‘a7e-33p8)P3roz&%3’,
    ENCRYPTION BY PASSWORD = ‘L5e-378M8)D3%o&1;3’,
    [FORCE]
–checking on your cert’s in the database
select * from sys.certificates
–Back up certs before deleting them—
BACKUP CERTIFICATE CERT1A TO FILE = ‘C:\CERTS_BU23’;
— drop a certificate–
DROP CERTIFICATE CERT1A;
GO

Conclusion

This article has covered only the surface of the uses and techniques involved in database encryption. The Microsoft documentation is useful, but not as deep as one would hope. As I always say, your mileage may vary.

You can contact me at eric@aboutsqlserver.com. If you want to find out more about my book, SQL Server 2005 Distilled, then please visit my site, http://www.aboutsqlserver.com/. There’s a lot of other good stuff on there too ;).