Relational databases are used in an amazing variety of applications with connections from a dizzying array of clients over widely distributed networks, particularly the Internet, which makes their data accessible to almost anyone, anywhere. Databases can hold a significant portion of human knowledge, including highly sensitive personal information and critical data that makes international commerce work.

These characteristics make databases attractive targets for people who want to steal data or harm its owner by tampering with it. Making sure that your data is secure is a critical part of configuring SQL Server and developing applications that use it to store data. This Stairway explores the basics of SQL Server 2012 security so that you can protect your data and server resources, getting as granular as you need to be to protect against the unique security threats that can affect your data. Much of the information will apply to earlier versions of SQL Server, going back to SQL Server 2005, because that is when Microsoft overhauled security in the product. But I'll also discuss features that are only in SQL Server 2012 and later.

An important security concept is defense in depth, which means that the best security is layered on rather than relying on a single layer of protection. After your network, server, SQL Server instance, and database are secured, you can add one final strong layer of data protection by encrypting sensitive data. This stairway level will explore data protection through encryption, both when the data is in motion across the network or in memory and at rest in a table. You'll learn about the encryption key hierarchy and the various kinds of keys you can use to encrypt data, as well as how you can manage the keys or let SQL Server do it for you.

Data Encryption

So far in this Stairway you've learned about a lot of the great security features built into SQL Server that you can use to protect your data and other database objects. When you combine these with great network security, such as firewalls, least-privilege user accounts, and other tools, your data should be perfectly safe, right?

Alas, no. Even though SQL Server 2012 and 2014 are by far the most secure versions yet—assuming you wisely and effectively put the security features to use—successful attacks are possible. Hackers are clever people, so it is quite possible and likely that someone, sometime, will figure out a way to hack into your database server and access the data there. Even if you're 100% confident that such an attack is impossible, you might find yourself on the receiving end of an insider attack—a trusted employee gone bad.

A security principle that can help you understand how to attain the highest level of security for your data or any other resource you need to protect is defense in depth. Defense in depth means that you never rely on a single security measure to protect anything valuable. You add layer after layer of different kinds of security so that any attacker has to break through multiple, difficult obstacles before reaching your data.

This is the reason that many medieval castles had a moat as a first line of defense, often with nasty critters swimming in it. The next layer was a heavily fortified, thick oak gate and stone walls that were meters thick. Often another thick, heavily fortified wall lay just inside the outer wall. Layer after layer of security protected the people and the treasures within.

In a similar way, SQL Server has many layers of security and many tools to build and maintain them. But when your data is extremely valuable, you can add one last layer of defense: encryption.

SQL Server 2005 was the first version to have rich support for data encryption, and each version since then has made some incremental improvements. You can use a variety of encryption types, including several types of keys that are the secret to transforming data into unreadable gibberish, unless the user has the key to unlock it. SQL Server supports a variety of encryption algorithms. And best of all, you can have the server do all the work of managing keys and keeping them secret, which has always been the hardest part of encryption.

Encryption Keys

SQL Server lets you use any of three types of encryption keys. An encryption key is the small piece of data that, when plugged into an algorithm, converts the data into gibberish that is virtually impossible to convert back to the unencrypted value without the proper decryption key.

• Asymmetric Key: This type of encryption uses a public/private matched key pair. One key encrypts the data and the other decrypts it. You can share the public key with anyone so that for any data they encrypt, only you can decrypt by using the private key. SQL Server uses the Rivest-Shamir-Adelman (RSA) encryption algorithms in 512, 1024, and 2048-bit keys. You can learn more about the RSA algorithms at Wikipedia's RSA page.
• Symmetric Key: In this type of encryption, the key that encrypts and decrypts the data is the same. This is sometimes called a shared secret, because both parties who share data must have the same key. In some scenarios, it is difficult to use symmetric keys because securely transmitting the secret from one party to the other is a problem. Symmetric keys are ideal for use in a database because they never leave the database. SQL Server supports the RC4 and RC2 algorithms as well as the DES and AES families of algorithms. You can find an overview of these algorithms at the Symmetric Encryption topic at encryptionanddecryption.com.

• Certificate: A certificate is a digital wrapper for a public key as part of asymmetric key encryption. SQL Server can create certificates for your use or you can get one from a third-party certificate authority. SQL Server uses the Internet Engineering Task Force's X.509 specifications.

SQL Server uses an encryption key hierarchy, shown in Figure 8.1, to encrypt and protect keys that you store in the database.

Figure 8.1: Encryption key hierarchy.

