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.

Related: SQL Server 2012

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:

SQL Server provides built-in functions for cell-level encryption and decryption. The encryption functions are:

The decryption functions are:

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]GOCREATE DATABASE [EncryptedDB]GO

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

USE [EncryptedDB] GO CREATE TABLE [dbo].[CreditCardInformation]   ([PersonID] [int] PRIMARY KEY   ,[CreditCardNumber] [varbinary](max)) GO

This table will hold false credit card information. The credit card numbers will be stored in a variable binary column because they'll be encrypted.

Now use the following code to create the EncryptedDB database's DMK, which is encrypted with the passphrase $tr0nGPa$$w0rd:

 USE [EncryptedDB] GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$tr0nGPa$$w0rd' GO

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.

USE [EncryptedDB]GO-- Create the asymmetric key encrypted by-- the passphrase StrongPa$$w0rd!CREATE ASYMMETRIC KEY MyAsymmetricKey  WITH ALGORITHM = RSA_2048  ENCRYPTION BY PASSWORD = 'StrongPa$$w0rd!'GO-- Create the symmetric key encrypted by-- the asymmetric key.CREATE SYMMETRIC KEY MySymmetricKey  WITH ALGORITHM = AES_256  ENCRYPTION BY ASYMMETRIC KEY MyAsymmetricKeyGO 

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:

 USE [EncryptedDB] GO OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION BY ASYMMETRIC KEY MyAsymmetricKey WITH PASSWORD = 'StrongPa$$w0rd!' GO

After running this code, query the sys.openkeys view to verify that the key is open:

USE [EncryptedDB]GOSELECT * FROM [sys].[openkeys]

You will get results similar to that shown in Figure 2.

Figure 2: Using the sys.openkeys View to Verify the Key Is Open 

Finally, you need to insert some credit card numbers into the CreditCardInformation table by running the code in Listing 2.

USE [EncryptedDB]GODECLARE @SymmetricKeyGUID AS [uniqueidentifier]SET @SymmetricKeyGUID = KEY_GUID('MySymmetricKey')IF (@SymmetricKeyGUID IS NOT NULL)  BEGIN    INSERT INTO [dbo].[CreditCardInformation]    VALUES (01, ENCRYPTBYKEY(@SymmetricKeyGUID,  N'9876-1234-8765-4321'))    INSERT INTO [dbo].[CreditCardInformation]    VALUES (02, ENCRYPTBYKEY(@SymmetricKeyGUID,  N'9876-8765-8765-1234'))    INSERT INTO [dbo].[CreditCardInformation]    VALUES (03, ENCRYPTBYKEY(@SymmetricKeyGUID,  N'9876-1234-1111-2222'))  ENDTRUNCATE TABLE [dbo].[CreditCardInformation]

Afterward, query the CreditCardInformation table:

USE [EncryptedDB]GOSELECT * FROM [dbo].[ CreditCardInformation]

As Figure 3 shows, all the data in the CreditCardNumber column is in binary format.

Figure 3: Examining the Encrypted Data 

You can use the DECRYPTBYKEY function to view the encrypted data:

 USE [EncryptedDB] GO SELECT [PersonID]   ,CONVERT([nvarchar](32), DECRYPTBYKEY(CreditCardNumber))   AS [CreditCardNumber] FROM [dbo].[CreditCardInformation] GOFigure 4 shows the results.Figure 4: Using the DECRYPTBYKEY Function to View the Encrypted Data 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.USE [EncryptedDB]GO-- Create the certificate.CREATE CERTIFICATE [CertToEncryptSymmetricKey]WITH SUBJECT =  'Self-signed certificate to encrypt symmetric key.'-- Create a symmetric key that's encrypted-- with the certificate.CREATE SYMMETRIC KEY [SymmetricKeyEncryptedWithCert]WITH ALGORITHM = AES_256ENCRYPTION BY CERTIFICATE [CertToEncryptSymmetricKey]-- Open the symmetric key.OPEN SYMMETRIC KEY [SymmetricKeyEncryptedWithCert]DECRYPTION BY CERTIFICATE [CertToEncryptSymmetricKey]-- Truncate the CreditCardInformation table.TRUNCATE TABLE [dbo].[CreditCardInformation]-- Insert the data into the table.DECLARE @SymmetricKeyGUID AS [uniqueidentifier]SET @SymmetricKeyGUID =  KEY_GUID('SymmetricKeyEncryptedWithCert')IF (@SymmetricKeyGUID IS NOT NULL)  BEGIN    INSERT INTO [dbo].[CreditCardInformation]    VALUES (01, ENCRYPTBYKEY(@SymmetricKeyGUID,  N'9876-1234-8765-4321'))    INSERT INTO [dbo].[CreditCardInformation]    VALUES (02, ENCRYPTBYKEY(@SymmetricKeyGUID,  N'9876-8765-8765-1234'))    INSERT INTO [dbo].[CreditCardInformation]    VALUES (03, ENCRYPTBYKEY(@SymmetricKeyGUID,  N'9876-1234-1111-2222'))  ENDAdvantages and Disadvantages of Cell-Level EncryptionThere 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 EncryptionIn 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 EncryptionTo enable transparent data encryption, you must create a DMK and server certificate in the master database. To do this, run the following code:USE [master]GO-- Create the DMK.CREATE MASTER KEY ENCRYPTION BYPASSWORD = '$tr0ngPa$$w0rd1'GO-- Create the server certificate.CREATE CERTIFICATE EncryptedDBCertWITH SUBJECT = 'Certificate to encrypt EncryptedDB';GO 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:USE [master]GOCREATE DATABASE ENCRYPTION KEYWITH ALGORITHM = AES_256ENCRYPTION BY SERVER CERTIFICATE [EncryptedDBCert]GOALTER DATABASE [EncryptedDB]SET ENCRYPTION ONGO Alternatively, you can use SSMS to enable transparent data encryption in a database. To do this, follow these steps:In Object Explorer, right-click the database name.Choose Properties, and select Options.Change the Encryption Enabled option to True.If you want to see the encryption state of all the databases on a server, you can run the query:USE [master]GOSELECT db.[name]  ,db.[is_encrypted]  ,dm.[encryption_state]  ,dm.[percent_complete]  ,dm.[key_algorithm]  ,dm.[key_length]FROM [sys].[databases] dbLEFT OUTER JOIN [sys].[dm_database_encryption_keys] dmON db.[database_id] = dm.[database_id];GO 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 WindowsYou 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.For additional general information about EFS, see "How it Works" on the Microsoft TechNet website. For more technical details on EFS, see "How Encrypting File System Works" on the TechNet website.Transport-Level EncryptionSQL 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.SSL ConfigurationConfiguring SSL on the server is more straightforward than configuring IPsec. In addition, SSL requires minimal client configuration. So, I'll show you how to configure SSL. (If you're interested in learning how to set up IPsec, see the Windows IT Pro article "Use IPsec to Encrypt Data" or "Step-by-Step Guide to Internet Protocol Security (IPsec)" on the TechNet website.)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).On the File menu, click Add/Remove Snap-in to launch the Add/Remove Snap-in dialog box. Choose Add to bring up the Add Standalone Snap-in dialog box.Choose Certificates, and click Add. Select Computer account, and click Finish.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:Launch SQL Server Configuration Manager and expand SQL Server Network Configuration.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 DataEncryption 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.