Welcome to my latest article, which looks into the encryption hierarchy within SQL Server. There still seems to be an increased amount of confusion as to how this security side of SQL Server actually works. I want to focus mainly on Service Master Keys, Database Master Keys and Server Certificates. These are all required in the support of Transparent Database Encryption and Encrypted Backups (new in SQL Server 2014).

Firstly, I would like to refer back to the SQL Server encryption hierarchy provided in Books Online by Microsoft Corporation.

More information may be found on the encryption hierarchy at this link: https://msdn.microsoft.com/en-us/library/ms189586.aspx.

It's important to understand the difference between Symmetric keys and Asymmetric keys. There are advantages and disadvantages of both, specifically, Symmetric keys are quick and easy to apply and use the same security descriptor for encryption and decryption. Asymmetric keys have extra layers of protection such as a public key and a private key pair for encryption and decryption, this process is slower however and usually requires more CPU effort.

Service Master Key

Looking at the above hierarchy we can see, as the root node, the Service Master Key. This sits at the top of the encryption hierarchy and is the principal under which all objects are secured. This key is a Symmetric Key also used within the instance to directly encrypt remote login details for items such as Linked Servers.

The key itself is created by the SQL Server setup and is generated by the Windows Data Protection API. The key used by the API is defined by the computer credentials and the service account credentials used for the SQL Server instance.

This symmetric key is unique to each instance based on how it is generated. Backups of the key should be held, but note that they should not be restored across instances.

Database Master Key

The next Symmetric key used is the Database Master Key (DMK). This is created primarily in the master database. This key is then used to secure server wide encryption descriptors, such as certificates used in Transparent Database Encryption (TDE) or Encrypted Backups. Database Master Keys may also be created within user databases in the pursuit of object level encryption.

For server wide encryption functions, the database master key must be manually created upon it's first usage requirement. For example, if a valid DMK does not exist for an instance when attempting to create a server certificate, the database engine will direct you to go create one as shown in the error message in the screenshot below.

You create the database master key using the following T-SQL command

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd1'

When creating the DMK, a copy is automatically encrypted by the SMK to avoid having to open the key each time it is required.

For server wide keys stored in the master database, this key is, or at least should be, unique between instances. There is no direct dependency on the DMK by any server wide certificate. The certificate's private key is merely encrypted by the DMK whilst it resides within the SQL Sever instance.

There's a fair degree of misunderstanding around the DMK, related mainly around Transparent Database Encryption and Encrypted SQL Server backups. It's wrongly assumed that you need to backup and restore the DMK as well as the certificate from a source SQL Server instance to a target instance when moving an encrypted backup (Databases using TDE automatically have their backups encrypted too). This is incorrect and not advisable, it could also cause the restore to fail and I'll detail why.

As we know from the information above, When you create a Database Master Key in the master database, you specify a password and the key is created. By default the database engine automatically protects the DMK by encrypting it with the Service Master Key. This enables the key to be seamlessly opened when required.

Now, If you restore a DMK from another instance and the key does not already exist on the target (as it likely won't), the default behaviour is different. The default here is to create the key without encryption by the SMK, this means the key must be manually opened each time you restore the database, something which the GUI cannot do.

Books Online details this: 

If there is no master key in the current database, RESTORE MASTER KEY creates a master key. The new master key will not be automatically encrypted with the service master key.'

So, if you restore the DMK from one instance to another using the process above and then restore your certificate everything will seem just fine. It's not until you attempt to restore the database that you'll get a failure as the DMK cannot be silently opened, allowing access to the server certificate.

There is an option to cure this but as already stated, you should ensure your DMKs are unique across instances. If you do wish to use the restored DMK you will need to perform the following:

--Open the DMK 
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'blahblahblah' 
--now set encryption by the SMK 
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Self Signed Certificates

A self signed certificate is an Asymmetric key that is created and authentically marked by an application service, in this case the SQL Server database engine. Certificates marked as self signed are generally thought of as unsecure as they are not created and signed by a recognised certificate provider.

In reality, it all depends on the ability of the application service to use recognised encryption libraries and algorithms to produce certificates which have a high degree of security. We've all seen the recent horror stories in the last few years whereby major encryption\security provider 3rd parties have had their libraries compromised by hackers.

Exactly which provider you use will depend on your corporate policy. For the most part a self signed certificate will possibly suffice. With all this in mind, let's take a look at how our symmetric and asymmetric keys come together to protect our TDE database or encrypted SQL Server backup.

First comes the Service Master Key, this in turn protects the Database Master Key. The Database Master Key protects the certificate's private key, the certificate protects the TDE enabled database or Encrypted SQL Server backup.

Transparent Database Encryption

Typically when we setup TDE we use the following process. We create the Database Master Key.

--To create a master key, this is required before enabling your instance for TDE.
--By default its protected by the service master key
USE [master];
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd1';
GO

We create the certificate in the master database.

--Now you have a master key, you need to create a certificate for your TDE database
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate',
START_DATE = '01/01/2014', EXPIRY_DATE = '01/01/2030'
GO

We create the Symmetric key in the user database.

--Create database encryption key
USE [somedb]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert
GO

Finally we enable the database for encryption.

--turn on encryption for database
ALTER DATABASE [somedb]
SET ENCRYPTION ON
GO

If you're wise and don't want to be typing your resume anytime soon, you'll backup the certificate.

--More importantly backup the cert as this is all you need to recover access
--to your TDE protected database even if moving it to another server
BACKUP CERTIFICATE MyServerCert TO FILE = 'C:\MyEncryptionCert.certbak'
   WITH PRIVATE KEY (
         FILE = 'C:\MyEncryptionCert.pkbak',
         ENCRYPTION BY PASSWORD = 'Pr!vK3yP@ssw0rd'); --protect private key with this password

Each of these steps relates to a level within the encryption hierarchy.

Encrypted Backups

When creating encrypted backups we use this process. First, we create the Database Master Key.

--To create a master key, this is required before enabling your instance for TDE.
--By default its protected by the service master key
USE [master];
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd1';
GO

We create the certificate in the master database.

--Now you have a master key, you need to create a certificate for your TDE database
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate',
START_DATE = '01/01/2014', EXPIRY_DATE = '01/01/2030'
GO

If you're wise and don't want to be typing your resume anytime soon, you'll backup the certificate.

--More importantly backup the cert as this is all you need to recover access
--to your TDE protected database even if moving it to another server
BACKUP CERTIFICATE MyServerCert TO FILE = 'C:\MyEncryptionCert.certbak'
   WITH PRIVATE KEY (
         FILE = 'C:\MyEncryptionCert.pkbak',
         ENCRYPTION BY PASSWORD = 'Pr!vK3yP@ssw0rd'); --protect private key with this password

Finally, we may the use following code sample to enable encryption for our database backup.

--backup via T-SQL using the following command  
BACKUP DATABASE [AdventureWorks2014_ENCBACKUP] TO
DISK = N'E:\Bak\MSSQL12.INST1\MSSQL\Backup\AdventureWorks2014_ENCBACKUP.bak'
WITH INIT, MEDIANAME = N'Encrypted backup'
NAME = N'AdventureWorks2014_ENCBACKUP-Full Database Backup',
COMPRESSION, ENCRYPTION ( ALGORITHM = AES_256, SERVER CERTIFICATE = [MyNewCert] )

We've looked at the encryption hierarchy and you should now have a better understanding of how this works.The various levels are protected by a parent up to the root where the Service Master Key sits, this is the root principal.

It would certainly help for you to go through the TDE and Encrypted Backup processes to fully understand exactly what is happening. Of course, please feel free to post in the discussion and I'll help all can.