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.

Even an otherwise well-secured database is susceptible to attack if an attacker is able to get access to the disk files that comprise the database. Cell-level encryption can protect some of the data, but for complete protection against this kind of attack it is necessary to encrypt the files and not just the data. That is exactly what Transparent Data Encryption (TDE) does, and in this stairway level you'll learn what TDE does, how it works, and how to make use of it to protect your database files.

Transparent Data Encryption (TDE)

In SQL Server 2008, Microsoft introduced Transparent Data Encryption, or TDE. TDE does real time encryption and decryption of data and log files, at the file level. This encrypts all of the data in the database, in contrast to the data encryption at the column level that I explored in Level 8 of this stairway. There you had to explicitly use T-SQL functions to encrypt the data values, along with symmetric keys, asymmetric keys, or certificates to protect and decrypt the data.

TDE encrypts everything in the database at the page level. The encryption is transparent in that it doesn't affect your access to the data at all. There is no special programming required, although there is support in T-SQL for managing TDE, as you'll see later in this Level. This makes TDE very easy to set up and maintain, although it does require more work when copying and moving databases to different locations and instances of SQL Server.

TDE encrypts the data as it is written to the database, then decrypts it as it is read, all automatically once you've enabled TDE for the database. The purpose of TDE is to protect the data at rest in the database and log files, keeping it safe from attacks that attempt to access the data directly from the files. One scenario where this is useful would be if you need to ship your database files via an overnight package delivery service, such as FedEx or UPS. Without TDE, an attacker could steal your package from the back of the delivery truck and attach the database to an instance of SQL Server where she has sysadmin privileges, getting access to the data. But if TDE is enabled on the database, the entire set of data is securely encrypted; without the key there is no feasible way to get access to the data. Other scenarios where TDE is useful is where you're worried that an attacker, such as an insider, might get access to the physical data files in any way, or if you need to keep archived copies of the database secure.

How TDE Works

TDE requires a certificate in order to access the physical database files. Without the certificate used to encrypt the database, the data is just unusable encrypted gibberish. This means that it is very important not to keep a backup of the certificate anywhere near the database it protects—such as in the same FedEx package that you use to ship the database files! Otherwise an attacker would have all she needs to decrypt your data. All she'd need to do is install the certificate on an instance of SQL Server she controls, and use it to attach the database, giving her full access to the decrypted data.

It is important to understand that TDE encrypts all the data written to the database, as it is written. Then it decrypts that data as needed to respond to queries. So it is only when the data is at rest, stored in the database, that it is protected by TDE. TDE encrypts and decrypts the data in each 8K page as it reads and writes the data.

If any database in a SQL Server instance, even just one database out of dozens attached to the instance, is protected with TDE, then SQL Server will automatically protect tempdb with TDE as well. This is the case even if the database protected with TDE doesn't explicitly use tempdb. This makes sense, because some of the data from the protected database could get temporarily stored in tempdb. That data would be at rest—even for a very short time—so for complete protection tempdb needs to be protected with TDE as well. The issue with this is that encryption causes a performance hit. With all the data saved in tempdb encrypted, all of the other databases in the instance that are not protected with TDE will have any data stored in tempdb encrypted. So the performance of those databases is likely to be impacted as well.

To set everything up, you'll need permissions to create a database master key, create a certificate in the master database, and CONTROL permissions on the user database to enable TDE. Most of the time, a sysadmin will do the work to enable TDE on a database, so the permissions required shouldn't be an issue.

Limitations of TDE

To use TDE effectively, you should understand what TDE does and what it is and isn't good for. Consider these limitations of TDE:

• You cannot encrypt a subset of the data in the database: it is all or nothing.
• TDE is not a way to restrict data access through the database engine. Data access is unchanged by TDE. If you have an application being run by a user, and either the application or the user has the necessary permissions to access the data in the database, TDE does not change that data access in any way. Instead, it is protecting the database files.
• TDE is not a substitution for securing your server, SQL Server instance, or individual pieces of sensitive data stored in the database. You still need to think carefully about securing your data using defense in depth for layered security. TDE is just one of those layers designed to protect against specific attacks against data at rest in the data and log files.
• One of the biggest downsides to TDE is that FILESTREAM data is not encrypted. This is data blobs stored outside of SQL Server but managed and secured by the database.
• TDE is only available as part of the Enterprise and Developer editions of SQL Server. This is a shame, since this precludes users of “lesser” editions from using one valuable security protection for data.

As you can see, these “limitations” aren't limitations as much as the details of what TDE is useful for, indicating that it is not useful as a security panacea. But if the threats to your data correspond to the protections it provides, TDE can be a significant security feature.

TDE Performance

An issue that you'll need to consider when looking at TDE is performance. Encryption is an expensive operation in terms of the processing cycles it needs. TDE performs reasonably well, because TDE doesn't encrypt data in the SQL cache. SQL Server encrypts the data only when it is written out to disk. So encryption doesn't necessarily occur every time you touch a piece of data if it's in the cache, which improves the overall efficiency. Microsoft has spent a lot of time making encryption as efficient as possible in databases, but it still causes a performance hit when data is read or written, because of the complex algorithms used by encryption.

