If you have worked with SQL Server for a given period of time chances are that you have experienced, witnessed, or at least familiar with the concept of backing up a database. This task is a primary staple in the DBA diet and should always include verification that the backups actually are valid.

An example of a statement that can be used to backup a SQL Server database is:

BACKUP DATABASE [DemoTDE]
    TO  DISK = 'C:\Backup\DemoTDE.bak'
    WITH NOFORMAT,
    INIT, 
    NAME = 'DemoTDE-Full Database Backup',
    SKIP,
    NOREWIND,
    NOUNLOAD, 
    STATS = 10
GO

When Transparent Data Encryption (TDE) is enabled on a database there are some additional dimensions that must be considered when performing a database backup.

BACKINGUP TDE ENABLED DATABASES
In my previous blog entry titled TDE: Under The Hood With TempDB I included the steps that are required to implement TDE. These steps include the creation of a Database Master Key (DMK), which is protected by the Service Master Key (SMK), as well as a Certificate that is protected by the DMK. All of these items reside in the MASTER database and are not included in the backup of the user database in which TDE is enabled or the MASTER database. To perform a backup of these items the following statements must be executed:

BACKUP SERVICE MASTER KEY
    TO FILE = 'C:\Backup\DemoTDE_SMK.bak'
    ENCRYPTION BY PASSWORD = 'MySMKBackupP@ssWord2009'           
GO

BACKUP MASTER KEY
    TO FILE = 'C:\Backup\DemoTDE_DMK.bak'
    ENCRYPTION BY PASSWORD = 'MyDMKBackupP@ssWord2009'  
GO

BACKUP CERTIFICATE MyServerCert
    TO FILE = 'C:\Backup\DemoTDE_CERT.bak'
GO

With the execution of the BACKUP DATABASE command and the three statements above you will have all of the files required to successfully recover your TDE enabled database. Please note that backing up the MASTER database or TEMPDB database is not required for recovering a TDE enabled user database.

The general database backup process often includes transferring the backup files to an external medium and stored in a safe location. When encryption is involved it is recommended to store the key backup files on a separate medium and location from the database backup file. This practice ensures that if the medium that contains the database backup file falls into malevolent hands the contents of the data remains secure since the keys are required to recover and access the data.

There are no additional backup requirements for the Database Encryption Key (DEK) which was created on the user database in which TDE was enabled. The DEK is actually stored in the user database's boot record and is included in the database backup. When the database is recovered the database boot record is accessed and the DEK is available for reference at that time.

RECOVERING TDE ENABLED DATABASES
When it comes time to recover a TDE enabled database there are a few variants in the approach depending upon the level of recovery required. For example: When the entire instance is requiring recovery, or the recreation of an instance is occurring on a separate server, the recovery of the SMK, DMK and Certificate will be required. If the recovery effort is focused on a specific user database within the instance of its origin the recovery of the database backup will often be sufficient.

If the SMK recovery is required you will need to carefully consider recovery or altering of all items within the instance that are protected by the SMK. The recovery of the SMK is performed by the following statement:

RESTORE SERVICE MASTER KEY
    FROM FILE = 'C:\Backup\DemoTDE_SMK.bak'                   
    DECRYPTION BY PASSWORD = 'MySMKBackupP@ssWord2009'  
GO  

The recovery of the DMK is performed by executing the following statement in the MASTER database:

RESTORE MASTER KEY
    FROM FILE = 'C:\Backup\DemoTDE_DMK.bak'
    DECRYPTION BY PASSWORD = 'MyDMKBackupP@ssWord2009'
    ENCRYPTION BY PASSWORD = 'MyNewDMKP@ssWord2009'
GO

Please note that we recovered the DMK using the protection of a password (ENCRYPTION BY PASSWORD) rather than the SMK. This allows the DMK to be recovered even in an instance in which the SMK is different from the instance in which the DMK was originally created. To change the DMK back to being protected by the SMK, the following series of statements are required in the MASTER database:

OPEN MASTER KEY
    DECRYPTION BY PASSWORD = 'MyNewDMKP@ssWord2009'
GO

ALTER MASTER KEY
    ADD ENCRYPTION BY SERVICE MASTER KEY
GO 

CLOSE MASTER KEY
GO

Please note that modification of the DMK requires it to be opened first.

Certificates are recovered through re-creation rather than recovery. If the Certificate used for TDE previously exists in the MASTER database it will need to be dropped prior to execution the following statement:

CREATE CERTIFICATE MyServerCert
    FROM FILE = 'C:\Backup\DemoTDE_CERT.bak'   
GO

At this point all of the keys that reside in the MASTER database have been recovered and the user database in which TDE has been enabled is ready to be recovered through the following statement:

RESTORE DATABASE [DemoTDE]
    FROM DISK = 'C:\Backup\DemoTDE.bak'
    WITH  FILE = 1, 
    NOUNLOAD, 
    REPLACE, 
    STATS = 10
GO

Once the database has been restored the success of its recovery can be determined through querying a table that resides within the database.

