SQL Server 2014 supports built-in column-and database-level encryption functionality directly through T-SQL. Column-level encryption allows you to encrypt the data in your database at the column level. Back in the days of SQL Server 2000 (and before), you had to turn to third-party tools or write your own extended stored procedures (XPs) to encrypt sensitive data. Even with these tools in place, subpar implementation of various aspects of the system, such as encryption key management, could leave many systems in a vulnerable state.

SQL Server 2014’s encryption model takes advantage of the Windows CryptoAPI to secure your data. With built-in encryption key management and facilities to handle encryption, decryption, and one-way hashing through T-SQL statements, SQL Server 2014 provides useful tools for efficient and secure data encryption. SQL Server 2014 also supports two encryption options: transparent data encryption (TDE) for supporting encryption of an entire database; and extensible key management (EKM), which allows you to use third-party hardware-based encryption key management and encryption acceleration.

This chapter discusses SQL Server 2014’s built-in column-level encryption and decryption functionality, key management capabilities, one-way hashing functions, and TDE and EKM functionality.

The Encryption Hierarchy

SQL Server 2014 offers a layered approach to encryption key management by allowing several levels of key-encrypting keys between the top-level master key and the lowest-level data-encrypting keys. SQL Server also allows for encryption by certificates, symmetric keys, and asymmetric keys. The SQL Server 2014 encryption model is hierarchical, as shown in Figure 8-1.

At the top of the SQL Server 2014 encryption hierarchy is the Windows Data Protection API (DPAPI), which is used to protect the granddaddy of all SQL Server 2014 encryption keys: the service master key (SMK). The SMK is automatically generated by SQL Server the first time it’s needed to encrypt another key. There is only one SMK per SQL Server instance, and it directly or indirectly secures all keys in the SQL Server encryption key hierarchy on the server.

Although each SQL Server instance has only a single SMK, each database can have a database master key (DMK). The DMK is encrypted by the SMK and is used to encrypt lower-level keys and certificates.

At the bottom of the SQL Server 2014 key hierarchy are the certificates, symmetric keys, and asymmetric keys used to encrypt data.

SQL Server 2014 also introduces the concept of the server certificate, which is a certificate created in the master database for the purpose of protecting database encryption keys. Database encryption keys are symmetric encryption keys created to encrypt entire databases via TDE.

Service Master Keys

As mentioned in the previous section, the SMK is automatically generated by SQL Server the first time it’s needed. Because the SMK is generated automatically and managed by SQL Server, there are only a couple of administrative tasks you need to perform for this key: backing it up and restoring it on a server as necessary. You also need access to the directory where the backup file is located. For example, in Listing 8-1, you want to create a folder named CH08 on your C drive. Listing 8-1 demonstrates the BACKUP and RESTORE SERVICE MASTER KEY statements.

Listing 8-1. BACKUP and RESTORE SMK Examples

-- Back up the SMK to a file
BACKUP SERVICE MASTER KEY TO FILE = 'c:CH08S0L2012.SMK'
ENCRYPTION BY PASSWORD = 'p@$$w0rd';

-- Restore the SMK from a file
RESTORE SERVICE MASTER KEY FROM FILE = 'c:CH08S0L2012.SMK'
DECRYPTION BY PASSWORD = 'p@$$w0rd';

The BACKUP SERVICE MASTER KEY statement allows you to back up your SMK to a file. The SMK is encrypted in the file, so the ENCRYPTION BY PASSWORD clause of this statement is mandatory.

The RESTORE SERVICE MASTER KEY statement restores the SMK from a previously created backup file. The DECRYPTION BY PASSWORD clause must specify the same password used to encrypt the file when you created the backup. Backing up and restoring an SMK requires CONTROL SERVER permissions. In the previous scenario, SQL Server is intelligent enough to know that the backup SMK and the SMK in the restore are the same, so it doesn’t need to go through an unnecessary decryption and encryption process. The data is encrypted again only if the SMK you’re trying to restore is different from the SMK you backed up.

The RESTORE SERVICE MASTER KEY statement can include the optional keyword FORCE to force the SMK to restore even if there is a data decryption failure. If you have to use the FORCE keyword, you can expect to lose data, so use this option with care and only as a last resort.

In addition to BACKUP and RESTORE statements, SQL Server provides the ALTER SERVICE MASTER KEY statement to allow you to change the SMK for an instance of SQL Server. When SQL Server generates the SMK, it uses the credentials of the SQL Server service account to encrypt the SMK. If you change the SQL Server service account, you can use ALTER SERVICE MASTER KEY to update it using the current service account credentials. Alternatively, you can advise SQL Server to secure the SMK using the local machine key, which is managed by the operating system. You can also use ALTER SERVICE MASTER KEY to regenerate the SMK completely.

As with the RESTORE SERVICE MASTER KEY statement, the ALTER SERVICE MASTER KEY statement allows use of the FORCE keyword. Normally, if there is a decryption error during the process of altering the SMK, SQL Server stops the process with an error message. When FORCE is used, the SMK is regenerated even at the risk of data loss. Just like the RESTORE statement, the FORCE option should be used with care, and only as a last resort.

Database Master Keys

Each database can have a single DMK, which is used to encrypt certificate private keys and asymmetric key-pair private keys in the current database. The DMK is created with the CREATE MASTER KEY statement, as shown in Listing 8-2.

Listing 8-2. Creating a Master Key

USE AdventureWorks2014;
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'p@$$w0rd' ;

The CREATE MASTER KEY statement creates the DMK and uses the Advanced Encryption Standard (AES) to encrypt it with the supplied password. If the password you supply doesn’t meet Windows’ password-complexity requirements, SQL Server will complain with an error message like the following:

Msg 15118, Level 16, State 1, Line 1
Password validation failed. The password does not meet Windows
policy requirements because it is not complex enough.