Using TDE

The sample code that accompanies this Level shows how you can use TDE and the effect that it has on a database. The script includes code not shown here that backs up the AdventureWorks2012 database then restores it as a new database called AdventureWorks2012Copy. There are various places in the code where you may have to change a file path to accommodate your local SQL Server installation, such as the backup location and a C:\Data folder used to back up a certificate.

Once you perform those setup steps, the code performs the four steps needed to enable TDE for a database:

1. Create a master key in the master database
2. Create/use a certificate protected by the master key
3. Create database encryption key, protected with the certificate
4. Enable TDE for the database

Listing 9.1 shows the code needed to create a certificate in the master database that will be used to protect the database encryption key in the database. It creates the database master key and protects it with a strong password, then creates the AdventureWorks2012TDECert. As a precaution, the code then backs up the certificate to the C:\Data folder. You should store this in a safe and secure place in case you ever need to move the database protected by TDE.

USE master;
GO
-- TDE hooks into encryption key hierarchy in SQL Server
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '!drJP9QXC&Vi%cs';
GO
-- Create the certificate used to protect the database encryption key
CREATE CERTIFICATE AdventureWorks2012TDECert WITH SUBJECT = 'Certificate to implement TDE on AdventureWorks2012Copy';
GO
-- Backup the certificate
-- Either create the C:\Data folder or change it in the code below
BACKUP CERTIFICATE AdventureWorks2012TDECert TO FILE = 'C:\Data\AdventureWorks2012TDECert'
    WITH PRIVATE KEY ( FILE = 'C:\Data\AdventureWorks2012TDECertPrivateKey' , 
    ENCRYPTION BY PASSWORD = 'RISiS9Ul%CByEk6' );
GO

Listing 9.1: Code to create and backup the certificate used to protect the TDE database.

Before going any further with configuring TDE, run the code in Listing 9.2. This statement lists the encrypted databases in the current instance of SQL Server, along with their encrypted status, if any. On a new, pristine instance of SQL Server, this should return an empty result. Later, after implementing TDE, you'll see how the results change.

SELECT DB_NAME(database_id) AS DatabaseName,
    key_algorithm AS [Algorithm],
    key_length AS KeyLength,
    CASE encryption_state
        WHEN 0 THEN 'No database encryption key present, no encryption'
        WHEN 1 THEN 'Unencrypted'
        WHEN 2 THEN 'Encryption in progress'
        WHEN 3 THEN 'Encrypted'
        WHEN 4 THEN 'Key change in progress'
        WHEN 5 THEN 'Decryption in progress'
    END AS EncryptionStateDesc,
    percent_complete AS PercentComplete
FROM sys.dm_database_encryption_keys;
GO

Listing 9.2: Code to list the status of encrypted databases in the SQL Server instance.

Now it's time to turn on TDE for the AdventureWorks2012Copy database. Execute the code in Listing 9.3, which starts by creating a database encryption key using the certificate in master that you created earlier. You'll get a warning message to back up the certificate if you haven't already; heed that advice! Then the code uses an ALTER DATABASE statement with the SET ENCRYPTION ON clause to turn TDE on for the database. Depending on the size of the database and the speed of the server, it could take some time to fully encrypt the database, potentially hours or days.

USE AdventureWorks2012Copy;
GO
-- Create the database encryption key for TDE. Analogous to database master key for data encryption.
CREATE DATABASE ENCRYPTION KEY 
    WITH ALGORITHM = TRIPLE_DES_3KEY
    ENCRYPTION BY SERVER CERTIFICATE AdventureWorks2012TDECert;
GO
-- Get a warning about backing up the key, if you haven't already
-- ...take the advice and back it up!
-- Now need to turn TDE on. 
ALTER DATABASE AdventureWorks2012Copy SET ENCRYPTION ON;

Listing 9.3: Code to create a database encryption key and to turn on TDE.

While it is encrypting the database, repeatedly execute the code back in Listing 9.2 to track its progress. You'll get results that look something like Figure 9.1, which I captured when encryption was about 45% complete. Notice that the figure shows two databases: now that at least one database in the instance uses TDE, tempdb is automatically encrypted as well. Once initial encryption is complete, the Percent Complete column will contain a zero for all databases (yes, the column header is a bit misleading, since it's not really the percent complete once it is finished).

Figure 9.1: Result of running code using sys.dm_database_encryption_keys to monitor the status of database encryption and it progresses.

Note also in the figure that the AdventureWorks2012Copy database is encrypted with the Triple DES encryption algorithm. This is because that is the algorithm we used for the database encryption key back in Listing 9.3; you have the option of any of the encryption algorithms supported by SQL Server. Note too that tempdb is encrypted by the default 256-bit AES encryption algorithm.