Disclaimer: All of the SQL Statements offered in this blog entry are provided as examples of their use and syntax. The arguments and their values used may vary depending upon your situation. Additional details regarding these statements can be obtained through SQL Server Books On Line. Always backup and test in a development environment before executing these suggested statements in a production environment.

There is a scene in the 1955 Looney Tunes short called "Sahara Hare" in which Yosemite Sam is attempting to enter a castle in the desert. After various failed methods of forced entry he encounters a secret entrance in the wall of the castle. When Sam opens the door there is another one immediately behind it. When that door is opened it reveals yet another one and so the scenario repeats until he encounters some strategically placed TNT. This segment of the cartoon reminds me of the encryption key hierarchy minus the destructive ending.

The series of keys that makes up the encryption key hierarchy protect each other until the key that used for the encrypting/decrypting process is revealed. The outer door being the Service Master Key (SMK) which protects an inner door, the Database Master Key (DMK) , which protects additional doors which are symmetric and asymmetric keys. With SQL Server 2008 and the introduction of Transparent Data Encryption (TDE) a new key was introduced into the mix: The Database Encryption Key (DEK).

On the surface it may be a little confusing when determining the difference between a Database Master Key (DMK) and a Database Encryption Key (DEK). The similarity between their names certainly add to the confusion. They both are addressing the encryption process and the use of the word "encryption" in its name does not provide any implication to its unique purpose. Under the hood, the differences become much easier to understand:

Database Master Key
This symmetric key is used to protect subsequent symmetric keys or asymmetric keys within the database that are utilized in the actual encryption/decryption process of the data. The algorithm used when a DMK is created is Triple DES. The key that protects the DMK is the Service Master Key or a user-supplied password. When the SMK is used to protect the DMK, the opening of the DMK occurs automatically; otherwise the DMK must be opened using the OPEN MASTER KEY statement.

The CREATE MASTER KEY command is used to create a DMK. The sys.symmetric_keys view will display the DMK for the database.

Database Encryption Key
This symmetric key is used only for TDE. The purpose of this key is to perform the encryption/decryption process on the physical files and filegroups of the database. The algorithm used when a DEK is created is determined based upon the WITH ALGORITHM argument and include various AES algorithms as well as Triple DES. The key that protects the DEK is a Certificate (Asymmetric Key) that resides in the MASTER database which is protected by the DMK for the MASTER database.

Since the DEK is used with the TDE process, the opening of this key is transparent to the end user.

The CREATE DATABASE ENCRYPTION KEY command is used to create a DEK. The sys.dm_database_encryption_keys view will display the DEK for the database.

I receive a daily feed from http://datalossdb.org/latest_incidents.rss which provides very basic information in regard to data loss events. These data loss events are not situations where a database crashes and data is lost, these are events in which valuable or sensitive data is disclosed to unauthorized parties. Examples of these events are:

• Names, addresses and social security numbers of college students are published on the Internet.
• A document containing sensitive information is found in a trash can.
• Sensitive data that is contained on a laptop which is stolen from an employee's or auditor's automobile.
• A disk containing sensitive information about a business' customers is lost.
• A hacker gains access to a server containing sensitive data.

I have been watching this feed for quite sometime and have been amazed at the frequency of these events. These occur on a daily basis and involves some rather significant organizations. It is very interesting to see that the events that are caused by hackers is a small percentage of the lot. Majority of them are the result of misplacement of data or irresponsible disclosure.

There is a lot that can be done to protect data while it is in storage. There is also a lot that can be done to protect data as it travels from the database to the user interface and back again. Role based security, encryption and other obfuscation methods provide the armored car affect for the data. Once data has been disclosed the imperfect human factor enters the picture.

A person who is otherwise authorized to view sensitive data might then save it in an Excel Spreadsheet on their laptop which gets stolen from their car. They might attach the information to an e-mail that they accidentally send to the entire company and their book club buddies. The might save that information on their favorite thumb drive which falls out of their pocket as they answer their cell phone... and the examples go on an on.

It is an excellent practice for the DBA or Developer to question the inclusion of sensitive data in an unprotected format on any vehicle of disclosure. The requestor may not realize that the data that they see every day could be considered sensitive. The requestor may not fully understand the consequences of further disclosing this data to potential unauthorized parties. It is very likely that the sensitive data may not be needed at all except for record identification purposes and an alternative piece of data could be recommended.

The practice of sending reports, data extracts or user interface displays that contain unprotected sensitive information to their recipients is not unlike attaching a scroll of paper to the leg of a pigeon in the old pigeon post days. While it is a very effective method of delivering data to those in need of it, the path is fraught with falcons and hunters awaiting to intercept the data.

At the heart of encryption are keys. These allow you, the authorized user, to unlock the subsequent key in a key hierarchy or the cipher text contained within a database. This hierarchical relationship in SQL Server 2008 is illustrated at http://msdn.microsoft.com/en-us/library/ms189586.aspx. For SQL Server 2005, the hierarchy is illustrated at http://msdn.microsoft.com/en-us/library/ms189586(SQL.90).aspx

