Securing the DB2 Database of your SAP System with Windows Encrypting File System

(1)

Securing the DB2 Database of your

SAP System with Windows

Encrypting File System

Applies to:

All SAP releases on IBM DB2 for Linux, UNIX, and Windows (in the following referred to as DB2 for LUW) on a Windows platform.

Summary

This document describes how to encrypt the database of your SAP system with Windows Encrypting File System (in the following referred to as EFS). You can use this document as a step-by-step guide to enable encryption on your existing SAP system that runs on DB2 for LUW. This document also provides

performance data based on a test analysis that was done at the IBM SAP Integration and Support Centre. Authors: Lili Zhang, Sunny Sun

Company: IBM, SAP (IBM SAP Integration and Support Centre, Toronto) Created on: November 2010

Author Bio

Lili Zhang is a member of the IBM SAP Integration and Support Centre at the IBM Toronto Lab. Her current activities include testing of SAP applications with DB2 for LUW and helping customers with problem analysis and troubleshooting. She is also a customer advocate, providing support for large customer accounts running SAP and DB2 for LUW.

(2)

Overview

Data security is crucial in today’s business as the computing environment is subject to malicious attacks and increasing regulatory pressures. The Encrypting File System (EFS) on Microsoft Windows provides NTFS file system encryption which enhances data security for your sensitive data. You can encrypt individual files or folders on NTFS drives. When the files are encrypted, the data is protected even if the attacker has physical access to the machine. Only authorized users and data recovery agents can decrypt the files.

With EFS, you are able to encrypt your database backup image or tablespace container files to protect sensitive data in your database. This document guides you through the steps needed to encrypt the existing database of your SAP system with EFS. You can also use the performance result as a reference when

considering encryption using Windows EFS on your system

_.

Introduction to Encrypting File System (EFS)

EFS was first introduced in version 3.0 of NTFS and supported as of Windows 2000. It uses a combination of public-private key pair and a per-file encryption key to encrypt and decrypt data. To encrypt a file, EFS generates a random symmetric key, also known as file encryption key (FEK) to encrypt the data. The FEK is then encrypted with the user’s public key from the encryption certificate and is added to the file as an EFS attribute named Data Decryption Field (DDF). To access an encrypted file, you must have the private key that corresponds to one of the public keys used to decrypt the FEK first. The decrypted FEK is then used to decrypt the data.

To protect against accidental loss of your data, EFS provides a data recovery capability. The capability is defined by a recovery policy where you can configure one or more data recovery agents (DRAs). A recovery agent is a privileged user account (for example, Administrator) that can recover data from any encrypted file in the event of lost or corrupted keys for authorized users. If a recovery policy is in place, the FEK is

additionally encrypted with the recovery agent’s public keys and stored in the Data Recovery Field (DRF) of the file. The recovery agent uses its corresponding private keys to decrypt the FEK, and the encrypted files will be recovered.

Figure 2-1 Structure of an Encrypted Data File

In general, the encryption process involves the following steps:

 EFS starts by performing a number of verification checks. These checks include whether the file can

be encrypted and whether there is enough disk space to encrypt the file. If the file is marked as a system file or resides in the %systemroot% directory, it cannot be encrypted.

 EFS then generates a file encryption key (FEK). By default, EFS uses the Advanced Encryption

(4)

 The FEK is encrypted with the current user's public key. If a recovery agent is defined, the FEK is also encrypted with the recovery agent’s public keys.

 EFS metadata is created. EFS metadata contains the Data Decryption Field (DDF), which consists of

the encrypted FEKs for all users that have been given access to the file. It also contains the Data Recovery Field (DRF), which contains the encrypted FEKs for all defined recovery agents. Other information, such as the EFS version and encryption algorithm, is also stored in the metadata.

 A temporary file is created in the current directory. The source file is copied to the temporary file for backup purposes. Each data stream in the original file is then truncated to a length of zero. Its length is then set back to its original size. This essentially deletes all the data in the stream. Next, EFS writes the metadata to the original file. At this point, EFS has the plaintext data in a temporary file and an empty source file that is marked encrypted because of the presence of the EFS metadata.

 EFS reads the data streams from the temporary file and writes them to the original file. The data is encrypted before it is written to disk.

 After all of the data has been written back to the original file, EFS verifies that the file is encrypted and then deletes the temporary file. If the conversion process fails for some reason, EFS restores the file to its original state.