At the server level, each instance of SQL Server has a Service Master Key that you use to encrypt other keys that are lower in the hierarchy. This key is created upon installation of the SQL Server instance. You can back up and restore it, as well as regenerate it if it is ever compromised, but for the most part you'll leave it alone. SQL Server manages it for you and you'll never have to explicitly use it. The Service Master Key is stored and protected deep within Windows, using the Windows Data Protection API, or DPAPI.

The Service Master Key has some internal uses, but for our purposes here SQL Server uses it to encrypt and protect any Database Master Keys you create. A Database Master Key is required in any database in which you encrypt data. It is a symmetric key that encrypts and protects any keys you create. You have to explicitly create it in a statement like this within the database context using code like that in Listing 8.1.

USE EncryptionDB;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'gK#3hbQKDFQY0oF';

Listing 8.1: Code to create a database master key in the EncryptionDB database.

The Database Master Key is stored twice: It is encrypted once by the Service Master Key and stored, and encrypted again by the password you supply. You can remove either of these, but not both. Normally you'll want to leave both in place.

As a symmetric key, the Database Master Key must be open before you can use it. Opening a key loads it into memory and decrypts it, so that it is ready for use. Because the server's Service Master Key encrypts the Database Master Key, SQL Server can open the key for you automatically so that you'll rarely need to explicitly open it. Like the Service Master Key, you can back up and restore the Database Master Key, and alter it if you like.

You'll use the other keys in Figure 8.1 for encrypting data. The arrows indicate which keys you use to encrypt and protect other keys. For example, you can use a Database Master Key to encrypt either certificates or asymmetric keys. Certificates and asymmetric keys can protect only symmetric keys. And symmetric keys can be protected by certificates, asymmetric keys, or other symmetric keys.

Key Management

Figure 8.1 shows one other aspect of encryption keys: You can create any of the keys by using either another key or a password. This is called key management, and it's one of the major services that SQL Server can take care of for you.

Key management is the single hardest thing to get right about encryption. History is full of examples of various countries' secrets being compromised when the key was intercepted en route. During World War II, the U.S, U.K., and other Allied countries put massive resources into intercepting encryption keys that Germany and Japan used so that they could intercept and read highly sensitive information. In more recent times, many a highly secure application was compromised because an attacker was able to find the secret keys either embedded within the application or on the computer. Sharing secrets securely is very difficult.

You can manage SQL Server encryption keys yourself if you choose to by using passwords, but then you take responsibility for keeping the key secret. Most people won't want to do this, because it requires highly specialized technical skills. But if you want to take on the task, simply use the option to supply a password when you create a key. The password is essentially the key, and you have to make sure you can save that key in a secure location and transmit it securely when you need it.

But you don't have to handle those details, because SQL Server will take care of key management for you. It will encrypt the new key for you, using whatever method you specify as part of the CREATE statement, and store the data as securely as it stores any other sensitive information.

You have the option to manage keys, but you shouldn't do so without a very good reason.

Encrypting Data

Now you'll look at an example of encrypting data in SQL Server. In this scenario, a Customer table has the usual information about customers. Some of the data, such as the customer name and the city where they are located, is not sensitive data and is not worth encrypting. Other data, such as the credit card type and number, as well as notes that could contain sensitive personal information, should be encrypted.

You'll use a symmetric key to encrypt the data in the table, but remember that a symmetric key requires either a certificate or asymmetric key to protect it in the database. So, first create an asymmetric key to protect the symmetric key using the code in Listing 8.2.

CREATE ASYMMETRIC KEY User1AsymmetricKey
    AUTHORIZATION User1
    WITH ALGORITHM = RSA_2048

Listing 8.2: Code to create an asymmetric key that is owned by User1 that uses the RSA 2048-bit algorithm.

The asymmetric key is called User1AsymmetricKey and the AUTHORIZATION clause specifies that User1 owns the key. This key uses RSA encryption with a 2048-bit key, very strong encryption indeed. This must be really, really important data!

