Encryption

A common request in the days of SQL Server 2000 was for a way to encrypt data inside the columns. Industries like healthcare and banking would typically have to encrypt their sensitive data. With the advent of laws like Sarbanes-Oxley, all publicly owned companies must encrypt sensitive data such as social security numbers or passwords. Developers would typically push the functionality into the application rather than attempt to write an extended stored procedure. Each application then would have its own logic for the encryption and decryption. In SQL Server 2005, the functionality is built into the core database engine and can be used with minimal effort.

Creating the Certificate and Key

The first question that security individuals ask when encrypting data is where you want to store the keys for encryption. Essentially, the key allows encryption and decryption of the data. SQL Server stores a hierarchy of keys. The top of the hierarchy is a database instance master key, which is protected by the Windows Data Protection API. Beneath that, each database can have a master key that protects all the individual keys that can be created.

Encryption in SQL Server 2005 is done by first creating a certificate, then creating a master key in the database, and finally creating an individual key to do the data encryption that uses a certificate. Last, you would use a series of functions to encrypt the data. The last sentence is worded very carefully because there is not a switch in the table designer that you can use to encrypt a column. Instead, you encrypt the data as it goes into the table using built-in system functions.

In SQL Server 2005, two different types of encryption keys can be used: symmetric and asymmetric. Symmetric key encryption uses the same key to encrypt and decrypt the data. While this is less secure, it performs much faster than asymmetric key encryption, which uses a different key for encryption and decryption. With asymmetric key encryption, you would lock the door with one key and then pass out a different key to anyone who needed to open the door. With symmetric encryption, you would essentially put the same key under the door mat and allow everyone to use it. You should never hand this key to anyone, because it's the only one to the house.

As mentioned earlier, there is a noticeable performance degradation when encrypting and decrypting data. It's not nearly as noticeable with symmetric key encryption, though. There is a compromise to keep your data secure with both types of keys. That solution enables you to encrypt your session key with the asymmetric key and then encrypt your data thereafter with the symmetric key. Because of the slight performance problem, you should never encrypt all of your columns in a table. For example, there's little reason to generally encrypt the data in the FirstName or Gender column.

Let's take an example to walk you through how data is encrypted and decrypted. This example can be downloaded from this book's Web site at www.wrox.com. First, create a fresh database called EncryptionExample. Create a login called LowPrivLogin with a password of encryptdemo08. Then grant the login access to the EncryptionExample database. You can perform all of these steps through the Management Studio interface or by using these three lines of code:

 CREATE DATABASE [EncryptionExample] CREATE LOGIN LowPrivLogin WITH PASSWORD = 'encryptdemo08' CREATE USER LowPrivLogin FOR LOGIN LowPrivLogin 

Create a table that you'll use throughout this example in the dbo schema. The table will hold fake credit card information. Notice that the credit card number is stored as a variable binary column. This is because you're going to use this column to store encrypted data.

 CREATE TABLE dbo.CustomerCreditCards           (CustomerID INT PRIMARY KEY,            CardNumber varbinary(256)) 

To create the key, you cannot use the Management Studio GUI interface; you have to use T-SQL. You can use Management Studio to delete the key, but that's the only functionality available under the Security tree for a given database. First, create a master key for the database by using the CREATE MASTER KEY syntax:

 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'EncryptionExampleMasterKey08$' 

Next, you will want to protect your other keys with a certificate. While logged in as a dbo, run the CREATE CERTIFICATE statement that follows. The subject is not needed but is handy metadata that can tell an administrator what the use of the key is for. Remember the name of the certificate, because you're going to use it later.

 CREATE CERTIFICATE [CertSymetricKey]   WITH SUBJECT = 'User defined subject. This key will protect the secret data.' 

With the certificate created, create a symmetric key using the CREATE KEY syntax, as shown below. This syntax accepts a number of algorithms, including triple DES, AES 128, and RC4 to name just a few. There are nine algorthims in all, including several variations of the same. Note that in the following syntax the key is protected with the CertSymetricKey certificate. There are other ways of doing this as well. For example, you could encrypt the symmetric key with a password or another key.

 CREATE SYMMETRIC KEY [SecretSymmetricKey]   WITH ALGORITHM = TRIPLE_DES --AES_128 Fine too   ENCRYPTION BY CERTIFICATE [CertSymetricKey] 

Encrypting the Data

You now are ready to begin encrypting your data. For the purpose of this example, remain logged in as a dbo account that created the keys. If you do not have access to the keys, you will not be able to see data. We'll cover that aspect of encryption momentarily.