Since the encrypting and decrypting processes are fully integrated at NTFS file system level, they are totally transparent to the users and applications. From the user’s point of view, encrypting a file is simply a matter of setting a file or a folder’s attribute.Authorized users can work with the files as usual while non-authorized users receive an Access denied error message when trying to open the encrypted file.

Configuring EFS for the Database of Your SAP System

Prerequisites

Obtaining the EFS certificate

Because EFS relies on a public key to encrypt files, you need a public-private key pair and a public key certificate for encryption. If you do not currently have a public key infrastructure (PKI), EFS will generate a self-signed certificate to be used the first time you attempt to encrypt a file or folder.

In a domain environment, it is recommended to deliver EFS certificates to users as part of your PKI. Using a certificate authority provides more flexibility for EFS certificate delivery and recovery management. For more information, see “How to Implement Data Recovery Strategies with PKI and How to Implement Key Recovery with PKI”.

Self-signed certificates are valid for 100 years. Certificates issued by certification authorities(CA) are typically valid for a few years. EFS automatically renews a certificate if possible. If the certificate cannot be renewed, it will request a new one from CA or generate a self-assigned certificate.

Planning your data recovery procedure

Encrypting a file bears the risk that the file might become unreadable. Therefore, it is crucial to consider data recovery implementation before encrypting the files.

(5)

On a stand-alone machine, no recovery policy is defined by default. Administrators on stand-alone machines have to create a recovery certificate and add it to the recovery policy. Otherwise, there is no way to recover the data if the private keys for authorized users are lost.

For more information about how to add recovery agents for your system, see “Recover files and folders”. Once the recovery agents are defined, it is important to back up the keys for recovery agents as well. To do

so, follow the instructions as described in How to back up the recovery agent Encrypting File System (EFS)

private key in Windows Server 2003.

For more information and best practices on data recovery and data protection, see Encrypting File System on Windows XP and Windows 2003.

Space requirements

(6)

Encrypting Data Files

You can encrypt files or folders by setting the encryption attribute in the object's Properties dialog box or by using the Ciper.exe command. To encrypt the database of an SAP system, you can choose to encrypt the entire database by encrypting the data path (folder), or perform encryption on a single data container file. Users with write permission on the files are able to perform encryption. Once the files are encrypted, only the encryptor or authorized users are able to access the encrypted files. On a Windows platform, DB2 processes are run by user db2<sid>. Therefore, you have to log in as user db2<sid> to encrypt the database files. Enabling Encryption for a Folder

If the encryption attribute is set for a folder, the folder itself is not encrypted. Any files created in or added to the folder are automatically encrypted. All the existing files under this folder can also be encrypted.

You can use this method to encrypt sapdata container folders for your existing database or prior to your SAP installation. In this way, the newly created database will automatically be encrypted during installation. Procedure:

1. Make sure all the DB2 processes are stopped. Encryption is not allowed when the file is being accessed.

2. Right-click the sapdata folder for your tablespace directory and choose Properties.

(7)

4. In the Advanced Attributes dialog box, select the Encrypt contents to secure data checkbox and choose the OK pushbutton.

Note: Although the NTFS file system supports both compression and encryption, it does not support both at the same time. This means that you can only select one or the other. A file or folder cannot be both encrypted and compressed at the same time.

5. In the sapdata Properties dialog box, choose the OK pushbutton again to confirm your selection. 6. Choose the Apply pushbutton in the sapdata Properties dialog box.

A new dialog box is displayed where you have to choose between the following options:

 Apply changes to this folder only.

This option does not have any effect on existing files. Files created in or copied to this folder later will be encrypted. The encryption attribute for subfolders will not be changed.

If you choose this option, the results are as follows:

File description Encryption status

Already stored in the folder and its subfolders. Unchanged