SQL Server 2014 automatically uses the SMK to encrypt a copy of the DMK. When this feature is used, SQL Server can decrypt your DMK when necessary without the need to first open the master key. When this feature isn’t in use, you must issue the OPEN MASTER KEY statement and supply the same password initially used to encrypt the DMK whenever you need to use it. The potential downside to encrypting your DMK with the SMK is that any member of the sysadmin server role can decrypt the DMK. You can use the ALTER MASTER KEY statement to change the method SQL Server uses to decrypt the DMK. Listing 8-3 shows how to turn off encryption by SMK for a DMK.

Listing 8-3. Turning Off DMK Encryption by the SMK

ALTER MASTER KEY
   DROP ENCRYPTION BY SERVICE MASTER KEY;

When the DMK is regenerated, all the keys it protects are decrypted and re-encrypted with the new DMK. The FORCE keyword is used to force SQL Server to regenerate the DMK even if there are decryption errors. As with the SMK, the FORCE keyword should be used only as a last resort. You can expect to lose data if you have to use FORCE.

You can also back up and restore a DMK with the BACKUP MASTER KEY and RESTORE MASTER KEY statements. The BACKUP MASTER KEY statement is similar in operation to the BACKUP SERVICE MASTER KEY statement. When you back up the DMK, you must specify the password that SQL Server will use to encrypt the DMK in the output file. When you restore the DMK, you must specify the same password in the DECRYPTION BY PASSWORD clause to decrypt the DMK in the output file. In addition, you must specify an encryption password that SQL Server will use to encrypt the password in the ENCRYPTION BY PASSWORD clause. Listing 8-4 demonstrates backing up and restoring a DMK.

Listing 8-4. Backing Up and Restoring a DMK

USE AdventureWorks2014;
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'p@$$w0rd' ;

BACKUP MASTER KEY
   TO FILE = 'c:CH08AdventureWorks2014.DMK'
   ENCRYPTION BY PASSWORD = 'p@$$wOrd';

-- Restore DMK from backup
RESTORE MASTER KEY
  FROM FILE = 'c:CH08AdventureWorks2014.DMK'
  DECRYPTION BY PASSWORD = 'p@$$wOrd'
  ENCRYPTION BY PASSWORD = '3rt=d4uy';

CLOSE MASTER KEY;

The FORCE keyword is available for use with the RESTORE MASTER KEY statement. But as with other statements, it should only be used as a last resort, because it could result in unrecoverable encrypted data.

The DROP MASTER KEY statement can be used to remove a DMK from the database. DROP MASTER KEY doesn’t remove a DMK if it’s currently being used to encrypt other keys in the database. If you want to drop a DMK that is protecting other keys in the database, the protected keys must first be altered to remove their encryption by the DMK.

If you choose to disable automatic key management with the ALTER MASTER KEY statement, you need to use the OPEN MASTER KEY and CLOSE MASTER KEY statements every time you wish to perform encryption and decryption in a database.

OPEN MASTER KEY requires you to supply the same password used to encrypt the DMK in the DECRYPTION BY PASSWORD clause. This password is used to decrypt the DMK, a required step when you’re encrypting and decrypting data. When you’re finished using the DMK, issue the CLOSE MASTER KEY statement. If your DMK is encrypted by the SMK, you don’t need to use the OPEN MASTER KEY and CLOSE MASTER KEY statements; SQL Server handles that task for you automatically.

Certificates

Certificates are asymmetric encryption key pairs with additional metadata, such as subject and expiration date, in the X.509 certificate format. Asymmetric encryption is a method of encrypting data using two separate but mathematically related keys. SQL Server 2014 uses the standard public key/private key encryption methodology. You can think of a certificate as a wrapper for an asymmetric encryption public key/private key pair. The CREATE CERTIFICATE statement can be used to either install an existing certificate or create a new certificate on SQL Server. Listing 8-5 shows how to create a new certificate on SQL Server.

Listing 8-5. Creating a Certificate on SQL Server

CREATE CERTIFICATE TestCertificate
  ENCRYPTION BY PASSWORD = 'p@$$wOrd'
  WITH SUBJECT = 'AdventureWorks2014 Test Certificate',
  EXPIRY_DATE = '2026-10-31';

The CREATE CERTIFICATE statement includes several options. The only mandatory things are the SQL Server identifier for the certificate immediately following the CREATE CERTIFICATE statement (in this case TestCertificate) and the WITH SUBJECT clause, which sets the certificate subject name. If the ENCRYPTION BY PASSWORD clause isn’t used when you create a certificate, the certificate’s private key is encrypted by the DMK. Additional options available to the CREATE CERTIFICATE statement include START_DATE and EXPIRY_DATE, which set the start and expiration dates for the certificate; and the ACTIVE FOR BEGIN DIALOG clause, which makes the certificate available for use by Service Broker dialogs.

You can also use the CREATE CERTIFICATE statement to load an existing certificate in a variety of ways, including the following:

• You can use the FROM ASSEMBLY clause to load an existing certificate from a signed assembly already loaded in the database.
• You can use the EXECUTABLE FILE clause to create a certificate from a signed DLL file.
• You can use the FILE clause to create a certificate from an existing Distinguished Encoding Rules (DER) X.509 certificate file.
• You can also use the WITH PRIVATE KEY clause with the FILE or EXECUTABLE FILE option to specify a separate file containing the certificate’s private key. When you specify the WITH PRIVATE KEY clause, you can specify the optional DECRYPTION BY PASSWORD and ENCRYPTION BY PASSWORD clauses to specify the password that will be used to decrypt the private key if it’s encrypted in the source file, and to secure the private key once it’s loaded.

After creating a certificate—as with DMKs and SMKs—you should immediately make a backup and store it in a secure location. Listing 8-6 demonstrates how to make a backup of a certificate.

Listing 8-6. Backing Up a Certificate

BACKUP CERTIFICATE TestCertificate
  TO FILE = 'c:CH08TestCertificate.CER'
  WITH PRIVATE KEY
  (
    FILE = 'c:CH08TestCertificate.PVK',
    ENCRYPTION BY PASSWORD = ' 7&rtOxp2',
    DECRYPTION BY PASSWORD = 'p@$$wOrd'
  );

