SQL Server 2012 : Encryption Support (part 2) - Encrypting Data at Rest

Encrypting Data at Rest

Social Security or other government-issue identification numbers, credit card numbers, salary information, driver’s license numbers, passwords—the list of sensitive information just keeps going. Access to this information stored in the database has traditionally been secured by permissions. This is still the case, but another layer of protection is available natively in SQL Server 2005 and higher. Sensitive data can now be encrypted using symmetric keys, asymmetric keys, or certificates.

Before you jump in and encrypt data in a column, it is important to define the building blocks of encryption support within SQL Server. The first time SQL Server starts, it creates a special symmetric key called the Service Master Key (SMK). This key is used to encrypt all database master keys (DMKs) as well as all server-level secrets such as credential secrets or linked server login passwords. In SQL Server 2012, the key itself is an AES-encrypted key. Earlier versions of SQL Server used 3DES to encrypt the SMK and DMKs, so you should use the ALTER SERVICE MASTER KEY STATEMENT to regenerate the SMK and DMKs when upgrading an instance to SQL Server 2012.

Note

Encryption algorithm availability depends on the cryptographic service provider of the operating system that SQL Server is running on. For example, Microsoft Windows XP Service Pack 2 (SP2) supports DES, 3DES, RC2, RC4 (deprecated in SQL Server 2012), and RSA, whereas Windows Server 2003 and 2008 support all those plus AES128, AES192, and AES256.

The SMK is encrypted using the Windows security API, the Data Protection API (DPAPI), and the credentials of the SQL Server service account. Because the SMK is used to encrypt all DMKs and other server-level secrets, it is very important and should be backed up regularly. You can back up and restore the SMK by using the BACKUP SERVICE MASTER KEY or RESTORE SERVICE MASTER KEY statements. In the event of a compromised SMK, or if you want to change the SMK as part of implementing a normal security best practice, you can regenerate it using the ALTER SERVICE MASTER KEY REGENERATE statement.

With respect to encrypting data, the SMK is used by SQL Server to decrypt the DMK so that the DMK can in turn decrypt the requested data for the client. There is only one DMK per database, and none are created by default, because a DMK is used only for data encryption.

When DMKs are created, they are encrypted by the SMK (so that SQL Server can decrypt the data for the client) and by a password. It is possible to remove the SMK encryption, but the password of the DMK would need to be specified by the user every time he or she accesses this key.

Now that we have discussed the key components of encryption, let’s consider an example. This scenario will include a table named SalaryInfo that contains the name, department, and salary of employees at your company. A user named HR_User needs to be able to insert and view data into this table.

Because a lot of steps are involved in setting up this example, it is best to walk through Example 1 line by line, and follow the in-line comments included throughout the listing. The comments explain exactly what each T-SQL statement does and why.

Example 1. Encryption using ENCRYPTBYKEY and DECRYPTBYKEY.

USE master
GO

-- Create a login
CREATE LOGIN HRLogin WITH PASSWORD = 'HRp@$$w0rd'
GO

-- Create a new database
IF EXISTS(SELECT name FROM sys.databases WHERE name = 'MyDB')
  DROP DATABASE MyDB
GO
CREATE DATABASE MyDB
GO
USE MyDB
GO

-- Create a user mapped to the login
CREATE USER HRUser FOR LOGIN HRLogin
GO

-- Create the database master key (DMK)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'DMKp@$$w0rd'
GO

-- Create a table that will store sensitive credit card numbers. Notice
-- the varbinary data type that will store the encrypted card numbers.
CREATE TABLE CreditCardInfo
(CardType varchar(50),
 CardNumber varbinary(255))
GO

-- Give access to HRUser so they can query and add data
GRANT SELECT, INSERT TO HRUser
GO

-- Create a Symmetric Key, encrypt it with a password, and give HRUser access to it
CREATE SYMMETRIC KEY HRUserKey
 AUTHORIZATION HRUser
 WITH ALGORITHM = TRIPLE_DES
 ENCRYPTION BY PASSWORD = 'SYMp@$$w0rd'
GO

-- Impersonate HRUser and encrypt some data
EXECUTE AS LOGIN = 'HRLogin'
GO