The files remain either encrypted or unencrypted.

Created in or copied to the folder by you later. Protected with your private key. Created in or copied to the folder by another user (if the

folder is shared) later. Protected with the other user's private key. Created in or copied to subfolders later. Unchanged

Moved to the folder or subfolders later. Unchanged

 Apply changes to this folder, subfolders and files.

(8)

The results are as follows:

File description Encryption status

Already in the folder and its subfolders. Protected with your private key if you have Write Attributes permission; otherwise, files remain unchanged.

Later created in or copied to the folder or

subfolders by you. Protected with your private key. Later created in or copied to the folder or

subfolders by another user. Protected with the other user's private key. Later moved to the folder or subfolders. Unchanged

7. Select Apply changes to this folder, subfolders and files and choose the OK pushbutton.

Note: If you choose this option, all the files in the current folder will be encrypted, including those that are owned by others if the folder is shared by multiple users. This will prevent others from accessing their own files.

Enabling Encryption for a Single File

If you want to encrypt a specific data file that contains sensitive data, perform the following steps:

1. Make sure all the DB2 processes are stopped. Encryption is not allowed if the file is being accessed. 2. Identify the tablespace container file that you want to encrypt. To obtain the container file name, you

can use the db2 list tablespace containers command. Example

C:\benchmark>db2 list tablespace containers for 30 Tablespace Containers for Tablespace 30 Container ID = 0

Name = E:\db2\ACO\sapdata1\DB2ACO\NODE0000\ACO\T0000030\C0000000.LRG Type = File

(9)

4. On the General tab page, choose the Advanced pushbutton.

5. In the Advanced Attributes dialog box, select the Encrypt contents to secure data checkbox and choose the OK pushbutton.

6. In the Properties dialog box, choose the Apply pushbutton.

(10)

When the files or folders are encrypted, the value E is displayed in the Attributes column in Windows Explorer.

(11)

Decrypting Files

For the decryption of files, the same procedure applies as for the encryption. This means that you open the Properties dialog box by right-clicking the relevant file and choose the Advanced pushbutton. In the

Advanced Attributes dialog box, you disable the encryption attribute by deselecting the Encrypt contents to secure data checkbox and apply the changes.

Only users who have write permission to the file and who have been granted access to the encrypted files are able to decrypt them.

Other Useful Operations

Exporting/Importing EFS certificate and keys

Once a file or folder is encrypted with EFS, it can only be decrypted with the private keys of the authorized user account. If the private key is damaged or missing, even the user that encrypted the file cannot decrypt it.

To ensure continued access to your own encrypted files, you can perform the following steps to export the EFS certificate and private key and store it in a safe place:

1. Open Certificate Manager by clicking Start -> Run, and execute certmgr.msc. 2. Expand the Personal folder and choose the Certificates folder.

3. Choose the certificate that lists Encrypting File System under Intended Purposes. (You might need to scroll to the right to see this.)

4. In the main menu bar, choose Action  All Tasks  Export.

(12)

6. Accept the preselected default settings by choosing Next.

7. Enter the password you want to use and confirm it.

8. Provide the location and the name of the file you want to save and choose the Next pushbutton to finish the process.

Note: Make sure the exported file is copied to a floppy disk, CD, or other removable media and stored in a safe place as anyone who has access to the file can import the key to his profile and will be able to decrypt the files.

To import a certificate, perform the following steps:

1. Open the Certificates snap-in and expand the Personal folder.

(13)

Authorizing Additional Users for Access to Encrypted Files (Optional)

Users can share encrypted files with other local or domain users. Before a user is granted access to the encrypted file, EFS first validates if the user’s certificate can be trusted. EFS then decrypts the FEK with the grantor’s private key and uses the new user’s public key to encrypt it. The encrypted FEK is then stored in a data decryption field (DDF) with the file.

To share encrypted files with other users, perform the following steps: 1. Right click the file and open the Properties dialog box. 2. On the General tab page, choose the Advanced pushbutton.

3. In the Advanced Attributes dialog box, choose the Details pushbutton.

4. In the Encryption Details dialog box, choose the Add pushbutton.

