SQL Server Encryption

With the increasing number of incidents of lost and unauthorized exposure to sensitive data, database security is a vital and growing concern for many organizations. Companies that hold sensitive data within their databases have to meet various compliance requirements such as the Gramm-Leach-Bliley Act (GLBA), European Union Data Protection Directive (EUDPD), Health Insurance Portability and Accountability Act (HIPAA), Payment Card Industry Data Security Standard (PCI DSS), and Sarbanes-Oxley (SOX) Act. They require the encryption of sensitive data (e.g., credit card numbers) at the database and OS levels.

Luckily, like other mainstream commercial database systems, SQL Server provides a variety of encryption options, including cell-level encryption, database-level encryption, file-level encryption through Windows, and transport-level encryption. These encryption options provide strong security for data at the database and OS levels. They also reduce the likelihood of unauthorized disclosure of confidential information, even if the SQL Server infrastructure or databases are compromised. After providing an overview of the SQL Server encryption model, I'll describe the encryption options available in SQL Server and how you can use them to encrypt sensitive information stored inside your SQL Server databases.

SQL Server Encryption Model

The SQL Server encryption model primarily includes built-in encryption key management modeled on the ANSI X9.17 standard. This standard defines several layers of encryption keys that are used to encrypt other keys, which, in turn, are used to encrypt actual data. Table 1 lists the SQL Server and ANSI X9.17 encryption key layers.

The service master key (SMK) is the top-level key and is the father of all the keys in SQL Server. The SMK is an asymmetric key that's encrypted by the Windows Data Protection API (DPAPI). The SMK is automatically created the first time you encrypt something and is tied to the SQL Server Service account. The SMK is used to encrypt the database master key (DMK).

The second layer of the encryption key hierarchy is the DMK. It encrypts symmetric keys, asymmetric keys, and certificates. Each database can have only one DMK.

The next level includes symmetric keys, asymmetric keys, and certificates. Symmetric keys are the main means of encrypting the data in the database. Microsoft recommends encrypting data with only symmetric keys. In addition, SQL Server 2008 and later also includes server-level certificates and database encryption keys to support transparent data encryption. Figure 1 shows the encryption key hierarchy for SQL Server 2008 and later.

Figure 1: Understanding the Encryption Key Hierarchy in SQL Server 2008 and Later

Now that you're familiar with the SQL Server encryption key hierarchy, let's take a look at how you can implement the encryption options available in SQL Server. I'll also walk you through how to implement some of them.

Cell-Level Encryption

Beginning with SQL Server 2005, you can encrypt or decrypt the data on the server. There are a variety of ways to do that. For example, you can encrypt the data in your databases with one of the following methods:

Password. Using a password is the least secure option because you use the same passphrase to encrypt and decrypt the data. If the stored procedures and functions aren't encrypted, the passphrase can be accessed through the metadata.

Certificate. Using a certificate offers strong protection and good performance. You can also associate a certificate with a user. The certificate must be signed by the DMK.

Symmetric key. Using a symmetric key is strong enough for most data security requirements and offers good performance. It uses the same key to encrypt and decrypt data.

Asymmetric key. Using an asymmetric key provides strong protection because you use a different key to encrypt and decrypt the data. However, it negatively affects performance. Microsoft recommends that it shouldn't be used to encrypt large values. An asymmetric key can be signed by the DMK or created using a password.

DECRYPTBYKEYAUTOASYMKEY, which decrypts data by using a symmetric key that's automatically decrypted with an asymmetric key

DECRYPTBYKEYAUTOCERT, which decrypts data by using a symmetric key that's automatically decrypted with a certificate

SQL Server provides two system views that you can use to return metadata information about all the symmetric and asymmetric keys that exist in a SQL Server instance. As their names suggest, sys.symmetric_keys returns the metadata for symmetric keys and sys.asymmetric_keys returns the metadata for asymmetric keys. Another helpful view is sys.openkeys. This catalog view returns information about the encryption keys that are open in the current session.

Cell-Level Encryption Demonstration

I'll demonstrate how to use some of the encryption functions, decryption functions, and views so that you can see them in action. But first I'll walk you through creating a test database that has a table containing credit card numbers in case you want to follow along.

Preparations. To begin, create the EncryptedDB database using SQL Server Management Studio (SSMS) or by running the T-SQL code:

USE [master] GO CREATE DATABASE [EncryptedDB] GO