Next, create the symmetric key User1SymmetricKey using the code in Listing 8.3. (You'll no doubt come up with far more descriptive names for your keys!) In this case, it uses the TRIPLE_DES algorithm and is protected by the asymmetric key that you just created.

CREATE SYMMETRIC KEY User1SymmetricKey
    WITH ALGORITHM = TRIPLE_DES
    ENCRYPTION BY ASYMMETRIC KEY User1AsymmetricKey;

Listing 8.3: Code to create a symmetric key that is protected by the earlier asymmetric key using the Triple DES algorithm.

If you want to list the symmetric encryption keys in the database, you can use the sys.symmetric_keys catalog view to see them. The code in Listing 8.4 produces the results shown in Figure 8.2. Notice that because the Database Master Key is a symmetric key, it also shows up in the list and you can see that it is encrypted by AES_256.

SELECT * FROM sys.symmetric_keys;

Listing 8.4: List the symmetric keys in the database.

Figure 8.2: Viewing the sys.symmetric_keys catalog view.

Listing 8.5 shows the structure of the table, which is in the EncryptionDB database (the code to create the table is in the sample setup code):

CREATE TABLE Customer (
    CustId int, 
    Name nvarchar(30), 
    City varchar(20), 
    CreditCardType varbinary(1000),
    CreditCardNumber varbinary(1000), 
    Notes varbinary(4000));

Listing 8.5: Code to create the Customer table, with varbinary fields for the encrypted data.

Notice that because the last three fields will contain binary encrypted data rather than the original string data, the fields are of type varbinary. The length of the field depends on the size of the data as well as the algorithm that protects it. The database has a User1 user, which has SELECT and INSERT permissions on the table.

Finally, it is time to encrypt some data and insert it into the database. The first step is to open the symmetric key using a statement like that shown in Listing 8.6. This step causes SQL Server to retrieve the key from its internal storage, make sure the user has permissions to use the key, and then decrypt the key into memory to make it ready for use.

OPEN SYMMETRIC KEY User1SymmetricKey
    DECRYPTION BY ASYMMETRIC KEY User1AsymmetricKey;

Listing 8.6: The first step to using a symmetric key is to open it using an OPEN SYMMETRIC KEY statement.

Data encryption uses the Transact-SQL EncryptByKey function, which takes the unique GUID that identifies the key. You can use the Key_GUID function to retrieve the GUID rather than pass the value directly. Otherwise, the code in Listing 8.7 is a regular Transact-SQL INSERT statement to insert a row of data into a table:

INSERT INTO Customer VALUES (1, 'Sally Roe', 'Chatinika',
    EncryptByKey(Key_GUID('User1SymmetricKey'), 'Visa'),
    EncryptByKey(Key_GUID('User1SymmetricKey'), '1234-5678-9009-8765'),
    EncryptByKey(Key_GUID('User1SymmetricKey'), 
        'One of our best customers. Treat like royalty.'));

Listing 8.7: Code to insert encrypted data into the table, using the EncryptByKey and Key_GUID functions.

The last step is to close the symmetric key, using the code in Listing 8.8. This removes the key from memory and frees those resources for other uses. You should always close the key as soon as you're done with it, since leaving it in memory could conceivably make it available to an attacker.

CLOSE SYMMETRIC KEY User1SymmetricKey;

Listing 8.8: CLOSE SYMMETRIC KEY statement to close the key and remove it from memory.

Now run a SELECT statement to see what is in the table, shown in Figure 8.3. You can see the plain, unencrypted text of the CustId, Name, and City in unprotected fields, but the encrypted text appears to be random binary data. Your data is now safe!

Figure 8.3: Encrypted data stored in a table.

Data in a table is fairly worthless unless there is a way to retrieve it. In this case you need to use a regular SELECT statement, and use the DecryptByKey function to decrypt the data. This function returns varbinary data, since the encrypted data can be any data type. So retrieving the original text requires that you convert the result of the DecryptByKey function into an appropriate text string.

Listing 8.9 is the code that opens the key and runs the SELECT statement.

OPEN SYMMETRIC KEY User1SymmetricKey
    DECRYPTION BY ASYMMETRIC KEY User1AsymmetricKey;
SELECT CustID, Name, City,
    CONVERT(VARCHAR, DecryptByKey(CreditCardType)) AS CardType,
    CONVERT(VARCHAR, DecryptByKey(CreditCardNumber)) AS CardNumber,
    CONVERT(VARCHAR, DecryptByKey(Notes)) AS Notes
FROM Customer;
CLOSE SYMMETRIC KEY User1SymmetricKey;

Listing 8.9: Code to open a symmetric key then use a SELECT statement to retrieve encrypted data with the DecryptByKey function.

This displays the results shown in Figure 8.4.

Figure 8.4: Decrypted data after using SELECT and DecryptByKey.

Summary

The data encryption features of SQL Server provide an additional layer of protection for your data, giving you deep defense in depth. Like the medieval castles of old that had multiple layers of fortification, SQL Server attackers have to breech network security, server security, SQL Server instance security, and database security to get at your data. Then, with victory so near, they have to deal with strong encryption for the data to be at all useful to them. Coupled with strong database security, making use of granular permissions to provide least privilege only to principals who need access to data, you can build robust protection for your most sensitive data.