(14)

Note: Use caution when sharing encrypted files. Any user who has been granted access to the files can authorize others to access them, too.

As the DB2 service is started with user db2<sid>, the tablespace container files need to be accessible to user db2<sid>. You do not need to share the files with other users.

Test Results

In this section, we will compare the test results of an encrypted database and a non-encrypted database to identify the performance impact on the SAP system using windows EFS.

Test overview

Hardware:

Operating System: Windows 2003 server SAP release: SAP ECC 6.0 SR3

DB2 release: 9.7 FP2SAP

Database Layout: database size is 80GB with 35 regular tablespaces. All tablespace containers are located in one subdirectory (sapdata1) on ESS storage, and they are all encrypted when the encryption test is conducted.

We will concentrate on evaluating the encryption impact on the runtime and CPU usage. Windows performance monitor is used to collect performance data for analyzing.

(15)

The physical CPU usage over runtime is displayed in the following figure: Benchmark Physical CPU usage

0 10 20 30 40 50 60 0s 190s 380s 570s 760s 950s _1140s _1330s _1520s _1710s _1900s _2090s _2280s _2470s _2660s _2850s _3040s _3230s _3420s _3610s _3800s _3990s _4180s _4370s _4560s % C P U Non-encrypted Encrypted

At high load intervals, the average number of physical CPU used is 44.3% for the non-encrypted database and 45.0% for the encrypted database.

The following figure provides a system overview for CPU and disk throughput on the non-encrypted database: Benchmark Non-encrypted 0 10000 20000 30000 40000 50000 60000 70000 80000 0s 195s 390s 585s 780s 975s _1170s _1365s _1560s _1755s _1950s _2145s _2340s _2535s _2730s _2925s _3120s _3315s _3510s _3705s _3900s _4095s _4290s _4485s K b /s e c 0 5 10 15 20 25 30 35 40 45

(16)

System overview for CPU and disk throughput on the encrypted database: Benchmark Encrypted 0 10000 20000 30000 40000 50000 60000 70000 80000 0s 255s 510s 765s _1020s _1275s _1530s _1785s _2040s _2295s _2550s _2805s _3060s _3315s _3570s _3825s _4080s _4335s _4590s _4845s KB /s ec 0 5 10 15 20 25 30 35 40 45 Read+Write KB/sec System_Time% User_Time%

Comparing the total read&write rate vs. runtime:

Total Disk Read&Write KB/s

(17)

Comparing system CPU usage: System_Time% 0 2 4 6 8 10 12 0s 180s 360s 540s 720s 900s _1080s _1260s _1440s _1620s _1800s _1980s _2160s _2340s _2520s _2700s _2880s _3060s _3240s _3420s _3600s _3780s _3960s _4140s _4320s _4500s Non-encrypted Encrypted

The figure above shows that the system CPU usage is a little bit higher for the encrypted database. Comparing user CPU usage:

User_Time% 0 5 10 15 20 25 30 35 40 45 0s 180s 360s 540s 720s 900s _1080s _1260s _1440s _1620s _1800s _1980s _2160s _2340s _2520s _2700s _2880s _3060s _3240s _3420s _3600s _3780s _3960s _4140s _4320s _4500s Non-encrypted Encrypted

All in all, the performance impact of encryption is very small in the benchmark test:

 The runtime degradation is close to zero.

 CPU usage under high workload is increased from 44.3% to 45.0%. The increase comes from the

(18)

Test 2: Zmerge

Zmerge test is an SAP internal test program consisting of several implementations of the BW compression (condense) process:

 Insert/Update

 Update via Temp Table

 Merge statement

 Insert/Merge combination

Runtime: Runtime difference is 16.3%

Non-encrypted Encrypted Difference

Runtime(sec) 9752 11308 15.96%

The increase in CPU and runtime is shown in the following figure:

Physical CPU usage vs. runtime

0

10

20

30

40

50

60 0s

480s

960s

_1440s

_1920s

_2400s

_2880s

_3360s

_3840s

_4320s

_4800s

_5280s

_5760s

_6240s

_6720s

_7200s