Next, run the following code to create a table named CreditCardInformation in the EncryptedDB database:

Demo 1. In the first demo, you'll be encrypting data using a symmetric key, which will be encrypted using an asymmetric key. To do this, you need to create the asymmetric key, encrypt it with the $tr0nGPa$$w0rd passphrase, create a symmetric key, and encrypt the symmetric key with the asymmetric key just created. You can run the code in Listing 1 to perform these tasks.

Now you're ready to start encrypting the data. To do so, you must first open the symmetric key that you just created by issuing the OPEN SYMMETRIC KEY command followed by the symmetric key's name. You then indicate that you want to decrypt it using the specified asymmetric key. The code looks like this:

Demo 2. In this second demo, you'll encrypt the data using a symmetric key, but this time the symmetric key is encrypted by a certificate. To do that, execute the code in Listing 3. This code first creates the certificate using the CREATE CERTIFICATE statement. It then creates a symmetric key that's encrypted with the certificate. Finally, after opening the symmetric key, the code inserts three rows into the CreditCardInformation table.

Advantages and Disadvantages of Cell-Level Encryption

There are advantages and disadvantages to using cell-level encryption. On the plus side, cell-level encryption provides a more granular level of encryption, giving you the means to encrypt a single cell within a table. In addition, the data isn't decrypted until it's used, which means the data isn't in plain text when the page is loaded into memory. You can also assign a key to users and protect it with a password to prevent automatic decryption.

On the minus side, cell-level encryption requires schema modification because all encrypted data must be stored using the varbinary data type. There's also a cost to the overall performance of the database because additional processing is required for encrypting and decrypting data. There will also be expensive table scans because all indexes on the table are encrypted and therefore won't be used.

Transparent Data Encryption

In SQL Server 2008, Microsoft introduced the ability to encrypt an entire database using transparent data encryption. With transparent data encryption, databases can be secured without modifying existing applications, database structures, or processes. It's the best option to meet regulatory compliance and corporate data security requirements because it encrypts the entire database on the hard disk.

Transparent data encryption encrypts databases in real-time, as the records are written to the SQL Server database files (*.mdf) and transaction log files (*.ldf). Records are also encrypted in real-time during database backups and when snapshots are taken. Transparent data encryption encrypts the data before it's written to disk and decrypts it before it's retrieved. This process is totally transparent to the user or application because it's performed at the SQL Server Service layer.

With transparent data encryption, SQL Server encrypts a database using its database encryption key. This asymmetric key is stored in the database boot record, which is why it's always available during recovery.

As shown in Figure 1, the database encryption key is encrypted using the server certificate, which is encrypted using the DMK of the master database. The master database's DMK is encrypted using SMK. The SMK is an asymmetric key that's encrypted by the Windows DPAPI. The SMK is automatically created the first time you encrypt something and is tied to the SQL Server Service account.

Enabling Transparent Data Encryption

To enable transparent data encryption, you must create a DMK and server certificate in the master database. To do this, run the following code:

It's important that you immediately back up the certificate and the DMK associated with the certificate. If the certificate becomes unavailable or if you want to restore or attach the database on another server, you must have the backups of both the certificate and the DMK. Otherwise, you won't be able to open the database.

The final step is to create the database encryption key and enable encryption on the database you want to secure. The following example enables transparent data encryption in the EncryptedDB database:

This query uses a dynamic management view named sys.dm_database_encryption_keys to return each database's encryption state. As Figure 5 shows, the results also include information about the database encryption key for each database.

Figure 5: Retrieving the Encryption States of the Databases on a Server

File-Level Encryption Through Windows

You can encrypt the entire SQL Server data directory using Encrypting File System (EFS), which is a file encryption feature in Windows 2000 and later. Like encryption in SQL Server, EFS relies on the Windows DPAPI. Unlike transparent data encryption, it doesn't encrypt backups automatically.

Database encryption using EFS is the recommended option to encrypt database files stored on NTFS disks. This is because SQL Server I/O operations are synchronous when EFS is enabled. The worker thread has to wait until the current I/O operation in the EFS-encrypted database file has completed.

Transport-Level Encryption

SQL Server offers two options to encrypt data that will be transmitted across a network between a SQL Server instance and a client application. This includes:

IPsec. IPsec is implemented at the OS level and supports authentication using Kerberos, certificates, or pre-shared keys. IPsec delivers application-transparent encryption services, providing advanced protocol filtering to block traffic by protocol and port. You can configure IPsec through the local security policy or through Group Policy. IPsec is compatible with Windows 2000 or later. To use this option, you must make sure that the client and server OSs support the IPsec protocol.