The first thing to do is use the symmetric key created earlier by issuing the OPEN SYMMETRIC KEY syntax. You specify the key name and specify that you wish to decrypt the key with the certificate that you created earlier as well. This key will remain open until your session expires or you issue the CLOSE statement.

 OPEN SYMMETRIC KEY [SecretSymmetricKey]     DECRYPTION BY CERTIFICATE [CertSymetricKey] 

The next code block is a little more complex but is still not too bad. In this block you get a unique GUID for the symmetric key created earlier by using the key_guid function. If you did not have permission to access the key, it would return NULL and would be caught by the error trap later in the block of code. The entire point of retrieving the GUID is that it's required for the encryptbykey function later in the code block. This function is actually what does the encryption in our case. You pass to that function the GUID for the key and the data you wish to encrypt.

 DECLARE @Key_Guid AS UNIQUEIDENTIFIER SET @Key_Guid = key_guid( 'SecretSymmetricKey') IF(@Key_Guid is not null) BEGIN INSERT INTO dbo.CustomerCreditCards VALUES ( 1, encryptbykey(@Key_Guid, N'4111-1234-1234-5678')) INSERT INTO dbo.CustomerCreditCards VALUES ( 2, encryptbykey( @Key_Guid, N'4111-9876-7543-2100')) INSERT INTO dbo.CustomerCreditCards VALUES ( 3, encryptbykey(@Key_Guid, N'4111-9876-7543-2100')) END ELSE BEGIN   PRINT 'Error retrieving key GUID' END 

This will insert three encrypted credit card numbers. If you select against the table, you will see that the data for that column is all in binary:

 SELECT * FROM dbo.CustomerCreditCards 

If you wish to decrypt the data, one of the functions that you can use is decryptbykey, as shown in the following code. This will use the key you opened earlier to decrypt the data. If the key that was opened matches the key that encrypted the data, you will be able to see the data unecrypted.

 SELECT CustomerId,        convert( NVARCHAR(100), decryptbykey(CardNumber)) as 'CardNumber'     FROM dbo.CustomerCreditCards go 

To close the key, you can use the CLOSE syntax, naming the key that you wish to close:

 CLOSE SYMMETRIC KEY SecretSymmetricKey 

At this point, the data viewed earlier will return NULL. Prove this out by rerunning the same SELECT statement that you ran previously with the decryptbykey syntax. This is also the behavior if you try to open the wrong key to view the data or if you don't have permission to use the key. It's worked for you in this example because you have dbo rights to the database. With the data encrypted, only users that had access to your certificiate and the key would be able to see the data. To grant a user rights to see the data, you would have to use syntax as shown below:

 GRANT CONTROL on certificate::[CertSymetricKey]  TO [username] go GRANT VIEW DEFINITION on symmetric key::[SecretSymmetricKey] TO [username] 

These may be more permissions than you would be willing to permanently hand out to a user. The method around this is to grant these rights only to a given account and tempoarily escalate the rights to the user by using the EXECUTE AS statement.

Creating a Helper Function Example

For usability, you may decide to create a function to help users decrypt the data. You can use context switching to give the user rights to the certificate and key for a short duration. In the following function, you use the decryptbykeyautocert function to combine the OPEN SYMMETRIC KEY and decryptbykey functionality.

 CREATE FUNCTION dbo.udfDecryptData ( @InputValue VARBINARY(256)) RETURNS NVARCHAR(20) WITH EXECUTE AS 'DBO' AS BEGIN RETURN convert( NVARCHAR(50), decryptbykeyautocert( cert_id( 'CertSymetricKey' ), null, @InputValue )) END 

For the purpose of this example, you'll allow the LowPrivUser you created earlier access to the function and to the credit card data by using the following code:

 GRANT EXECUTE ON [dbo].[udfDecryptData] TO [LowPrivUser] GRANT SELECT ON [dbo].[CustomerCreditCards] TO [LowPrivUser] 

You're now ready to use the function. Either sign off as the dbo user and sign back in as the owPrivUser, or use the EXECUTE AS syntax that follows to simulate the user. The following code should look much like the SELECT statement used earlier, but now you're using the user-defined function you created.

 EXECUTE AS USER = 'LowPrivUser' SELECT CustomerId,        dbo.udfDecryptData( CardNumber ) as 'CardNumber'     FROM dbo.CustomerCreditCards REVERT 

This will display the data unencrypted, and you would simply need to grant users rights to the function o enable them to see the data. As you can imagine, this does open a slight can of worms, though, as it makes the data a little too easy to decrypt, but you did add layers of security and obscurity.