An organization can take several precautions to help secure a database, such as designing a secure system, encrypting confidential assets, and building a firewall around the database servers. However, in a scenario where the physical media (such as disk drives or backup tapes) are stolen, a malicious party can simply restore or attach the database and browse the contained data. One solution is to encrypt the sensitive data in the database and protect the keys that are used to encrypt the data with a certificate. This prevents anyone without the keys from using or accessing the data, but this kind of protection must be planned in advance.
As of SQL Server 2008 a new feature called Transparent Data Encryption (TDE) was introduced into the RDBMS product. The new feature differs significantly from Data Encryption introduced with SQL Server 2005, which could be used to encrypt data of certain columns in tables. Microsoft received the following feedback from customers and partners, especially from larger ISVs:
Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module. TDE protects data "at rest", meaning the data and log files. It provides the ability to comply with many laws, regulations, and guidelines established in various industries. This enables software developers and more specifically system administrators to encrypt data by using AES and 3DES encryption algorithms without changing existing applications.
Two very important technical and operational aspects to consider before implementing SQL Server TDE, is the impact on database performance (CPU and I/O) and the management of certificates and private keys. Activating TDE will directly impact the database performance to varying degrees, depending on which encryption algorithm is chosen. This impact is discussed in more detail in Step #3 below.
Additionally, the management and backup of database certificates and keys are vital to ensure that the encrypted database can be restored in the advent of a disaster. If you cannot retrieve your database certificate and private keys, the data is lost! Step #7 discusses the procedures for creating file level backups of these keys and certificates. SAP fully supports TDE on SQL Server databases and OSS note 1380493 can be referenced for more information.
Step #1 – Verify SAP & SQL Server
Verify SAP & SQL Server before starting the TDE procedure. Login to SAP and execute DBACOCKPIT to display some basic database information thus verifying the database is open and working satisfactorily.
Next, using SQL Server Management Studio to verify the database options showing that encryption (TDE) is not enabled.
Step #2 – Full Backup
Execute a full database backup including transaction logs before starting the TDE procedure. Using SQL Server Management Studio or any 3rd party backup software to start and execute the full backup. Be sure to provide a descriptive label including the text that this backup is not encrypted.
After the backup completes, verify with DBACOCKPIT and/or SQL Server Management Studio or any 3rd party backup software.
Step #3 – Encryption Type
Before proceeding, a decision must be made regarding the encryption algorithm that will be used. Microsoft supports multiple encryption algorithms which have different levels of performance and encryption. The table below depicts a simple load comparison conducted using various encryption algorithms and base lined with no encryption.
Supported encryption algorithms are AES with 128-bit, 192‑bit, or 256‑bit keys or 3 Key Triple DES (see Database Encryption in SQL Server 2008 Enterprise Edition at the end of this post for more information).
The Microsoft LoB CoE recommends using AES_256 or lower for SAP systems in general. Special consideration must be given to systems which already are I/O bottlenecked because of the increased encryption overhead caused by the data getting encrypted/decrypted in the I/O path. Therefore, servers with low I/O and a low CPU load will have the least performance impact. Applications with high CPU usage will suffer the most performance loss.
Selecting a higher encryption level will exacerbate this degradation and large scans which mainly happen on disk will be slowed down. In particular, TRIPLE_DES has significant higher impact than one of the AES algorithms.
Step #4 – Stop SAP
Before starting the encryption procedure, we recommend that all SAP applications utilizing the database be shut down. Stopping the SAP application prevents any DDL statement from being executed from the SAP data dictionary or via SAP transports.
The following operations are not allowed during initial database encryption, key change, or database decryption:
The following operations are not allowed during the CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY, or ALTER DATABASE...SET ENCRYPTION statements.
The following operations or conditions will prevent the CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY, or ALTER DATABASE...SET ENCRYPTION statements.
Step #5 – Create the Master Key
Create a master key that applies within the entire SQL Server instance. This creates the database master key (DMK), which again is used to encrypt certificates to be created for database encryption. The master key itself is protected by the Service Master Key, which is automatically generated when SQL Server is installed. Be aware that the password needs to accommodate the default Windows password policies if those are enabled on the server you are creating the certificate on.
Step #6 – Create the Certificate
Create a certificate that is also valid for the entire SQL Server. Because no specific encryption password is used, the DMK is used as encryption password.
Step #7 – Backup the Key and Certificate
If the local security certificate is used, back up the certificate. If possible, the backup location should be on a different server. If you cannot retrieve this certificate, the data is lost. It doesn't matter whether there is a valid backup of the encrypted database. In order to restore the backup, a valid certificate is needed. If the only location of the certificate is the production SQL Server instance and the server goes down or the SQL Server instance is lost, the backup is not worth a cent. Therefore it doesn't make sense to keep the backups of the certificates on the same server; they should be stored on a different computer.
The following procedure creates a file level backup of the database certificate (.CER) and corresponding private key (.PVK). The private key file is also encrypted with an additional password as shown in the screenshot.
Strategies for Managing Keys
The illustration below shows the architecture of TDE encryption and how each key and certificate is protected. In general the generated certificate and keys are stored in the master database within the SQL Server instance. To ensure that the keys are available after a disaster should the SQL Server instance be lost, the keys should be stored in additional locations and media independent of the database server. The following scenarios can be considered:
Step #8 – Link the master key, certificate and database
Link the master key, the certificate, and the database to be encrypted. This is done by creating an encryption key for the specific database to be encrypted.
Step #9 – Encrypt the database
Execute the database encryption command per the screenshot below:
Step #10 – Monitoring the progress
While the background processes continue to work, this query can be used to get an overview of the progress of work. The progress of the encryption can be monitored using the following transact SQL statement:
SELECT DB_NAME(database_id), encryption_state, key_algorithm, key_length, percent_complete FROM sys.dm_database_encryption_keys
The first column will show the database name. The second column will have a value from 1 to 5 where the values stand for:
You will also notice additional CPU utilization for the SQLSERVR.EXE process as shown below via task manager:
Executing the query after encryption is completed displays the encryption_state = 3 and percent_complete = 0.
Step #11 – Start SAP
After the encryption process has completed, start the SAP system. No additional changes or profile parameters are required for SAP to function correctly.
Step #12 – Verification
After the encryption process has completed and the SAP system restarted, small verification checks can be executed to ensure that the system is stable and working correctly. Using SQL Server Management Studio, open the database parameters options and looks at the encryption setting:
DBACOCKPIT can also be executed again to verify that SAP and the database are communicating correctly:
Step #13 – Full Backup
Finally, execute a full database backup including transaction after the TDE procedure. Using SQL Server Management Studio or any 3rd party backup software to start and execute the full backup. Note that this backup is now encrypted and can only be restored if the database certificate and private key are present on the target SQL Server instance. Any restore will fail if the keys are not installed and available to SQL Server.
One important aspect to note about SQL Server backups after enabling TDE is that compression rates fall significantly. TDE destroys recurring patterns in the data stream that the compression algorithms use. Our engineering experience has shown that backup compression rates drop to only 1% on average when TDE is enabled and thus the resulting backup set is much larger.
The process of encrypting your Microsoft SQL Server 2008 database is fairly straight forward and painless. The LoB CoE highly recommends performing this process multiple times in a non-production system to ensure that the results are understood and validated before moving into your "live" SAP environments. Additionally, please ensure that the Microsoft KB article 2300689 is referenced and applied before enabling or disabling TDE.
A special thanks to Juergen Thomas and Raymond Smith for helping with this content. Part #2 - Impacts of TDE and Disabling/Removing TDE will be published mid-December 2011 and I expect a total of 4 blogs on this topic:
Related SAP OSS Notes1380493 - SQL Server Transparent Data Encryption (TDE)
Restore error after disabling TDEKB - 2300689 - http://support.microsoft.com/kb/2300689
Database Encryption in SQL Server 2008 Enterprise Editionhttp://msdn.microsoft.com/en-us/library/cc278098
Understanding Transparent Data Encryption (TDE)http://msdn.microsoft.com/en-us/library/bb934049.aspx
How to: Enable TDE Using EKMhttp://msdn.microsoft.com/en-us/library/cc645957.aspx
SAP with Microsoft SQL Server 2008 and SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalabilityhttp://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/SAP_SQL2008_Best Practices_Part_I.docx
One of my esteemed colleagues has provided some internal feedback that I would like to share with people reading this blog.
Firstly, we cannot over stress the condition that customers/partners must manage the certificates with NO room for error. The database cannot be restored or recovered without a valid certificate and thus we recommend having multiple copies of the certificate (*.CER) and private key (*.pvk) files if possible.
DATA LOSS WILL OCCUR if the certificates are lost or destroyed! Period...
Second, we encourage customers/partners to perform some stress or performance benchmark testing post TDE to establish the overall performance impact. I will attempt to provide some estimates in my next blog post for the expected duration of the TDE encryption for a sample database sizes.