SSL. SSL is configured on SQL Server. It's most commonly used to support web clients, but it can also be used to support SQL Server native clients. SSL verifies the server when a client requests an encrypted connection. If the SQL Server instance is running on a computer that has a certificate from a public Certificate Authority, the identity of the computer and the instance of SQL Server ensure that the chain of certificates leads to the trust root authority. This server-side validation requires that the computer on which the client application is running be configured to trust the root authority of the certificate used by the server. Encryption with a self-signed certificate is possible, but a self-signed certificate offers little protection.

You can get and install an SSL certificate from a third-party Certificate Authority such as VeriSign, or you can install the Windows Certificate service and provision your own. (SQL Server can issue a self-signed certificate, but it's not meant to be used for production use.) After you obtain the SSL certificate, follow these steps to configure SSL on SQL Server:

On the Start menu, choose Run. In the Run dialog box, type MMC in the text box and click OK to launch the Microsoft Management Console (MMC).

In the MMC Certificates snap-in, expand Certificates, then Personal. Right-click the Certificates folder, choose All Tasks, and select the Import option. This will launch the Certificate Import Wizard. Click Next.

Browse and locate the SSL certificate file, then click OK.

Click Next, then click Next again to accept the default store. Click the Finish button.

Click OK to close the Success dialog box.

After installing the certificate on the server, you need to configure the SQL Server instance to accept encrypted connections. To do this, follow these steps:

Right-click Protocols for SQL Server instance (where SQL Server instance is the name of your instance) and choose Properties.

On the Certificate tab, select the SSL certificate from the drop-down list. Click OK.

On the Flags tab, change the ForceEncryption option to Yes, which means that SQL Server will allow only encrypted connections. If you leave the ForceEncryption option set to No, SQL Server allows both encrypted and unencrypted connections to SQL Server.

Click OK, and restart SQL Server.

Protect Your SQL Server Data

Encryption is the process of encoding the sensitive data with a key or password. Encryption provides strong security for the data and reduces the likelihood of unauthorized disclosure of confidential information, because the data is useless without the corresponding key or password. SQL Server provides a variety of encryption options, including cell-level encryption, database-level encryption, file-level encryption through Windows, and transport-level encryption. Although the SQL Server encryption options don't address accessibility to the SQL Server infrastructure and databases, they improve data security at the database and OS levels, even if the SQL Server infrastructure's or databases' security is compromised.

Discuss this Article 1

This is a good write up. A couple of comments:
1) Something to consider if you have more than a handful of databases and want to avoid key management headaches is a network Hardware Security Module (HSM) to secure and manage the keys. The network HSM manages the asymmetric key which is used to protect the symmetric key that is created when TDE is enabled for both SQL Server (and Oracle if you are using Oracle TDE).
2) The key in the Master database is not secure - there are known ways to extract this key if you have access to the system. This is a security hole and without an external key manager or HSM on the system SQL Server with TDE is technically not PCI compliant if that is critical to your regulatory needs. A network HSM allows you to avoid having to purchase a hardware HSM for each server to protect the key. On a side note, Vormetric Key Management manages TDE keys for both SQL Server and Oracle.
3) There are third party solutions. Vormetric Encryption combined encryption and key management and provides file-level encryption for data outside of your user and tempdb tables along with associated files outside of the database. This can encrypt the Master Tables, System Tables, Log files and any other external content such as trace files that may contain sensitive data
Cheers!
Todd from Vormetric

From the Blogs

The quest for the Golden Record to achieve a single, accurate and complete version of a customer record is worth the pursuit to attain survivorship. Record matching and consolidation are only the beginning. Melissa Data takes a new approach. Learn how to apply intelligent rules based on reference data to make smarter and better decisions for data cleansing....More

On SQL Servers where Availability Groups (or Mirroring) isn’t in play, I typically recommend keeping a combination of on-box backups along with copying said backups off-box as well. Obviously, keeping databases AND backups on the SAME server is the metaphorical equivalent of putting all of your eggs in one basket – and therefore something you should avoid like the plague....More

One of the biggest strengths of AlwaysOn Availability Groups is that they allow DBAs to address both high availability and disaster recovery concerns from a single set of tooling or interfaces. But, this doesn’t mean that you won’t still need backups....More