Partager via

SQL Server TDE with SAP Applications - Part #1 Enabling TDE

  • Article

Introduction

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:

  • The encryption technology should be transparent to the underlying applications. The emphasis of such a technology would be less on protecting the data from user access, and more on protection from media loss (backup tapes or disks) or media theft.
  • Data encryption should cover the entire database.
  • Data encryption should be extended to database backups. 

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 by Step Procedure 

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:

  • Dropping a file from a filegroup in the database
  • Dropping the database
  • Taking the database offline
  • Detaching a database
  • Transitioning a database or filegroup into a READ ONLY state

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.

  • Dropping a file from a filegroup in the database.
  • Dropping the database.
  • Taking the database offline.
  • Detaching a database.
  • Transitioning a database or filegroup into a READ ONLY state.
  • Using an ALTER DATABASE command.
  • Starting a database or database file backup.
  • Starting a database or database file restore.
  • Creating a snapshot.

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.

  • The database is read-only or has any read-only file groups.
  • An ALTER DATABASE command is executing.
  • Any data backup is running.
  • The database is in an offline or restore condition.
  • A snapshot is in progress.
  • Database maintenance tasks.


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:

  1. Using a secondary SQL Server database to store the certificates and keys. A smaller SQL Server 2008 instance or even SQL Server 2008 Express instance can be used to accomplish this scenario. The secondary instance must be 100% independent from the encrypted database and should not share any infrastructure components.
  2. Storing the backup of the database certificate (.CER) and corresponding private key (.PVK) on a third party file repository with Access Control Lists (ACL). One example would be creating a SharePoint repository, setting the appropriate ACL and then storing these files with that repository. Additionally, to simplify the management of the file pair, both files can be consolidated into a single password protected ZIP file before storage.
  3. Storing the backup of the database certificate (.CER) and corresponding private key (.PVK) on removable media such as a USB flash drive and then physically securing the USB flash drive. Additionally, to simplify the management of the file pair, both files can be consolidated into a single password protected ZIP file before storage.
  4. Using an extensible key management (EKM) provider to generate and store the keys. The procedures for this step will vary depending on the requirements of the EKM provider, but might include installing a certificate from the EKM provider in the local certificate store, and copying EKM DLL files onto the SQL Server computer. Using an EKM provider is not in scope for this article and thus not discussed further in this document. Using this key/certificate management mechanism would be preferable when multiple systems/databases require encryption. The LoB CoE together with our engineering colleagues are considering a more in depth and focused blog entry for the future.   

 

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:

  1. Unencrypted
  2. Encryption in progress
  3. Encrypted
  4. DEK change in progress
  5. Decryption in progress

 

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.

Conclusion

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:

  • Part #1 - Enabling TDE
  • Part #2 - Impacts of TDE and Disabling/Removing TDE
  • Part #3 – System Copy with TDE
  • Part #4 – Log Shipping and Database Mirroring with TDE

 

Additional Information

Related SAP OSS Notes
1380493    - SQL Server Transparent Data Encryption (TDE)

Restore error after disabling TDE
KB - 2300689 - https://support.microsoft.com/kb/2300689

Database Encryption in SQL Server 2008 Enterprise Editionhttps://msdn.microsoft.com/en-us/library/cc278098

Understanding Transparent Data Encryption (TDE)
https://msdn.microsoft.com/en-us/library/bb934049.aspx

How to: Enable TDE Using EKM
https://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 Scalabilityhttps://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/SAP_SQL2008_Best Practices_Part_I.docx

Comments

  • Anonymous
    January 01, 2003
    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. Cheers, Fazil.