Enabling Transparent Data Encryption for FIM 2010

Anyone who is a DBA or SQL person already knows what this is and how to implement it. I am writing this post to help those of us who are tossed into the database world without really being a DBA. As I took over new roles in my career like Forefront Identity Manager, I wanted to learn how to know the most about the engine driving the product. The engine to FIM along with many other great Microsoft products is SQL Server. This is a relational database which is different from say Microsoft Exchange which uses ESE (Extensible Storage Engine). ESE draws it’s information from “base types” and has no knowledge of any structure or schema. A relational database like SQL all structures are part of the meta-data, which is the database itself. When looking at something like FIM there in many times lots of personal information since the role of the product is to provision, de-provision, modify and synchronize user accounts and groups.

SQL Server databases in native form are unencrypted and easily viewable using a wide range of tools and methods. One way to view this would be opening the database file in Microsoft Notepad. This can be a huge liability if you have data stored in the unencrypted database that is sensitive. There are different levels of encryption that you can use to secure a database; this document will talk about Transparent Data Encryption. The other ways to encrypt data in SQL Server are using Symmetric Keys, Certificates and Asymmetric Keys. These methods are called cell based encryption. TDE gives you full database encryption, also there are Windows file based encryption methods (Bit locker and Encrypting File System “EFS”)

Transparent Data Encryption (TDE) was introduced in SQL Server 2008, as a feature of the Enterprise Edition. The Developer Edition of SQL Server 2008 also offers TDE, but its license limits its use to development and testing only. TDE’s specific purpose is to protect data by encrypting the physical files of the database, rather than the data. These physical files include the database file, the transaction log file and the backup files. The protection of the database files is accomplished through an encryption key hierarchy that exists externally from the database in which TDE has been enabled.

Transparent Data Encryption Features

Advantages and Disadvantages of TDE

TDE vs. Cell Level Encryption

Comparing TDE to cell-level encryption is a bit like comparing apples to oranges. They are solutions for different technical designs. TDE offers general protection to the database while cell-level encryption offers specific protection to data. A highly secure design would be to use TDE in conjunction with other encryption methods, for a layered approach to protection. If the amount of data that must be encrypted is very small or if the application can be custom designed to use it and performance is not a concern, cell-level encryption is recommended over TDE. Otherwise, TDE is recommended for encrypting existing applications or for performance sensitive applications. Cell-level encryption is available in all SQL Server editions (2005 and newer) while TDE is available only in SQL Server 2008 Enterprise Edition and SQL Server 2008 Developer Edition.

Advantages

• Implementation of TDE does not require any schema modifications.
• Since the physical data files and not the data itself are encrypted, the primary keys and indexes on the data are unaffected, and so optimal query execution can be maintained.
• The performance impact on the database is minimal. In their whitepaper titled “Database Encryption in SQL Server 2008 Enterprise Edition“, Microsoft estimates the performance degradation for TDE to be 3-5%, while cell-level encryption is estimated to be 20-28%.
• The decryption process is invisible to the end user.

Disadvantages

• Use of TDE renders any benefits to be gained from backup compression, as the backup files will be only minimally compressed. It is not recommended to use these two features together on the same database.
• TDE does not provide the same granular control, specific to a user or database role, as is offered by cell-level encryption.
• TDE is available only with SQL Server 2008, Enterprise Edition.
• In a replicated data design the data transmitted to the mirror is not encrypted, both the publisher and subscriber would need to have TDE enabled on the replicated database.

Considerations when Implementing TDE

Prior to implementing TDE, there are several issues to take into consideration.

Master Key Interdependency

The process of implementing TDE involves the creation of a database master key and certificate on the master database. Only one database master key can be created for a given database so any other user databases that share the instance, and have TDE implemented, will share a dependency upon the master database master key. This interdependency increases the importance of performing a backup of the master database master key to ensure the continued accessibility of the TDE-enabled databases.

TempDB Impact

When TDE is initially implemented, the physical file of the TempDB system database is also encrypted. Since the TempDB database contains temporary data from the TDE-enabled database, its encryption is required to maintain full protection by this feature; otherwise the information that is temporarily stored in the TempDB database from the TDE enabled databases would be exposed through the physical files of TempDB.