The BACKUP CERTIFICATE statement in Listing 8-6 backs up the TestCertificate certificate to the c:TestCertificate.CER file and the certificate’s private key to the c:TestCertificate.PVK file. The DECRYPTION BY PASSWORD clause specifies the password to use to decrypt the certificate, and ENCRYPTION BY PASSWORD gives SQL Server the password to use when encrypting the private key in the file. There is no RESTORE statement for certificates; instead, the CREATE CERTIFICATE statement has all the options necessary to restore a certificate from a backup file by simply creating from an existing certificate using the FROM FILE clause. T-SQL also provides an ALTER CERTIFICATE statement that allows you to make changes to an existing certificate.

You can use certificates to encrypt and decrypt data directly with the certificate encryption and decryption functions, EncryptByCert and DecryptByCert. The EncryptByCert function encrypts a given clear text message with a specified certificate. The function accepts an int certificate ID and a plain text value to encrypt. The int certificate ID can be retrieved by passing the certificate name to the CertID function. Listing 8-7 demonstrates this function. EncryptByCert returns a varbinary value up to a maximum of 432 bytes in length (the length of the result depends on the length of the key). The following section, “Limitations of Asymmetric Encryption,” describes some of the limitations of asymmetric encryption on SQL Server, including encryption by certificate.

Limitations of Asymmetric Encryption

Asymmetric encryption has certain limitations that should be noted before you attempt to encrypt data directly with certificates or asymmetric keys. The EncryptByCert function can accept a char, varchar, binary, nchar, nvarchar, or varbinary constant, column name, or variable as clear text to encrypt. Asymmetric encryption, including encryption by certificate, on SQL Server returns a varbinary result, but it won’t return a result longer than 432 bytes. As mentioned, the maximum length of the result depends on the length of the encryption key used. As an example, with the default private key length of 1,024 bits, you can encrypt a varchar plain text message with a maximum length of 117 characters and an nvarchar plain text message with a maximum length of 58 characters. The result in either case is a varbinary result of 128 bytes.

Microsoft recommends that you avoid using asymmetric encryption to encrypt data directly because of the size limitations, and for performance reasons. Symmetric encryption algorithms use shorter keys but operate more quickly than asymmetric encryption algorithms. The SQL Server 2014 encryption key hierarchy provides the best of both worlds, with the long key lengths of asymmetric keys protecting the shorter, more efficient symmetric keys. To maximize performance, Microsoft recommends using symmetric encryption to encrypt data and asymmetric encryption to encrypt symmetric keys.

The DecryptByCert function decrypts text previously encrypted by EncryptByCert. The DecryptByCert function accepts an int certificate ID, an encrypted varbinary cipher text message, and an optional certificate password that must match the one used when the certificate was created (if one was specified at creation time). If no certificate password is specified, the DMK is used to decrypt it. Listing 8-7 demonstrates encryption and decryption by certificate for short plain text. The results are shown in Figure 8-2. If you get an error during the CREATE MASTER KEY and CREATE CERTIFICATE commands, be sure to run the final DROP statements prior to creating the objects.

Listing 8-7. Example Encryption and Decryption by Certificate

--      Create a DMK
CREATE  MASTER  KEY
    ENCRYPTION BY PASSWORD = 'P@55w0rd';

--  Create a certificate
CREATE  CERTIFICATE  TestCertificate
    WITH  SUBJECT  =  N'AdventureWorks  Test  Certificate',
    EXPIRY_DATE = '2026-10-31';

-- Create the plain text data to encrypt
DECLARE @plaintext nvarchar(58) =
    N'This is a test string to encrypt';
SELECT  'Plain  text  =  ',  @plaintext;

-- Encrypt the plain text by certificate
DECLARE @ciphertext varbinary(128) =
    EncryptByCert(Cert_ID('TestCertificate'), @plaintext);
SELECT 'Cipher text = ', @ciphertext;

--  Decrypt the cipher text by certificate
DECLARE @decryptedtext nvarchar(58) =
    DecryptByCert(Cert_ID('TestCertificate'), @ciphertext);
SELECT  'Decrypted  text  =  ',  @decryptedtext;

-- Drop the test certificate
DROP  CERTIFICATE TestCertificate;

--      Drop the DMK
DROP  MASTER KEY;

Listing 8-7 first creates a DMK and a test certificate using the CREATE MASTER KEY and CREATE CERTIFICATE statements presented previously in this chapter. It then generates an nvarchar plain text message to encrypt:

-- Create a DMK
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@55wOrd';

-- Create a certificate
CREATE CERTIFICATE TestCertificate
WITH SUBJECT = N'AdventureWorks Test Certificate',
EXPIRY_DATE = '2026-10-31';

-- Create the plain text data to encrypt
DECLARE @plaintext nvarchar(58) =
N'This is a test string to encrypt';
SELECT 'Plain text = ', @plaintext;

The example uses the EncryptByCert function to encrypt the plain text message. The CertID function is used to retrieve the int certificate ID for TestCertificate:

-- Encrypt the plain text by certificate
DECLARE @ciphertext varbinary(128) =
EncryptByCert(Cert_ID('TestCertificate'), @plaintext);
SELECT 'Cipher text = ', @ciphertext;

The DecryptByCert function is then used to decrypt the cipher text. Again, the CertID function is used to retrieve the TestCertificate certificate ID:

-- Decrypt the cipher text by certificate
DECLARE @decryptedtext nvarchar(58) =
DecryptByCert(Cert_ID('TestCertificate'), @ciphertext);
SELECT 'Decrypted text = ', @decryptedtext;

The balance of the code performs some cleanup, dropping the certificate and DMK:

-- Drop the test certificate
DROP CERTIFICATE TestCertificate;
-- Drop the DMK
DROP MASTER KEY;

You can also use a certificate to generate a signature for a plain text message. SignByCert accepts a certificate ID, a plain text message, and an optional certificate password. The result is a varbinary string, up to a length of 432 characters (again, the length of the result is determined by the length of the encryption key). When SignByCert is used, the slightest change in the plain text message—even a single character—will result in a completely different signature being generated for the message. This allows you to easily detect whether your plain text has been tampered with. Listing 8-8 uses the SignByCert function to create a signature for a plain text message. The results are shown in Figure 8-3.