-- Open the key that will be used to encrypt data. Notice you have to supply
-- the password for the key
OPEN SYMMETRIC KEY HRUserKey DECRYPTION BY PASSWORD = 'SYMp@$$w0rd'
GO

-- This system view shows open keys that can be used for encryption
SELECT * FROM sys.openkeys

-- Insert sensitive data into the table using ENCRYPTBYKEY, which takes the
-- GUID of the key (using KEY_GUID) and the text to be encrypted, and returns
-- the result as varbinary.
INSERT INTO CreditCardInfo VALUES
('MasterCard', ENCRYPTBYKEY(KEY_GUID('HRUserKey'), '5426-1891-5411-1369')),
 ('American Express', ENCRYPTBYKEY(KEY_GUID('HRUserKey'), '3728-847852-83004'))

-- When done, always close all keys
CLOSE ALL SYMMETRIC KEYS
GO

-- View the table as it lives in the database, notice the encrypted binary
SELECT * FROM CreditCardInfo

-- Now, decrypt and view the contents using DECRYPTBYKEY, which takes the column
-- name. You don't specify a key GUID because SQL will look at all open keys and
-- use the appropriate one automatically.
OPEN SYMMETRIC KEY HRUserKey DECRYPTION BY PASSWORD = 'SYMp@$$w0rd'
GO

SELECT *, CONVERT(varchar, DECRYPTBYKEY(CardNumber))
 FROM CreditCardInfo
GO

CLOSE ALL SYMMETRIC KEYS
GO

-- Revert back to sysadmin
REVERT
GO

-- When encrypting by password, need to know the password and pass it every time
-- you encrypt/decrypt. Alternatively you can create a certificate and give access
-- to the HR User. With this, the user doesn't have to provide a password and you
-- can easily revoke access to that encrypted data by simply removing the
-- certificate for that use.
CREATE CERTIFICATE HRCert
 AUTHORIZATION HRUser
 WITH SUBJECT = 'Certificate used by the Human Resources person'

-- Open the key so you can modify it
OPEN SYMMETRIC KEY HRUserKey DECRYPTION BY PASSWORD = 'SYMp@$$w0rd'
GO

-- You cannot remove the password because that would leave the key
-- exposed without encryption, so you need to add the certificate first
ALTER SYMMETRIC KEY HRUserKey
 ADD ENCRYPTION BY CERTIFICATE HRCert
GO

-- Now you can remove the password encryption from the key
ALTER SYMMETRIC KEY HRUserKey
 DROP ENCRYPTION BY PASSWORD = 'SYMp@$$w0rd'
GO

CLOSE ALL SYMMETRIC KEYS
GO

-- Now change context to HRLogin to test
EXECUTE AS LOGIN = 'HRLogin'
GO

-- Notice the key is opened without a password this time, because you created
-- the certificate and gave authorization on it explicitly to HRUser.
OPEN SYMMETRIC KEY HRUserKey DECRYPTION BY CERTIFICATE HRCert
GO

SELECT *, CONVERT(varchar, DECRYPTBYKEY(CardNumber))
 FROM CreditCardInfo
GO

CLOSE ALL SYMMETRIC KEYS
GO

-- Revert back to sysadmin
REVERT
GO

This example demonstrates the basics of encrypting and decrypting in SQL Server. The amount of data in this example is trivial to encrypt and decrypt, even for old (slow) machines. Performance for encryption depends on two factors other than how big of a server you are running on: the size of the data to encrypt and the algorithm used to encrypt the data. Using RSA2048 to encrypt a larger file might take a bit longer than encrypting a Social Security number. It is difficult to give a nice graph of size versus time because it depends on so many factors. The best thing to do is set up a test environment that simulates your production environment and run some performance tests: encrypt and decrypt various data sizes in different algorithms, or at least in the algorithm you are planning to use.

Another interesting issue with encrypted columns is indexing and searching. To SQL Server itself, these are binary columns, so there is no effective way to create an index on them because you cannot predict a random stream of bytes. The best thing to do in this case is to create or use another unencrypted column to index on. The problem with this is that you might inadvertently give information about the data that is encrypted. Imagine you want to index on salaries and create a column named range. Anyone who has SELECT permission on the table can guess what the employee makes. If you must index or search encrypted data, be creative about your unencrypted columns.