Once the TempDB database is encrypted, any reference and use of this database by other databases, regardless of whether they have TDE enabled or not, will require encryption and decryption. While this encryption and decryption of the TempDB database files remains transparent to the user, it does have a minimal performance impact on the entire instance. Microsoft has estimated the entire impact of TDE on a SQL Server instance to be 3-5% depending on the server environment and data volume.

FILESTREAM Data

The FILESTREAM data type stores large unstructured objects, such as documents and images, in an integrated physical file that is separate from the database file. When TDE is implemented on a user database that contains FILESTREAM data, the FILESTREAM files remain unencrypted.

Encryption & Decryption

TDE is designed to protect data by encrypting the physical data files rather than the data itself. This level of protection prevents the data and backup files from being opened in a text editor like Microsoft Notepad to expose the file’s contents.

TDE encryption occurs prior to writing data to disk, and the data is decrypted when it is queried and recalled into memory. This encryption and decryption occurs without any additional coding or data type modifications; hence the name “Transparent Data Encryption”. Alongside TDE, you should consider applying additional supporting layers of protection to your sensitive data, to ensure complete protection from unauthorized disclosure. For example, you may wish to implement, in addition to TDE, encrypted database connections, cell-level encryption, or one-way encryption. File level encryption is another option. For data in transit externally from the database, you may want to look at your security from the network aspect (Firewalls, Access Control Lists, DMZ’s).

Backup Keys and Certificate

TDE prevents the backup files from being opened by a plain text editor. It also limits the recovery of the database backup file to the instance that holds the encryption key hierarchy that was in existence at the time the backup was created. Backup files of databases with TDE enabled are encrypted using a key hierarchy that includes the service master key of the SQL Server instance, the database master key and certificate for the master database.

Despite this dependency, none of these keys are included with the standard database backup, and must be backed up separately via the following commands:

• BACKUP SERVICE MASTER KEY to backup of the service master key.
• BACKUP MASTER KEY to backup of a database master key.
• BACKUP CERTIFICATE to backup the certificate.

In order to restore the encrypted data to another instance of SQL Server, a user needs to recover the service master key backup file, the master database master key backup file and the master database certificate private key, prior to recovering the database backup file. The database encryption key that is created in the user database, where TDE has been implemented, is included in the standard database backup. When the service master key and database master key are backed up, it is recommended to store their backup files in a separate location from the database files.

Replication Databases

If the TDE enabled database is part of a replication setup, the subscribing database must also have TDE implemented. The data that is traveling between the databases will be in plain text and is vulnerable to unauthorized disclosure. Encrypting connections, such as secure socket layers (SSL) or Internet protocol security (IPSec) would be a method to secure data end to end. Also access control lists would be a method to limit communications between only servers part of the subscription to the database.

How to Implement Transparent Data Encryption

Let’s set up TDE on our example user database called “AT”. Our TDE implementation, in comparison to cell-level encryption, will be very simple. There are no modifications to the schema required, there are no permissions that need to be granted to database users and roles in order to use TDE, and there are no additional database objects that must be created to perform the encryption and decryption methods. What we will need is Control permissions on the Master and AT databases.

Backup before proceeding

It is a best practice to back up a database prior to making modifications. However, it is especially important when implementing TDE; this will be the quickest way to revert the database should the TDE implementation need to be reversed. The image below shows the T-SQL statement backup expression for the “AT” database. You could also use a maintenance plan if you have one setup to back up the database.

GOBACKUPDATABASE ATTODISK=’D:\AT\Backup\AT.bak’ WITHNOFORMAT, INIT, NAME =’AT-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS=10 GO

Create the Master Database Key

The first step is to create a database master key for the master database, using the CREATE MASTER KEY command, as shown in image 2.

USE Master; GO CREATEMASTERKEY ENCRYPTIONBYPASSWORD=’Str0ngPassword2012′; GO

Create the Self-Signed Certificate

The next step is to create a self-signed certificate that is protected by the database master key of our master database.

