Encryption, Certificates, and Key Management

Let's assume you create a paper request to ask a manager at your company for some information and put it in your company's outbox. The messenger takes the message and heads off to the other building to get the answer for you. Meanwhile, someone notices the messenger leaving and writes his own response. When the messenger returns, he removes the original response and place his own in the inbox. You read the message but have no way of verifying that the sender is really the head office. This is called a "man-in-the-middle" ploy, and electronic signals that travel across untrusted networks are vulnerable to this type of attack.

To protect your applications, you need to be able to scramble the data so that it cannot be read. But to protect yourself from a "man-in-the-middle" attack, you need to have some way of verifying who the original sender is. In the real-world example, you might do one of two things. You could get a third party who puts a special mark on the document to verify that it has not been tampered with. Another method might be to place a special code in the document, and when the head office reads the message, they use that code to scramble the letters in the document. When you receive the document, you use another code to unscramble the message.

The electronic equivalents of these tools are the symmetric and asymmetric key and the certificate. SQL Server 2005 has both of these methods, and you can create them for your developers to use.

Keys

An asymmetric key has two parts. The first part is called the public key, which is a password known to the receiver of the message. The second part of the key is the private key, which is a password known only to the sender. At the core, both of these are just really long strings of numbers. The data is run through one set of numbers to encrypt the data, and through the other to decrypt it. Because only one of the keys are exposed at any one time, it makes the security fairly high. To create an asymmetric key, think of a good password and a name for the key.

Next, select the type of algorithm you want to use when the key encrypts the data. You have three to choose from: RSA_512, RSA_1024, and RSA_2048. Each gains in strength but takes longer to encrypt and decrypt. Here is an example script for creating an asymmetric key called Asym_LogCabinPress in the database with the password of This1sToo Easy:

CREATE ASYMMETRIC KEY Asym_LogCabinPress
WITH ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = 'This1sTooEasy'
GO

The I in the Is part of this password is actually a number 1, because using all characters does not meet the complexity requirements of my test system. You can also create a key from a file, an executable, or even a CLR assembly that contains security code.

A symmetric key is the same on both sides. The data is encrypted and decrypted using the same key. With a symmetric key, you have even more choices for the encoding algorithm:

DES
TRIPLE_DES
RC2
RC4
DESX
AES_128
AES_192
AES_256

This example script creates a symmetric key call Sym_LogCabinPress with the same password I used earlier and the DES encryption algorithm:

CREATE SYMMETRIC KEY Sym_LogCabinPress
WITH ALGORITHM = DES
ENCRYPTION BY PASSWORD = 'This1sTooEasy'
GO

After you have created the keys, you can see them under the Security item in the database on the Object Explorer in SQL Server Management Studio.

Certificates

A certificate is a piece of software that is coded either to a third party (called a certifying authority) that verifies the identity of a principal. To use a certificate from a third party, follow the instructions they provide for your site.

You can also "self-certify," by having SQL Server generate its own certificate. To do that, you need to have a master key for the database. This is a password used to guarantee the certificate. Here is an example of the T-SQL syntax to create a master key:

CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'This1sTooEasy'
GO

You can now create a certificate. In this example, I am creating a certificate called Cert_LogCabinPress:

CREATE CERTIFICATE Cert_LogCabinPress
WITH SUBJECT = 'Self-Certification',
 EXPIRY_DATE = '01/01/2007 v
GO

I have also placed a date on the certificate that sets the expiration, called EXPIRY_DATE. Certificates can also be authorized for certain accounts. Here is another certificate that only the WoodyB principal can use:

CREATE CERTIFICATE Cert_ForBuckWoody
WITH SUBJECT = 'Buck Woody',
AUTHORIZATION WoodyB
GO

DBA 101: Passwords

Using the same passwords or even the same pattern of passwords for anything is a really bad practice in production. If someone were to guess one of the passwords, they would be able to get everything I have created here. For clarity, I am using the same password to focus on the concepts.

To put all this together into a single concept, let's look at a simple example a developer might use. The aim is to insert some data into the database such that it is unreadable to anyone. The process is as follows:

The developer asks you to create keys and certificates.
You provide the developer the names of those objects and the passwords on them.
The developer writes code to encrypt the data using the objects.
The developer writes code to decrypt the data using the objects.

I have already shown you the first step. Let's assume we have given the developer the passwords, and now the developer wants to encrypt some data in a field called FName in a table called Test using the certificate.

To use the certificate, the developers use the EncryptByCert function, which has two parameters: the certificate name and the string of characters to be encrypted. They first use the encryption function to set up the data and then insert it into the table. Here is the script:

USE DBAMDT
GO
INSERT INTO Test (FName)
VALUES (EncryptByCert ( Cert_ID('Cert_LogCabinPress'),
N'Buck Woody'))
GO

Now let's have a look at that data:

SELECT FName
FROM Test
GO
___________________________

amsdjdknosdj dkm asklf doihjf sakifnsaofhuhwe9uqhrp oisd
[f0as spij

That is not a misprint. The data really looks like that because it is encrypted. To see the data properly, the developer reverses the process using the DecryptByCert function:

SELECT
CONVERT(nvarchar(max)
, DecryptByCert(Cert_Id('Cert_LogCabinPress')
, FName))
FROM Test
GO

The developer has to use the CONVERT or CAST functions to bring the data from VARBINARY (which is how the data is encrypted) to the NVARCHAR characters that you can read. There are three parts in this script. The first is the DecryptByCert function, which decodes the data. The second is the column name that the developer wants (in this case, Fname). If you create a certificate using a password, you also need to include it as a third parameter.

Encrypting data by using the two keys we created earlier follows a similar process, with one exception. Before the developers can decrypt using the key, they need to open it. Here is the code that does that:

OPEN SYMMETRIC KEY Sym_LoginCabinPress
DECRYPTION BY PASSWORD N'This!sTooEasy'
GO

To use asymmetric encryption, the developer then uses the EncryptByAsymKey and DecryptByAsymKey functions, and for the symmetric keys they use the EncryptByKey and DecryptByKey functions. When they are done, they use the CLOSE SYMMETRIC KEY statement.

In the earlier examples, I created new keys and encrypted them with a password. You can also create keys and certificates and encrypt them with each other. You could create an asymmetric key and then encrypt your symmetric keys with it. Because there is a slight performance penalty for using the higher levels of encryption, this gives you a greater security function without the cost of asymmetric decryption.

Keep your keys safe and make sure you track the duration of your certificates. They should be put into place for no more than a year at a time to be truly secure.