Safeguarding Confidential Data in SharePoint 2010 : Using SQL Transparent Data Encryption (TDE)

4/2/2011 3:22:59 PM
SQL Transparent Data Encryption (TDE) is a simple yet extremely effective solution that can be used to protect your SharePoint content databases. Consider the following information when implementing SQL TDE.

Understanding the Problem

By default, SharePoint data is secured by access control lists (ACLs), but the data in the database itself is not encrypted in any form. If a rogue agent were to gain access to either the SQL server or the SQL database backups, they would be able to overwrite SharePoint security ACLs and gain access to the data in the database quite easily.

For security and for compliance reasons, it may become necessary to enforce data encryption of the SQL databases. Within SQL Server 2008 and SQL Server 2008 R2 Enterprise edition, Microsoft includes a new feature known as Transparent Data Encryption (TDE) that allows for this type of functionality.

Encryption Solutions

TDE is actually only one of several SQL Server encryption solutions available. Each encryption solution works in different ways, however, so it is important to understand first what the available encryption solutions are and how they can be utilized.

  • Cell-level encryption— Cell-level encryption encrypts individual database cells, rather than the entire database. This type of encryption is not supported for SharePoint databases.

  • File-level encryption— File-level encryption includes technologies such as BitLocker and the Encrypting File System (EFS). These technologies encrypt the entire hard drive and can be used with SQL. They do not, however, encrypt backups of the SQL databases that are stored on other volumes.

  • Active Directory Rights Management Services (AD RMS)— AD RMS is an encryption solution that uses encryption techniques to enforce rights protection on data, restricting what a user can and can’t do with the data (for example, can’t print, copy/paste content).

  • Transparent Data Encryption— TDE is the ideal solution for SharePoint content database encryption because it encrypts the entire database while in storage, while being used in tempdb, and when backed up. In addition, the encryption is completely handled by SQL, and SharePoint does not even know that the encryption is taking place.

Understanding How TDE Works

There are several key things to note about TDE, as follows:

  • When enabled on a database, TDE encrypts the database, its associated log file, snapshots, backups, and mirrored database instances associated with that database, if applicable.

  • The tempdb for the SQL instance is also encrypted. This can affect other databases on the same instance. It is subsequently recommended to create a dedicated instance for encrypted databases so that they can have their own dedicated tempdb.

  • Backup cannot be restored to other servers if those servers do not have a copy of the private key used to encrypt. Stolen database files are subsequently worthless to the thief.

  • The overhead associated with enabling TDE is only a 3 percent to 5 percent performance penalty on the box, so minimal server resources are required to enable.

Understanding the TDE Key Hierarchy

TDE works by establishing a hierarchy of keys. It is critical to understand what these keys are and how they are used to encrypt each other. Figure 1 illustrates the key hierarchy used by TDE.

Figure 1. Understanding the TDE key hierarchy.

At the root, the Windows Data Protection API (DPAPI) is used to create and protect the service master key (SMK). The SMK is unique to each server and does not need to be backed up or recovered on any other systems. The SMK is then used to create and protect the database master key (DMK). The DMK is then subsequently used to create and protect the TDE Certificate, which in turn is used to create a Database Encryption Key (DEK), which encrypts the content DB itself.

Understanding TDE Requirements and Limitations