_7680s

_8160s

_8640s

_9120s

_9600s

₁₀₀₈₀

₁₀₅₆₀

₁₁₀₄₀

₁₁₅₂₀

%

C

P

U

Non-encrypted

Encrypted

(19)

Overview of CPU and disk throughput on the non-encrypted database:

Zmerge-Non-encrypted

0 10000

20000

30000

40000

50000

60000

70000

80000

0s

480s

960s

_1440s

_1920s

_2400s

_2880s

_3360s

_3840s

_4320s

_4800s

_5280s

_5760s

_6240s

_6720s

_7200s

_7680s

_8160s

_8640s

_9120s

_9600s

K

B/

se

c

0

5

10

15

20

25

30

35 Read+Write KB/sec

System_Time%

User_Time%

Overview of CPU and disk throughput on the encrypted database:

Zmerge-Encrypted

0 10000

20000

30000

40000

50000

60000

70000

80000

90000

0s

480s

960s

_1440s

_1920s

_2400s

_2880s

_3360s

_3840s

_4320s

_4800s

_5280s

_5760s

_6240s

_6720s

_7200s

_7680s

_8160s

_8640s

_9120s

_9600s

10080s

10560s

11040s

11520s

K

B/

se

c

0

5

10

15

20

25

30

35

(20)

Comparing system CPU usage:

System_Time%

0

5

10

15

20

25

30

35 0s

480s

960s

_1440s

_1920s

_2400s

_2880s

_3360s

_3840s

_4320s

_4800s

_5280s

_5760s

_6240s

_6720s

_7200s

_7680s

_8160s

_8640s

_9120s

_9600s

10080s

10560s

11040s

11520s

System_Time%-Non-encrypted

System_Time%-Encrypted

System CPU usage is increased. The system CPU usage increased by 20% at the spikes. On average, System_Time% is 2.23% for the non-encrypted database and 8.01% for the encrypted database. Comparing the disk throughput rate:

Total Disk Read&Write KB/sec

(21)

All in all, there is a slight impact on runtime and CPU usage for the zmerge test.

 There is a 15.96% degradation in runtime.

 The average physical CPU usage is increased from 22.3% to 25.3%. System CPU usage is

increased from 2.23% to 8.01%.

Test 3: Client Copy/Client Delete/SGEN:

Client Copy and Client Delete are standard SAP transactions to copy or delete client-specific data to or from SAP tables. SGEN is used for SAP code generation.

Runtime difference between encrypted and non-encrypted database:

Non-encrypted Encrypted Difference

SGEN 7139 7261 1.71%

Client Copy 5144 5201 1.11%

Client Delete 3677 3925 6.74%

Test Summary:

There is a slight performance impact when using EFS for the database of your SAP system.

The runtime difference depends on the amount of disk activities and the number of SAP processes running the job. Zmerge test uses only one SAP process, and the job involves heavy disk read and write activities. In this case, the runtime degradation could be as high as 16%. On the other hand, the runtime difference is negligible for benchmark, sgen, and client copy/client delete tests.

The total CPU usage increased from 1% to 3% which is mainly caused by system CPU usage. At certain points, there was an increase of 20% in CPU usage for a short period of time.

(22)

Miscellaneous

New functions with Windows 2008

With the release of Windows 2008, EFS has been enhanced with the following new functions:

 Smart card key storage

EFS encryption keys and certificates can be stored on smart cards. This increases the security of those keys because they cannot be attacked by another user or by someone who steals the computer.

 Per-user encryption of offline files

If this option is enabled, each file in the offline cache is encrypted with a public key from the user who cached the file. In previous Windows releases, the encryption of cached offline files is done with system keys.

 Increased configurability of EFS through Group Policy

A number of new Group Policy options have been added to help administrators define and implement organizational policies for EFS. These include the ability to require smart cards for EFS, enforce page file encryption, stipulate minimum key lengths for EFS, enforce encryption of the user’s documents folder, and prohibit self-signed certificates.

 Encrypting File System Rekeying Wizard