Within SQL Server, the head of the encryption family is the Service Master Key. This key is a Symmetric key that is based off of the service account credentials as well as the machine key from the Windows Data Protection API (DPAPI). There is only one Service Master Key per instance of SQL Server and is created at setup of that instance.

The Database Master Key is a Symmetric key that is unique to each catalog (database) within the SQL Server instance. This key is encrypted using the Service Master Key of that instance. When a catalog is created the Database Master Key is not automatically generated. These keys are created using the CREATE MASTER KEY command.

Once the Database Master Key is created, additional keys can be created to increase the granularity of of the encrypted data. For example a table or cell can be encrypted with one key while another table or cell could be encrypted with a separate key. Both keys are therefore encrypted by the Database Master Key. These keys can fall into the following types:

Asymmetric Keys are the combination of a private key and a public key. The plain text is encrypted using the public key, which is distributed to others, and is decrypted by a corresponding private key which is very limited in its distribution. An Asymmetric Key is created using the CREATE ASYMMETRIC KEY command.

A Symmetric Key is a single key that is used to encrypt and decrypt data. A Symmetric Key can only decrypt the data that was encrypted by itself. As previously noted the Service Master Key and Database Master Key are considered this type of key. A Symmetric Key is created using the CREATE SYMMETRIC KEY command.

Certificates are private or public keys that are digitally associated with an individual or device. The use of a Certificate is very similar to Asymmetric Key. These can be created externally from SQL Server and can offer expiration management. To create a Certificate within SQL Server you will use the CREATE CERTIFICATE command.

When a database is backed up through the standard means, the keys that are applied to the instance and subsequent catalogs are not included. Each key must be individually backed up using the BACKUP SERVICE MASTER KEY, BACKUP MASTER KEY or BACKUP CERTIFICATE commands. A best practice is not to store these key backups on the same device as the database backup in the event that the device is compromised.

While a mile of text could be used to provide more details regarding keys, this cursory review should provide the novice cryptographer with some basic information that will help begin to lift the fog in the implementation of this valuable feature of SQL Server.

On the surface, the question of whether or not a database contains sensitive data may seem like a rather simple one to answer. Most people recognize that a Federal identification number or a credit card number is and should be recognized as sensitive data. While these pieces of data get a tremendous amount of attention by the media when data loss is reported there are other pieces of data that are not as easily recognized as being considered as sensitive.

The following categories of data are considered to be sensitive and should be protected:

Government Assigned Personal Identification data
This type of data includes Social Security numbers, tax identification numbers for businesses, driver license numbers and other data that the Federal, State or Local Governments have assigned to an individual or business for the purpose of identification.

Biometric data
As biometrics become utilized more often for the purpose of identification verification the importance of protecting this information becomes more critical. This type includes items such as retinal scan images, facial images, fingerprints and signatures.

Medical data
The Health Insurance Portability and Accountability Act (HIPAA) protects data in regard to medical and insurance information for patients. This includes notations in regard to conversations with your health care professional, physical and mental medical history as well as payment history of medical care. Unauthorized disclosure could result in civil and criminal penalties.

Student Education data
The Federal Educational Rights and Privacy Act (FERPA) protects data in regard to students and their educational records. This includes the student’s name, address, telephone number as well as information specifically regarding their education history. Unauthorized disclosure could affect a school’s Federal funding… not to mention compromising a student’s privacy.

Employment data
Items such as salary information, performance reviews, worker’s compensation claims, benefit information and pension plan details fall into this category. Any HR Professional will tell you that the unauthorized disclosure of such information could result in severe consequences.

Communication data
E-mail messages, telephone records and recordings, fax documents, text messages are all carriers of information that may contain data that would fall into any of these categories; Therefore, this information should be considered sensitive data.

Financial data
Financial data not only discloses information regarding an individual or business’ financial status it also often contains data that is used to gain access to assets. For example: bank account numbers, personal identification numbers and beneficiary information.

Intellectual Property data
Items that fall into this category are source code, schematics, details regarding a new product and also creative works such as images and written documents. The unauthorized disclosure of such information could destroy the competitive edge of a business or compromise the copyright claim by an author or artist.

As the DBA and Developer, we are typically the ones that implement encryption and other security measures in the database. We are often requested to provide extracts of data for use by external systems. We are also often requested to produce printed reports that present data for the use of Business Analysts to review. We are also a target for phishing or social engineering efforts to gain access to sensitive data.

Once data leaves the protected environment of the database the control of its dissemination becomes nearly impossible. The printed report could be passed around and end up in the hands of a person who will use the information for fraudulent activities. The spreadsheet that is generated by a SSIS Package or query could be stored on a laptop that is not password protected or encrypted which is stolen from the person’s automobile. The information could be attached or typed in an e-mail that was accidentally sent to a mailing list that contains hundreds of people.

While you cannot control the further disclosure of the data once it is in the requestor's possession the understanding of the data that is being stored in your database will go a long way in protecting your client's privacy, your employer's reputation and ultimately your job.