Configuring TDE in SharePoint Content Databases

Summary

Transparent Data Encryption is designed to protect data by encrypting the physical files of the database, rather than the data itself. Its main purpose is to prevent unauthorized access to the data by restoring the files to another server.  With Transparent Data Encryption in place, this requires the original encryption certificate and the master key.  It was introduced in the Enterprise edition of SQL Server 2008.
Encrypt include the database file (.mdf), the transaction log file (.ldf) and the backup files (.bak). 

Benefits

1.     Implementation of TDE does not require any schema modifications.

2.     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.

3.     The performance impact on the database is minimal. In their whitepaper titled "Database Encryption in SQL Server Enterprise Edition", Microsoft estimates the performance degradation for TDE to be 3-5%, while cell-level encryption is estimated to be 20-28%. Of course, the impact well may vary, depending upon your specific environment, and volume of data.

4.     The decryption process is invisible to the end user.

Disadvantages

1.     Use of TDE renders negligible 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.

2.     TDE does not provide the same granular control, specific to a user or database role, as is offered by cell-level encryption.

3.     TDE is available only with SQL Server Enterprise Editions and so will probably not be available to all installations within your environment.

Experience in the field

Suggestions

1.     Depending on the size of your farm, or you have one server specifically for the SharePoint Content Databases, or you create another SQL Instance, that's because the "tempdb" is encrypted also, of course, you don´t want to encrypt the Service Applications Databases. When you create a new instance, a new "tempdb" is created.

2.     Don't encrypt cells, encrypt the hole database

3.     For my experience, if you follow all Microsoft best practices, with no exception, I´ll say that you lose from 1-2% performance degradation

Encrypt

--- Check if encryption exists

SELECT name, is_encrypted

FROM SYS.DATABASES;

**
**

Encryption and backup script, run step by step

--Create a master key (and Password!) that will reside in the Master Database

USE [master];

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'somePassword';

GO

--Create a certificate protected with the Master Key

USE [master];

CREATE CERTIFICATE TDECertificate WITH SUBJECT = 'TDECert';

--Check Certificate name

SELECT name, pvt_key_encryption_type_desc

FROM sys.certificates;

--Create a Database Encryption Key

USE [YourContentDB];

GO

CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDECertificate;

GO

--Encrypt RESULT MUST BE "3"

USE [master];

ALTER DATABASE [YourContentDB] SET ENCRYPTION ON;

--BACKUP with CERT and PVK (DON'T LOSE IT)

BACKUP CERTIFICATE TDECertificate TO FILE='C:\BACKUP\TDECertificate.cert' WITH PRIVATE KEY

(FILE='C:\BACKUP\TDECertPrivateKey.key', ENCRYPTION BY PASSWORD='somePassword');

GO

Restore and Decrypt

--RESTORE with CERT and PVK

USE master;

CREATE CERTIFICATE TDECertificate

FROM FILE='C:\BACKUP\TDECertificate.cert' WITH PRIVATE KEY

(FILE='C:\BACKUP\TDECertPrivateKey.key', DECRYPTION BY PASSWORD='somePassword');

GO

Conclusion

Although the database is encrypted, and can't be decrypted without the CER, the data transmission is not, use an SSL Certificate to be fully secured. TEST and TEST before going into production.