Listing 8-8. Signing a Message with the SignByCert Function

-- Create a DMK
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@55w0rd';
-- Create a certificate
CREATE CERTIFICATE TestCertificate
WITH SUBJECT = 'AdventureWorks Test Certificate',
EXPIRY_DATE = '2026-10-31';
-- Create message
DECLARE @message nvarchar(4000) = N'Four score and seven years ago our fathers brought forth on this continent a new nation, conceived in Liberty, and dedicated to the proposition that all men are created equal.
Now we are engaged in a great civil war, testing whether that nation, or any nation, so conceived and so dedicated, can long endure. We are met on a great battle-field of that war. We have come to dedicate a portion of that field, as a final resting place for those who here gave their lives that that nation might live. It is altogether fitting and proper that we should do this. ';
-- Sign the message by certificate
SELECT SignByCert(Cert_ID(N'TestCertificate'), @message);
-- Drop the certificate
DROP CERTIFICATE TestCertificate;
-- Drop the DMK DROP MASTER KEY;

Asymmetric Keys

Asymmetric keys are actually composed of a key pair: a public key, which is publicly accessible, and a private key, which is kept secret. The mathematical relationship between the public and private keys allows for encryption and decryption without revealing the private key. T-SQL includes statements for creating and managing asymmetric keys.

The CREATE ASYMMETRIC KEY statement allows you to generate an asymmetric key pair or install an existing key pair on the server, in much the same manner as when creating a certificate. Encryption-key length is often used as an indicator of relative encryption strength, and when you create an asymmetric key on SQL Server, you can specify an RSA key length, as shown in Table 8-1.

Listing 8-9 creates an asymmetric key pair on SQL Server 2014.

Listing 8-9. Creating an Asymmetric Key Pair

CREATE ASYMMETRIC KEY TempAsymmetricKey WITH ALGORITHM = RSA_1024;

You can alter an existing asymmetric key with the ALTER ASYMMETRIC KEY statement. ALTER ASYMMETRIC KEY offers the following options for managing your asymmetric keys:

• You can use the REMOVE PRIVATE KEY clause to remove the private key from the asymmetric public key/private key pair.
• You can use the WITH PRIVATE KEY clause to change the method used to protect the private key.
• You can change the asymmetric key protection method from DMK encryption to password encryption with the ENCRYPTION BY PASSWORD option.
• You can switch from password protection for your asymmetric key to DMK protection with the DECRYPTION BY PASSWORD clause.
• You can specify both the ENCRYPTION BY PASSWORD and DECRYPTION BY PASSWORD clauses together to change the password used to encrypt the private key.
• The DROP ASYMMETRIC KEY statement removes an asymmetric key from the database.

The EncryptByAsymKey and DecryptByAsymKey functions allow you to encrypt and decrypt data with an asymmetric key in the same way as EncryptByCert and DecryptByCert.

The EncryptByAsymKey function accepts an int asymmetric key ID and plain text to encrypt. The AsymKeyID function can be used to retrieve an asymmetric key ID by name. DecryptByAsymKey accepts an asymmetric key ID, encrypted cipher text to decrypt, and an optional password to decrypt the asymmetric key. If the password is specified, it must be the same password used to encrypt the asymmetric key at creation time.

Listing 8-10 demonstrates the use of asymmetric key encryption and decryption functions. Be sure to drop any master keys prior to running the code. The results are shown in Figure 8-4.

Listing 8-10. Encrypting and Decrypting with Asymmetric Keys

-- Create DMK
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'P@55wOrd';

-- Create asymmetric key
CREATE ASYMMETRIC KEY TestAsymmetricKey WITH ALGORITHM = RSA_512;

--Assign a credit card number to encrypt
DECLARE @CreditCard nvarchar(26) = N'9000 1234 5678 9012';
SELECT @CreditCard;

--Encrypt the credit card number
DECLARE @EncryptedCreditCard varbinary(64) =
  EncryptByAsymKey(AsymKey_ID(N'TestAsymmetricKey'), @CreditCard);
  SELECT @EncryptedCreditCard;

--Decrypt the encrypted credit card number
DECLARE @DecryptedCreditCard nvarchar(26) =
  DecryptByAsymKey(AsymKey_ID(N'TestAsymmetricKey'), @EncryptedCreditCard);
SELECT @DecryptedCreditCard;

-- Drop asymmetric key
DROP ASYMMETRIC KEY TestAsymmetricKey;

--Drop DMK
DROP MASTER KEY;

This example first creates a DMK and an RSA asymmetric key with a 512-bit private key length. Then it creates plain text representing a simple credit card number:

-- Create DMK
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@55wOrd';

-- Create asymmetric key
CREATE ASYMMETRIC KEY TestAsymmetricKey WITH ALGORITHM = RSA_512;

--Assign a credit card number to encrypt
DECLARE @CreditCard nvarchar(26) = N'9000 1234 5678 9012';
SELECT @CreditCard;

The example then encrypts the credit card number with the EncryptByAsymKey function and decrypts it with the DecryptByAsymKey function. Both functions use the AsymKeylD function to retrieve the asymmetric key ID:

-- Encrypt the credit card number
DECLARE @EncryptedCreditCard varbinary(64) =
EncryptByAsymKey(AsymKey_ID(N'TestAsymmetricKey'), @CreditCard);
SELECT @EncryptedCreditCard;

-- Decrypt the encrypted credit card number
DECLARE @DecryptedCreditCard nvarchar(26) =
DecryptByAsymKey(AsymKey_ID(N'TestAsymmetricKey'), @EncryptedCreditCard);
SELECT @DecryptedCreditCard;

The code finishes up with a little housekeeping, namely dropping the asymmetric key and the DMK created for the example:

-- Drop asymmetric key
DROP ASYMMETRIC KEY TestAsymmetricKey;
-- Drop DMK
DROP MASTER KEY;