The Encrypting File System Rekeying Wizard allows you to choose a certificate for EFS and select and migrate existing files using the newly chosen certificate. This is more efficient than decrypting and re-encrypting files.

File operations on encrypted files across the network

With EFS file sharing, you can access an encrypted file and perform other file operations across the network. Keep in mind that EFS is not designed to protect data while it is transferred from one system to another. All EFS operations occur on the computer on which the files are stored. If you open an encrypted file on the network drive, the file is decrypted on the computer on which the file is physically stored and then transmitted in plaintext over the network to your computer. Similarly, if the encrypted files are copied or moved to or from a network file share on a remote computer, the files are decrypted locally and re-encrypted on the target volume with a new FEK.

You can reduce or eliminate the risk over network by using IP security with ESP and encryption to secure data as it is transmitted, or by storing encrypted files on Web folders. For more information, see Using

Encrypting File System, section Remote EFS Operations on File Shares and Web Folders.

Changing user passwords

The private key that is used to decrypt the file encryption key is located in the user profile under

RootDirectory\Documents and Settings\username\Application Data\Microsoft \Crypto\RSA. All files in the RSA folder are automatically encrypted using a random symmetric key called user’s master key. The user’s master key is 64 bytes in length and is generated by a strong random number generator. Before master keys are stored, they are 3DES-encrypted using a key derived from the user’s password.

(23)

To avoid unexpected access errors caused by a password change, use ALT+Ctrl+Del to change the password properly.

Backing up an encrypted file or folder

Backing up encrypted files is as easy as backing up any other regular file. Since the backup does not require the user’s private key, the file remains encrypted, regardless of its destination.

Keep in mind that you can only decrypt a file if the backup is restored to a domain or local computer where authorized user accounts or data recovery agents exist and the private keys for the users are available.

Performing data recovery

You can log in with the data recovery agent account to recover an EFS-encrypted file for a user whose private keys are lost. The recovery agent can open the files normally and save it in non-encrypted format or deselect the encryption attribute in the file’s Properties dialog box.

The most secure mechanism for data recovery is to use a central recovery workstation. You can do so by using a backup utility to perform a raw backup of the encrypted files and then restore those files on a central recovery machine where the private keys for DRAs are stored. You can add extra protection for your recovery procedures by restricting the recovery agent to log on only at this workstation and placing the workstation in a physically secured area.

Expired certificates (private keys)

 EFS user certificates

Self-assigned certificates are valid for 100 years. If the certificates are issued by certification authorities, they are typically valid for only a few years. If an EFS certificate expires, users can still access the encrypted files as the private keys are stored with the user profile. However, they cannot encrypt more files with the existing certificate. When the certificate is expired, EFS automatically renews the certificate if possible. If EFS cannot renew the certificate, it requests a new certificate from a trusted enterprise CA if one is known and available. Otherwise, EFS creates a new self-signed certificate the next time the user encrypts a file.

As the renewal process is fully automatic, you may not realize that different certificates are used for your encrypted files. It is important to back up every certificate (and the keys) to ensure the files can be recovered in the future.

 Data recovery agent certificate

When the administrator logs on to the domain control for the first time, a default domain recovery agent certificate is created, and the private key is stored in the domain administrator’s profile. The default domain recovery agent certificate is valid for three years only and cannot be renewed. When the DRA’s certificate is expired, the DRA can still be used to decrypt existing files as the private keys are still valid. However, you receive the error message “Recovery policy configured for this system contains invalid recover certificate” when trying to encrypt new files using an expired recovery certificate.

(24)

Summary

Encrypting your sensitive data with EFS adds another layer of security. It is especially useful on portable computers or on computers that are shared by several users. If files are encrypted, the data is protected even if an attacker has full access to the computer’s data storage.

(25)

Disclaimer and Liability Notice

This document may discuss sample coding or other information that does not include SAP official interfaces and therefore is not supported by SAP. Changes made based on this information are not supported and can be overwritten during an upgrade.

SAP will not be held liable for any damages caused by using or misusing the information, code or methods suggested in this document, and anyone using these methods does so at his/her own risk.

Securing the DB2 Database of your SAP System with Windows Encrypting File System