Test the encryption by executing queries against the database, such as the one in Listing 9.4. As long as you had the necessary permissions to access the data before turning on TDE, you will still be able to access the data. The same goes for any applications that access the data.

SELECT TOP 500 * FROM Production.Product;

Listing 9.4: Sample code to test database access after enabling TDE.

If you want to turn off TDE for a database, you can use the code in Listing 9.5.

ALTER DATABASE AdventureWorks2012Copy
    SET ENCRYPTION OFF;
GO

Listing 9.5: Disable TDE for a database.

Testing TDE

It is always wise to test security features to make sure that they work the way you expect. The sample code includes a few steps for testing TDE, including if you were to “accidentally” delete the certificate used to protect the database encryption key used for TDE. The code in Listing 9.6 backs up the AdventureWorks2012Copy database, drops the database, then drops the AdventureWorks2012TDECert, thereby simulating the loss of the certificate. (This also simulates what happens when you try to attach the encrypted database to a different instance of SQL Server, one that doesn't have the original certificate installed.)

BACKUP DATABASE AdventureWorks2012Copy
    TO DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012Copy.bak'
    WITH NOFORMAT, INIT, NAME = N'AdventureWorks2012Copy Full Database Backup',
    SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO
USE master
GO
DROP DATABASE AdventureWorks2012Copy;
GO
-- Oops! We lost the certificate and don't have a copy!
-- Or, going to restore the database to another server instance
DROP CERTIFICATE AdventureWorks2012TDECert; 
GO

Listing 9.6: Code to back up the database, drop the database, and “lose” the certificate.

Next, attempt to restore the database using the code in Listing 9.7. You'll get the error message shown in Figure 9.2. This is the protection of TDE kicking in: it is impossible to restore or attach a database to an instance of SQL Server that doesn't have the original encryption certificate installed.

RESTORE DATABASE AdventureWorks2012Copy
    FROM DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012Copy.bak'
    WITH 
        FILE = 1, NOUNLOAD, REPLACE, STATS = 10;

Listing 9.7: Code that attempts to restore the database protected by TDE.

Figure 9.2: Result of attempt to restore the database to an instance without the protection certificate installed.

But if you backed up the certificate, all is not lost! Use the code in Listing 9.8 to restore the certificate from the backup file, using the password used earlier to protect the certificate in the file, then attempt to restore the database. This time the fully available database—still protected with TDE—is successfully restored.

-- Recover from the problem
-- Restore the certificate
CREATE CERTIFICATE AdventureWorks2012TDECert
    FROM FILE = 'C:\Data\AdventureWorks2012TDECert'
    WITH PRIVATE KEY ( FILE = 'C:\Data\AdventureWorks2012TDECertPrivateKey', 
    DECRYPTION BY PASSWORD = 'RISiS9Ul%CByEk6');
    
-- Now try to restore the database
RESTORE DATABASE AdventureWorks2012Copy
    FROM DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012Copy.bak'
    WITH 
        FILE = 1, NOUNLOAD, REPLACE, STATS = 10;

Listing 9.8: Code that restores the deleted certificate from the backup file, then attempts again to restore the database.

The sample code file has code at the end to clean up the test database and the certificate in master.

TDE Compared to Column-Level Data Encryption

Between this Level 9 and the previous Level 8, you should have a good understanding of the two main forms of encryption provided by SQL Server, and when to use each. To summarize the primary differences, with data encryption at the column level:

• The encryption is much more granular. You can encrypt a single column in a single row in a single table.
• The encrypted data is decrypted only when it is used. If the data is rarely used, it will rarely be decrypted.
• You need to change the table schema to accommodate the encrypted binary data and change the applications to include encryption and decryption code.
• You can't easily search and sort encrypted data, and indexes are largely useless. There are some workarounds, but they aren't efficient and expose characteristics of the data that an attacker can use to get a toehold.

In general, you'll want to use column-level data encryption for small amounts of data to protect the most sensitive data and save processing cycles on the server.

This all begs the question: Which form of encryption should you use in SQL Server 2008 and later? The key is to understand the threats that you need to protect against, which is crucial when implementing any security features.

If the threat is the theft and misuse of database and log files, use Transparent Data Encryption. TDE will prevent someone from attaching the database files to another instance of SQL Server and gaining access to the data.

But if the threat is hacking the data on your server, column-level encryption is probably the better option. Properly implemented column-level data encryption prevents access to the data when an attacker gets access to the database server.

If you can't decide between the two options, it may be that you have to protect against threats that require both types of encryption. Fortunately, they work very well together, each protecting against their own unique threats.

Summary

Transparent Data Encryption performs real time encryption and decryption of data and log files, at the file level. This encrypts all of the data in the database and logs, preventing an attacker from attaching a database to another instance of SQL Server and gaining access to the data. If the threats you need to protect against are theft and misuse of the data files, TDE can provide strong data protection without requiring schema or programming changes. Used properly, it can provide a strong layer of security for overall defense in depth.