Like certificates, asymmetric keys offer a function to generate digital signatures for plain text. The SignByAsymKey function accepts a string up to 8,000 bytes in length and returns a varbinary signature for the string. The length of the signature is dependent on the key length, as previously shown in Table 8-1. Listing 8-11 is a simple example of the SignByAsymKey function in action. The results are shown in Figure 8-5.

Listing 8-11. Signing a Message by Asymmetric Key

-- Create DMK
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'P@55wOrd';

-- Create asymmetric key
CREATE ASYMMETRIC KEY TestAsymmetricKey WITH ALGORITHM = RSA_512;

-- Create message
DECLARE @message nvarchar(4000) = N'Alas, poor Yorick!';
SELECT @message;

-- Sign message by asymmetric key
SELECT SignByAsymKey(AsymKey_ID(N'TestAsymmetricKey'), @message);

-- Drop asymmetric key
DROP ASYMMETRIC KEY TestAsymmetricKey;

-- Drop DMK
DROP MASTER KEY;

Asymmetric Key “Backups”

SQL Server provides no BACKUP or RESTORE statement for asymmetric keys. For physical backups of your asymmetric keys, you should install the asymmetric keys from an external source like an assembly, an executable file, a strong-name file, or a hardware security module (HSM). You can make backups of the source files containing your asymmetric keys. As an alternative, you can use certificates instead of asymmetric keys. Keep these options in mind when you’re planning to take advantage of SQL Server 2014 encryption.

Symmetric Keys

Symmetric keys are at the bottom of the SQL Server encryption key hierarchy. Symmetric encryption algorithms use trivially related keys to both encrypt and decrypt your data. Trivially related simply means the algorithm can use either the same key for both encryption and decryption, or two keys that are mathematically related via a simple transformation to derive one key from the other. Symmetric keys on SQL Server 2014 are specifically designed to support SQL Server’s symmetric encryption functionality. The algorithms provided by SQL Server 2014 use a single key for both encryption and decryption. In the SQL Server 2014 encryption model, symmetric keys are encrypted by certificates or asymmetric keys, and they can be used in turn to encrypt other symmetric keys or raw data. The CREATE SYMMETRIC KEY statement allows you to generate symmetric keys, as shown in Listing 8-12.

Listing 8-12. Creating a Symmetric Key

CREATE SYMMETRIC KEY TestSymmetricKey WITH ALGORITHM = AES_128 ENCRYPTION BY PASSWORD = 'p@55wOrd';

The options specified in the CREATE SYMMETRIC KEY statement in Listing 8-12 specify that the symmetric key is created with the name TestSymmetricKey, it’s protected by the password p@55wOrd, and it uses AES with a 127-bit key (AES128) to encrypt data.

When creating a symmetric key, you can specify any of several encryption algorithms, including the following:

• AES128, AES192, and AES256 specify the AES block-encryption algorithm with a symmetric key length of 128, 192, or 256 bits and a block size of 128 bits.
• DES specifies the DES block-encryption algorithm, which has a symmetric key length of 56 bits and a block size of 64 bits.
• DESX specifies the DES-X block-encryption algorithm, which was introduced as a successor to the DES algorithm. DES-X also has a symmetric key length of 56 bits (although because the algorithm includes security augmentations, the effective key length is calculated at around 118 bits) and a block size of 64 bits.
• RC2 specifies the RC2 block-encryption algorithm, which has a key size of 128 bits and a block size of 64 bits.
• RC4 and RC4_128 specify the RC4 stream-encryption algorithm, which has a key length of 40 or 128 bits. RC4 and RC4_128 aren’t recommended, because they don’t generate random initialization vectors to further obfuscate the cipher text.

The CREATE SYMMETRIC KEY statement provides additional options that allow you to specify options for symmetric key creation, including the following:

• You can specify a KEYSOURCE to designate a passphrase to be used as key material from which the symmetric key is derived. If you don’t specify a KEY SOURCE, SQL Server generates the symmetric key from random key material.
• The ENCRYPTION BY clause specifies the method used to encrypt this symmetric key in the database. You can specify encryption by a certificate, a password, an asymmetric key, another symmetric key, or HSM.
• The PROVIDER_KEY_NAME and CREATI0N_DISP0SITI0N clauses allow you to use your symmetric key with EKM security.
• The IDENTITYVALUE clause specifies an identity phrase that is used to generate a GUID to “tag” data encrypted with the key.

Temporary Symmetric Keys

