SQL Server Data Encryption and Backup - any guidelines to follow?
When it comes to the data encryption within SQL Server 2008 you would think about Transparent data encryption (TDE) which is a new encryption feature.
It sounds good that this is a high level security feature you can deploy to secure the data, and a catch is you can do it without affecting existing applications. Traditional methods in implementing encryption in a database traditionally involves complicated application changes such as modifying table schemas, removing functionality, and significant performance degradations. A good example of taking the scenario within previous versions such as use of encryption in SQL Server 2005, the column data type must be changed to varbinary; ranged and equality searches are not allowed; and the application must call built-ins (or stored procedures or views that automatically use these built-ins) to handle encryption and decryption, all of which slow query performance. There may be other references to usage of third party tools in this regard but still they are not exceptional to this type of performance issues when you deploy other type of encryption with the application data management.
What TDE can offer to solve this issue?
TDE solves these problems by simply encrypting everything. Thus, all data types, keys, indexes, and so on can be used to their full potential without sacrificing security or leaking information on the disk. This works underlying feature of Windows Operating system using Encrypting File System (EFS) and BitLocker Drive Encryption (BDE) on a similar scale and are transparent to the user.
Good to know on more about SQL Server Encryption is we have 2 levels of encryption, database-level and cell-level that uses a key management type of method. Microsoft Technet documentation refers on methods on key management that (source & credit- Technet):
At the root of encryption tree is the Windows Data Protection API (DPAPI), which secures the key hierarchy at the machine level and is used to protect the service master key (SMK) for the database server instance. The SMK protects the database master key (DMK), which is stored at the user database level and which in turn protects certificates and asymmetric keys. These in turn protect symmetric keys, which protect the data. TDE uses a similar hierarchy down to the certificate. The primary difference is that when you use TDE, the DMK and certificate must be stored in the master database rather than in the user database. A new key, used only for TDE and referred to as the database encryption key (DEK), is created and stored in the user database.
This hierarchy enables the server to automatically open keys and decrypt data in both cell-level and database-level encryption. The important distinction is that when cell-level encryption is used, all keys from the DMK down can be protected by a password instead of by another key. This breaks the decryption chain and forces the user to input a password to access data. In TDE, the entire chain from DPAPI down to the DEK must be maintained so that the server can automatically provide access to files protected by TDE. In both cell-level encryption and TDE, encryption and decryption through these keys is provided by the Windows Cryptographic API (CAPI).
The following figure shows the full encryption hierarchy. The dotted lines represent the encryption hierarchy used by TDE.
.gif)
Figure: SQL Server encryption key hierarchy with TDE and EKM
So the next question will be how to enable TDE?
Books Online (BOL) documentation has lots of information and code examples to do so. Moreover you must ensure to have the normal permissions associated with creating a database master key and certificates in the master database. You must also have CONTROL permissions on the user database.
As per BOL (again)
Perform the following steps in the master database:
1. If it does not already exist, create a database master key (DMK) for the master database. Ensure that the database master key is encrypted by the service master key (SMK).
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘some password’;
2. Either create or designate an existing certificate for use as the database encryption key (DEK) protector. For the best security, it is recommended that you create a new certificate whose only function is to protect the DEK. Ensure that this certificate is protected by the DMK.
CREATE CERTIFICATE tdeCert WITH SUBJECT = ‘TDE Certificate’;
3. Create a backup of the certificate with the private key and store it in a secure location. (Note that the private key is stored in a separate file—be sure to keep both files). Be sure to maintain backups of the certificate as data loss may occur otherwise.
BACKUP CERTIFICATE tdeCert TO FILE = ‘path_to_file’ WITH PRIVATE KEY (FILE = ‘path_to_private_key_file’,ENCRYPTION BY PASSWORD = ‘cert password’);
4. Optionally, enable SSL on the server to protect data in transit.
Perform the following steps in the user database. These require CONTROL permissions on the database.
5. Create the database encryption key (DEK) encrypted with the certificate designated from step 2 above. This certificate is referenced as a server certificate to distinguish it from other certificates that may be stored in the user database.
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE tdeCert
6. Enable TDE. This command starts a background thread (referred to as the encryption scan), which runs asynchronously.
ALTER DATABASE myDatabase SET ENCRYPTION ON
To monitor progress, query the sys.dm_database_encryption_keys view (the VIEW SERVER STATE permission is required) as in the following example:
SELECT db_name(database_id), encryption_state FROM sys.dm_database_encryption_keys
As mentioned before TDE is designed to be as transparent as possible, virtually with no application changes are required. The only task you need to ensure is not to have any READONLY filegroups on the database and still there is a possibility to perform TDE as such, that involves the filegroups must first be set to allow writes. After the encryption scan completes, the filegroup can be set back to read only. Key changes or decryption must be performed the same way.
The impact as such of CPU & memory usage is minimal if you want to compare the system resource monitor during the TDE tasks, as the whole process of encryption involves the database level access that can leverage indexes and keys for query optimization. But the main task of encryption is CPU intensive and is performed at I/O cost, so you need to ensure to schedule or perform TDE during a low period of I/O & usage on the server.
Don't assume that TDE can be used to restrict the access to data or control the accessibility, all it performed is dependant on users who have permission to access the database are still allowed access; they do not need to be given permission to use the DEK or a password. As per the documentation TDE feature is only available on Enterprise edition and Developer edition (which is not for production usage) which means other editions cannot be encrypted by using TDE and TDE-encrypted databases cannot be used in other editions. If you attempt to perform TDE such as restore from a TDE'd database then the server will error out on attempts to attach or restore.
SO how about backups when you use the TDE?
Obviously the database backups are encrypted too when TDE is enabled. The backup encryption will follow the same method as it performed on usual data encryption method, so you need to ensure or take utmost care to keep the certificate and if this is lost, then the data will be unreadable. So the best practice is to backup the certificate along with the database and store it on same directory or server in order to access when the restore task is performed. Bear in mind the certificate will have 2 files and both of them should be seperated from the database backup file for obvious security reasonse and you can consider using the EKM feature to store & maintain the keys used for initial TDE process.
There may be discussions on using Encryptng File System methods rather than TDE directly and just a last thought on what kind of disadvantage will be in using EFS will be that primarily in performance and administration. EFS is not designed for high-concurrency random access and I/O operations may become bottlenecked and serialized. Such factors and information are already documented within KBA You may experience decreased performance in some features of SQL Server 2005 when you use EFS to encrypt database files & EFS and Vista... and XP blog entry. Due the fact that involvement of additional manpower when decrypting the EFS mechanism on operating system, as most of the Enterprises may not have similar levels of Operating System Administrator level privielges to the DBAs for obvious reasons.
So next time when you have a the data encryption requirement then think about using or testing TDE methods on SQL Server 2008, as the different levels of encryption available in SQL Server and Windows can be leveraged to provide defense in depth and greater overall security. Transparent data encryption provides a good blend of ease of administration, ease of use, performance, and security. TDE also provides a comprehensive defense because the encryption stays with the database even when it is moved to different locations. Also you need to wait upon what SQL Server 2008 RTM can offer in terms of the new features that are discussed earlier.