There are a few requirements and limitations to TDE that should be known in advance of its deployment, as follows:

  • The Enterprise edition of SQL Server is required for TDE. This version of SQL Server is considerably more expensive than the Standard edition.

  • TDE does not encrypt the communication channel. IPsec is the solution for this.

  • TDE cannot take advantage of SQL 2008 RTM/R2 backup compression.

  • Replication or FILESTREAM data is not encrypted when TDE is enabled.

  • The tempdb is encrypted for the entire instance, even if only one database is enabled for TDE.

  •  Safeguarding Confidential Data in SharePoint 2010 : Enabling SQL Database Mirroring
  •  Safeguarding Confidential Data in SharePoint 2010 : Outlining Database Mirroring Requirements
  •  Remote Administration of Exchange Server 2010 Servers : RDP with Exchange Server 2010 (part 2)
  •  Remote Administration of Exchange Server 2010 Servers : RDP with Exchange Server 2010 (part 1) - Planning and Using Remote Desktop for Administration
  •  Remote Administration of Exchange Server 2010 Servers : Using the ECP Remotely
  •  Safeguarding Confidential Data in SharePoint 2010 : Examining Supported Topologies
  •  SharePoint 2010 : SQL Server Database Mirroring for SharePoint Farms
  •  Remote Administration of Exchange Server 2010 Servers : Using the Remote Exchange Management Shell
  •  Remote Administration of Exchange Server 2010 Servers : Certificates, Trust, and Remote Administration
  •  Enabling Presence Information in SharePoint with Microsoft Communications Server 2010
  •  Integrating Exchange 2010 with SharePoint 2010
  •  Documenting an Exchange Server 2010 Environment : Exchange Server 2010 Project Documentation
  •  Documenting an Exchange Server 2010 Environment : Benefits of Documentation
  •  Getting the Most Out of the Microsoft Outlook Client : Using Cached Exchange Mode for Offline Functionality
  •  UML Essentials - UML at a Glance
  •  Understanding Microsoft Exchange Server 2010
  •  Working with Email-Enabled Content in SharePoint 2010
  •  Enabling Incoming Email Functionality in SharePoint
  •  Getting the Most Out of the Microsoft Outlook Client : Using Outlook 2007 (part 3) - Using Group Schedules
  •  Getting the Most Out of the Microsoft Outlook Client : Using Outlook 2007 (part 2) - Sharing Information with Users Outside the Company
    Top 10
    Nikon 1 J2 With Stylish Design And Dependable Image And Video Quality
    Canon Powershot D20 - Super-Durable Waterproof Camera
    Fujifilm Finepix F800EXR – Another Excellent EXR
    Sony NEX-6 – The Best Compact Camera
    Teufel Cubycon 2 – An Excellent All-In-One For Films
    Dell S2740L - A Beautifully Crafted 27-inch IPS Monitor
    Philips 55PFL6007T With Fantastic Picture Quality
    Philips Gioco 278G4 – An Excellent 27-inch Screen
    Sony VPL-HW50ES – Sony’s Best Home Cinema Projector
    Windows Vista : Installing and Running Applications - Launching Applications
    Most View
    Bamboo Splash - Powerful Specs And Friendly Interface
    Powered By Windows (Part 2) - Toshiba Satellite U840 Series, Philips E248C3 MODA Lightframe Monitor & HP Envy Spectre 14
    MSI X79A-GD65 8D - Power without the Cost
    Canon EOS M With Wonderful Touchscreen Interface (Part 1)
    Windows Server 2003 : Building an Active Directory Structure (part 1) - The First Domain
    Personalize Your iPhone Case
    Speed ​​up browsing with a faster DNS
    Using and Configuring Public Folder Sharing
    Extending the Real-Time Communications Functionality of Exchange Server 2007 : Installing OCS 2007 (part 1)
    Google, privacy & you (Part 1)
    iPhone Application Development : Making Multivalue Choices with Pickers - Understanding Pickers
    Microsoft Surface With Windows RT - Truly A Unique Tablet
    Network Configuration & Troubleshooting (Part 1)
    Panasonic Lumix GH3 – The Fastest Touchscreen-Camera (Part 2)
    Programming Microsoft SQL Server 2005 : FOR XML Commands (part 3) - OPENXML Enhancements in SQL Server 2005
    Exchange Server 2010 : Track Exchange Performance (part 2) - Test the Performance Limitations in a Lab
    Extra Network Hardware Round-Up (Part 2) - NAS Drives, Media Center Extenders & Games Consoles
    Windows Server 2003 : Planning a Host Name Resolution Strategy - Understanding Name Resolution Requirements
    Google’s Data Liberation Front (Part 2)
    Datacolor SpyderLensCal (Part 1)