You can create temporary symmetric keys by prefixing the symmetric key name with a number sign (#). A temporary symmetric key exists only during the current session and is automatically removed when the current session ends. Temporary symmetric keys aren’t accessible to any sessions outside of the session they’re created in. When referencing a temporary symmetric key, the number sign (#) prefix must be used. You can use the same WITH clause options described in this section to specify how the symmetric key should be created. To be honest, we don’t see much use for temporary symmetric keys at this point, although we don’t want to discount them totally. After all, someone may find a use for them in the future.

SQL Server also provides the ALTER SYMMETRIC KEY and DROP SYMMETRIC KEY statements for symmetric key management. The ALTER statement allows you to add or remove encryption methods on a symmetric key. As an example, if you created a symmetric key and encrypted it by password but later wished to change it to encryption by certificate, you would issue two ALTER SYMMETRIC KEY statements—the first ALTER statement would specify the ADD ENCRYPTION BY CERTIFICATE clause, and the second would specify DROP ENCRYPTION BY PASSWORD, as shown in Listing 8-13. Again, you may need to drop the certificate and key prior to running the code.

Listing 8-13. Changing the Symmetric Key Encryption Method

-- Create certificate to protect symmetric key
CREATE CERTIFICATE TestCertificate
  WITH SUBJECT = 'AdventureWorks Test Certificate',
  EXPIRY_DATE = '2026-10-31';

CREATE SYMMETRIC KEY TestSymmetricKey WITH ALGORITHM = AES_128 ENCRYPTION BY 
PASSWORD = 'p@55wOrd';

OPEN SYMMETRIC KEY TestSymmetricKey
  DECRYPTION BY PASSWORD = 'p@55wOrd';

ALTER SYMMETRIC KEY TestSymmetricKey
  ADD ENCRYPTION BY CERTIFICATE TestCertificate;

ALTER SYMMETRIC KEY TestSymmetricKey
  DROP ENCRYPTION BY PASSWORD = 'p@55wOrd';

CLOSE SYMMETRIC KEY TestSymmetricKey;

-- Drop the symmetric key
DROP SYMMETRIC KEY TestSymmetricKey;

-- Drop the certificate
DROP CERTIFICATE TestCertificate;

The DROP SYMMETRIC KEY statement allows you to remove a symmetric key from the database.

Once you create a symmetric key, you can encrypt and decrypt data with the EncryptByKey and DecryptByKey functions. Listing 8-14 creates a symmetric key and encrypts 100 names with it. Partial results are shown in Figure 8-6.

Listing 8-14. Encrypting Data with a Symmetric Key

-- Create a temporary table to hold results
CREATE TABLE #TempNames
(
   BusinessEntityID int PRIMARY KEY,
   FirstName      nvarchar(50),
   MiddleName     nvarchar(50),
   LastName       nvarchar(50),
   EncFirstName    varbinary(200),
   EncMiddleName   varbinary(200),
   EncLastName     varbinary(200)
);

-- Create DMK
CREATE MASTER KEY
  ENCRYPTION BY PASSWORD = 'Test_P@sswOrd';

-- Create certificate to protect symmetric key
CREATE CERTIFICATE TestCertificate
  WITH SUBJECT = 'AdventureWorks Test Certificate',
  EXPIRY_DATE = '2026-10-31';

-- Create symmetric key to encrypt data
CREATE SYMMETRIC KEY TestSymmetricKey
  WITH ALGORITHM = AES_128
  ENCRYPTION BY CERTIFICATE TestCertificate;

-- Open symmetric key
OPEN SYMMETRIC KEY TestSymmetricKey
  DECRYPTION BY CERTIFICATE TestCertificate;

-- Populate temp table with 100 encrypted names from the Person.Person table
INSERT
INTO #TempNames
(
BusinessEntityID,
EncFirstName,
EncMiddleName,
EncLastName
)
SELECT TOP(100) BusinessEntityID,
  EncryptByKey(Key_GUID(N'TestSymmetricKey'), FirstName),
  EncryptByKey(Key_GUID(N'TestSymmetricKey'), MiddleName),
  EncryptByKey(Key_GUID(N'TestSymmetricKey'), LastName)
FROM Person.Person
ORDER BY BusinessEntityID;

-- Update the temp table with decrypted names
UPDATE #TempNames
SET FirstName = DecryptByKey(EncFirstName),
  MiddleName = DecryptByKey(EncMiddleName),
  LastName = DecryptByKey(EncLastName);

-- Show the results
SELECT BusinessEntityID,
  FirstName,
  MiddleName,
  LastName,
  EncFirstName,
  EncMiddleName,
  EncLastName
FROM #TempNames;

-- Close the symmetric key
CLOSE SYMMETRIC KEY TestSymmetricKey;

-- Drop the symmetric key
DROP SYMMETRIC KEY TestSymmetricKey;

-- Drop the certificate
DROP CERTIFICATE TestCertificate;

--Drop the DMK
DROP MASTER KEY;

--Drop the temp table
DROP TABLE #TempNames;

Listing 8-14 first creates a temporary table to hold the encryption and decryption results:

-- Create a temporary table to hold results
CREATE TABLE #TempNames
(
BusinessEntityID  int PRIMARY KEY,
FirstName               nvarchar(50),
MiddleName              nvarchar(50),
LastName                nvarchar(50),
EncFirstName            varbinary(200),
EncMiddleName           varbinary(200),
EncLastName             varbinary(200)
);

Then a DMK is created to protect the certificate that will be created next. The certificate that’s created is then used to encrypt the symmetric key:

-- Create DMK
CREATE MASTER KEY
  ENCRYPTION BY PASSWORD = 'Test_P@sswOrd';

-- Create certificate to protect symmetric key
CREATE CERTIFICATE TestCertificate
  WITH SUBJECT = 'AdventureWorks Test Certificate',
  EXPIRY_DATE = '2026-10-31';

-- Create symmetric key to encrypt data
CREATE SYMMETRIC KEY TestSymmetricKey
  WITH ALGORITHM = AES_128
  ENCRYPTION BY CERTIFICATE TestCertificate;

In order to encrypt data with the symmetric key, the example must first execute the OPEN SYMMETRIC KEY statement to open the symmetric key. The DECRYPTION BY clause specifies the method to use to decrypt the symmetric key for use. In this example, the key is protected by certificate, so DECRYPTION BY CERTIFICATE is used. You can specify decryption by certificate, asymmetric key, symmetric key, or password. If the DMK was used to encrypt the certificate or asymmetric key, leave off the WITH PASSWORD clause:

-- Open symmetric key
OPEN SYMMETRIC KEY TestSymmetricKey
  DECRYPTION BY CERTIFICATE TestCertificate;

The next step is to use the EncryptByKey function to encrypt the data. In this example, the FirstName, MiddleName, and LastName for 100 rows from the Person.Person table are encrypted with EncryptByKey. The EncryptByKey function accepts a clear text char, varchar, binary, varbinary, nchar, or nvarchar constant, column, or T-SQL variable with a maximum length of 8,000 bytes. The result returned is the encrypted data in varbinary format with a maximum length of 8,000 bytes. In addition to clear text, EncryptByKey accepts a GUID identifying the symmetric key with which you wish to encrypt the clear text. The KeyGUID function returns a symmetric key’s GUID by name:

-- Populate temp table with 100 encrypted names from the Person.Person table
INSERT
INTO #TempNames
(
   BusinessEntityID,
EncFirstName,
EncMiddleName,
EncLastName
)

SELECT TOP(100) BusinessEntityID,
   EncryptByKey(Key_GUID(N'TestSymmetricKey'), FirstName),
   EncryptByKey(Key_GUID(N'TestSymmetricKey'), MiddleName),
   EncryptByKey(Key_GUID(N'TestSymmetricKey'), LastName)
FROM Person.Person
ORDER BY BusinessEntityID;

The example code then uses the DecryptByKey function to decrypt the previously encrypted cipher text in the temporary table. SQL Server stores the GUID of the symmetric key used to encrypt the data with the encrypted data, so you don’t need to supply the symmetric key GUID to DecryptByKey. In the example code, the varbinary encrypted cipher text is all that’s passed to the EncryptByKey function:

-- Update the temp table with decrypted names
UPDATE #TempNames
SET FirstName = DecryptByKey(EncFirstName),
    MiddleName = DecryptByKey(EncMiddleName),
    LastName = DecryptByKey(EncLastName);

Finally, the results are shown and the symmetric key is closed with the CLOSE SYMMETRIC KEY statement:

-- Show the results
SELECT BusinessEntityID,
 FirstName,
 MiddleName,
 LastName,
 EncFirstName,
 EncMiddleName,
 EncLastName
FROM #TempNames;

-- Close the symmetric key
CLOSE SYMMETRIC KEY TestSymmetricKey;

The balance of the code drops the symmetric key, the certificate, the master key, and the temporary table:

-- Drop the symmetric key
DROP SYMMETRIC KEY TestSymmetricKey;

-- Drop the certificate
DROP CERTIFICATE TestCertificate;

-- Drop the DMK
DROP MASTER KEY;

-- Drop the temp table
DROP TABLE #TempNames;

Salt and Authenticators

The initialization vector (IV), or salt, is an important aspect of encryption security. The IV is a block of bits that further obfuscates the result of an encryption. The idea is that the IV helps prevent the same data from generating the same cipher text if it’s encrypted more than once by the same key and algorithm. SQL Server doesn’t allow you to specify an IV when encrypting data with a symmetric key, however. Instead, SQL Server generates a random IV automatically when you encrypt data with block ciphers like AES and DES. The obfuscation provided by the IV helps eliminate patterns from your encrypted data patterns that cryptanalysts can use to their advantage when attempting to hack your encrypted data. The downside to SQL Server’s randomly generated IVs is that they make indexing an encrypted column a true exercise in futility.

In addition to random IV generation, SQL Server’s EncryptByKey and DecryptByKey functions provide another tool to help eliminate patterns in encrypted data. Both functions provide two options parameters: an add_authenticator flag and an authenticator value. If the add_authenticator flag is set to 1, SQL Server derives an authenticator from the authenticator value passed in. The authenticator is then used to obfuscate your encrypted data further, preventing patterns that can reveal information to hackers through correlation analysis attacks. If you supply an authenticator value during encryption, the same authenticator value must be supplied during decryption.

When SQL Server encrypts your data with a symmetric key, it automatically adds metadata to the encrypted result, as well as padding, making the encrypted result larger (sometimes significantly larger) than the unencrypted plain text. The format for the encrypted result with metadata follows the following format:

• The first 16 bytes of the encrypted result represent the GUID of the symmetric key used to encrypt the data.
• The next 4 bytes represent a version number, currently hard-coded as 0x01000000.
• The next 8 bytes for DES encryption (16 bytes for AES encryption) represent the randomly generated IV.
• If an authenticator was used, the next 8 bytes contain header information with an additional 20-byte SHA1 hash of the authenticator, making the header information 28 bytes in length.
• The last part of the encrypted data is the actual padded data. For DES algorithms, the length of this encrypted data is a multiple of 8 bytes. For AES algorithms, the length is a multiple of 16 bytes.

In addition to DecryptByKey, SQL Server 2014 provides DecryptByKeyAutoCert and DecryptByKeyAutoAsymKey functions. Both functions combine the functionality of the OPEN SYMMETRIC KEY statement with the DecryptByKey function, meaning you don’t need to issue an OPEN SYMMETRIC KEY to decrypt your cipher text. The DecryptByKeyAutoAsymKey function automatically opens an asymmetric key protecting a symmetric key, whereas DecryptByKeyAutoCert automatically opens a certificate protecting a symmetric key. If a password is used to encrypt your asymmetric key or certificate, that same password must be passed to these functions. If the asymmetric key is encrypted with the DMK, you pass NULL as the password. You can also specify an authenticator with these functions if one was used during encryption. Decryption of data in bulk using these functions may cause a pretty severe performance penalty over using the OPEN SYMMETRIC KEY statement and the DecryptByKey function.

Encryption Without Keys

SQL Server 2014 provides additional functions for encryption and decryption without keys and for one-way hashing, which is the concept of inputting a value into a function to get a hash value but not being able to use the hash value to reproduce the input. These functions are named EncryptByPassPhrase, DecryptByPassPhrase, and HashBytes, respectively.

The EncryptByPassPhrase function accepts a passphrase and clear text to encrypt. The passphrase is simply a plain text phrase from which SQL Server can derive an encryption key. The idea behind the passphrase is that users are more likely to remember a simple phrase than a complex encryption key. The function derives a temporary encryption key from the passphrase and uses it to encrypt the plain text. You can also pass an optional authenticator value to EncryptByPassPhrase if you wish. EncryptByPassPhrase always uses the 3DES algorithm to encrypt the clear text passed in.

DecryptByPassPhrase decrypts cipher text that was previously encrypted with EncryptByPassPhrase. To decrypt using this function, you must supply the same passphrase and authenticator options that you used when encrypting the clear text.

Hashing Data

The HashBytes function performs a one-way hash on the data passed to it and returns the hash value generated. HashBytes accepts two parameters: a hash algorithm name and the data to hash. The return value is a fixed-length varbinary hash value, which is analogous to a fingerprint for any given data. Table 8-2 lists the SQL Server-supported hash algorithms.

Listing 8-15 demonstrates the EncryptByPassPhrase, DecryptByPassPhrase, and HashBytes functions. The results are shown in Figure 8-7.

Listing 8-15. Encryption and Decryption by Passphrase and Byte Hashing

DECLARE @cleartext nvarchar(256);
DECLARE @encrypted varbinary(512);
DECLARE @decrypted nvarchar(256);

SELECT @cleartext = N'To be, or not to be: that is the question: ' +
    N'Whether ''tis nobler in the mind to suffer ' +
    N'The slings and arrows of outrageous fortune, ' +
    N'Or to take arms against a sea of troubles';

SELECT @encrypted = EncryptByPassPhrase(N'Shakespeare''s Donkey', @cleartext);

SELECT @decrypted = CAST
(
    DecryptByPassPhrase(N'Shakespeare''s Donkey', @encrypted)
        AS  nvarchar(128)
);

SELECT @cleartext AS ClearText;
SELECT @encrypted AS Encrypted;
SELECT @decrypted AS Decrypted;
SELECT HashBytes ('SHA1', @ClearText) AS Hashed;

Extensible Key Management

SQL Server 2014 contains a feature added in SQL 2008 known as EKM, which allows you to encrypt your SQL Server asymmetric keys (and symmetric keys) with keys generated and stored on a third-party HSM. To use EKM, you must first turn on the EKM provider enabled option with spconfigure, as shown in Listing 8-16.

Listing 8-16. Enabling EKM Providers

sp_configure 'show advanced', 1;
GO
RECONFIGURE;
GO
sp_configure 'EKM provider enabled', 1;
GO
RECONFIGURE;
GO

Once you’ve enabled EKM providers and have an HSM available, you must register a cryptographic provider with SQL Server. The cryptographic provider references a vendor-supplied DLL file installed on the server. Listing 8-17 gives an example of registering a cryptographic provider with SQL Server.

Listing 8-17. Registering a Cryptographic Provider

CREATE CRYPTOGRAPHIC PROVIDER Eagle_EKM_Provider
FROM FILE = 'c:Program FilesEagle_EKMSQLEKM.DLL';
 GO

After your EKM provider is registered with SQL Server, creating an asymmetric key that is encrypted by an existing key on the HSM is simply a matter of specifying the EKM provider, the CREATIONDISPOSITION option, and the name of the key on the EKM device via the PROVIDER_KEY_NAME option. Listing 8-18 gives an example.

Listing 8-18. Creating an Asymmetric Key with HSM Protection

CREATE ASYMMETRIC KEY AsymKeyEKMProtected
  FROM PROVIDER Eagle_EKM_Provider
  WITH PROVIDER_KEY_NAME = 'EKM_Key_1',
    CREATION_DISPOSITION = OPEN_EXISTING;
GO

EKM is designed to support enterprise-level encryption key management by providing additional encryption key security. It provides this additional security by physically separating the encryption keys from the data they encrypt. In addition to external storage of encryption keys, HSM vendors can also provide hardware-based bulk encryption and decryption functionality and external support for additional encryption options beyond what is supported natively by SQL Server 2014. Some of the additional options provided by HSM vendors include key aging and key rotation functionality.

Transparent Data Encryption

Up to this point, we’ve talked about the column-level encryption functionality available in SQL Server 2014. These functions are specifically designed to encrypt data stored in the columns of your database tables. SQL Server 2014 provides a method of encryption, TDE, which allows you to encrypt your entire database at once.

TDE automatically encrypts every page in your database and decrypts pages as required when you access them. This feature allows you to secure an entire database without worrying about all those little details that pop up when encrypting at the column level. TDE doesn’t require extra storage space, and it lets the query optimizer generate far more efficient query plans than it can when you search on encrypted columns. As an added bonus, TDE is easy to implement and allows you to secure the data in your databases with no changes to middle-tier or front-end code.

The first step to implement TDE in your database is to create a server certificate (see Listing 8-19). A server certificate is a certificate created in the master database for the purpose of encrypting databases with TDE.

Listing 8-19. Creating a Server Certificate

CREATE CERTIFICATE ServerCert
  WITH SUBJECT = 'Server Certificate for TDE',
  EXPIRY_DATE = '2022-12-31';
GO

Once you’ve created a server certificate, you can create a database encryption key in the database to be encrypted (see Listing 8-20). The database encryption key is created with the CREATE DATABASE ENCRYPTION KEY statement. Using this statement, you can create a key using one of the four different algorithms listed in Table 8-3.

Listing 8-20. Creating as Database Encryption Key and Securing the Database

USE AdventureWorks2014;
GO

CREATE DATABASE ENCRYPTION KEY
  WITH ALGORITHM = AES_128
  ENCRYPTION BY SERVER CERTIFICATE ServerCert;
GO

ALTER DATABASE AdventureWorks2014
SET ENCRYPTION ON;

GO

The obvious question at this point is, because TDE is so simple and secure, why not use it all the time? Well, the simplicity and security of TDE come at a cost. When you encrypt a database with TDE, SQL Server also encrypts the database log file and the tempdb database. This is done to prevent leaked data that a hacker with the right tools might be able to access. Because tempdb is encrypted, the performance of every database on the same server takes a hit. Also, SQL Server incurs additional CPU overhead because it has to decrypt noncached data pages that are accessed by queries.

Summary

Back in the days of SQL Server 2000, database encryption functionality could be achieved only through third-party tools or by creating your own encryption and decryption functions. SQL Server 2014 continues the tradition of T-SQL column-level encryption and decryption functionality introduced in SQL Server 2005. The tight integration of Windows DPAPI encryption functionality with native T-SQL statements and functions makes database encryption easier and more secure than ever.

SQL Server 2012 introduced new functionality, including TDE for quickly and easily encrypting entire databases transparently, and EKM for providing access to third-party HSMs to implement enterprise-level security solutions and bulk encryption functionality.

This chapter discussed the SQL Server hierarchical encryption model, which defines the relationship between SMKs, DMKs, certificates, asymmetric keys, and symmetric keys. SQL Server provides a variety of T-SQL statements to create and manage encryption keys and certificates, which you saw demonstrated in code examples throughout the chapter. SQL Server also provides several functions for generating one-way hashes, generating data signatures, and encrypting data by certificate, asymmetric key, symmetric key, and passphrase.

The next chapter covers the topics of SQL windowing functions and common table expressions (CTEs).