USE Master; GO CREATECERTIFICATEMyTDECert WITHSUBJECT=’My Server Cert for TDE’; GO

Create the AT Database Encryption Key

Next we will create the Database Encryption Key.

USE AT; GO CREATEDATABASEENCRYPTIONKEY WITHALGORITHM=AES_128 ENCRYPTIONBYSERVERCERTIFICATEMyTDECert GO

Backup the Certificate and Key

Our next step will be to back up the certificate and Key. This is very important; once you have run this command the key and certificate will be stored in the directory you specified. A best practice would be to copy these off to a remote location. A file server in a different datacenter would be the best option.

USE Master; GO BACKUPCERTIFICATEMyTDECert TOFILE=’E:\AT\Backup\MasterCert.cer’ WITHPRIVATEKEY ( FILE=’E:\AT\Backup\MasterCert.key’, ENCRYPTIONBYPASSWORD=’MyBackUpPa55word’); GO

Enable TDE on the AT Database

Our final step is to turn on TDE on the AT database.

USE AT; GO ALTERDATABASEAT SETENCRYPTIONON; GO

Verifying TDE

Once TDE is implemented there are a few ways you can verify that these steps indeed succeeded.

Using Dynamic Management Views

Dynamic management views (DMV) are built-in views that provide metadata regarding the settings, health and properties of SQL Server instances and databases. The “sys.dm_database_encryption_keys” DMV presents information about the database encryption keys used in a given database, as well as the encryption state of the database.

USE Master; GO SELECT db.name, db.is_encrypted, dm.encryption_state, dm.percent_complete, dm.key_algorithm, dm.key_length FROM sys.databasesdb LEFT OUTER JOIN sys.dm_database_encryption_keys dm ONdb.database_id = dm.database_id; GO

Through the use of a query in which the “sys.dm_database_encryption_keys” DMV and the “sys.databases catalog” view are joined through the database_id column, we are able to determine the success of the TDE implementation, as demonstrated in image below.

Another step to verify would be to back up the TDE enabled user database, then try and open it with a text editor. If TDE in implemented correctly your data should look like the image below.

Reversing TDE

To remove TDE before any transactions have occurred perform the following steps:

1. Restore the backup of the “AT” database that was created before TDE was implemented.

2. Drop the certificate that was created in the Master database. Remember the interdependency on key; if you enabled TDE on other user databases within this instance you should not drop the certificate.

3. Drop the database master key that was created in the Master database. Same rules apply about the interdependency on the master key; if you have other user databases using TDE do not drop the master key.

4. Restart the instance in which the “AT” database resides. If there are not any other user databases on the instance that have TDE implemented, this action will force the recreation of the TempDB database in an unencrypted format.

To remove TDE after transactions have occurred perform the following steps:

1. Alter the “AT” databaseto have the ENCRYPTION option set to the value of OFF.

2. Wait until the decryption process is complete. Use the sys.dm_database_encryption_keys DMV to determine its status. A value of “1” returned in the encryption_status column indicates that the decryption is complete.

3. Drop the database encryption key for the “AT” database.

4. Restart the instance in which the “AT” database resides. If there are not any other user databases on the instance that have TDE implemented, this action will force the recreation of the TempDB database in an unencrypted format.

In my opinion TDE offers a big bang for a small buck so to speak, you are able to implement a low performance cost solution that provides a huge upgrade in security compared to the default which is no encryption of the database and database backups. There are limitations like not having the granular control of cell based encryption that level of control comes with its own draw backs. The short answer is there is no one solution that fits all scenarios from the research I have done. An example would be a homegrown financial application that needs the highest level of security on certain tables would be a better fit for cell based encryption since you would have the option of designing the database to have the schema modifications needed and indexes created to work with cell based encryption. Another example would be a COTS application like Forefront Identity Manager 2010, you want some level of encryption but you can’t modify the schema and performance of the database is at a premium. Using either of these methods should also be combined with other security best practices like network security using firewalls; access controls lists, and file level security. Limiting the attack surface along with encrypting what is exposed are fundamental techniques in keeping data secure.