AWS Database Blog

Configuring SSL encryption on Oracle and PostgreSQL endpoints in AWS DMS

AWS Database Migration Service (DMS) helps you migrate databases to AWS securely. It supports homogeneous and heterogeneous migrations between different database platforms, such as Oracle to Amazon Aurora. AWS DMS supports continuous data replication while maintaining high availability and has been widely adopted for database migrations because it is easy to configure. For more information, see What Is AWS Database Migration Service?

This post demonstrates how to encrypt source (Oracle) and target (Aurora) endpoints to use SSL, and implement data in transit encryption. To simulate the customer environments, the post covers steps to configure SSL on Oracle on an Amazon EC2 instance, Oracle on Amazon Relational Database Service (RDS) as the source database, and Amazon Aurora with PostgreSQL as the target.

Solution overview

The solution contains the following steps:

  1. Configure SSL on the source Oracle instance. This includes the following steps:
    a. Configure SSL on the Oracle RDS instance.
    b. Configure SSL on the Oracle EC2 instance.
  2. Configure the source Oracle database endpoint with SSL on the AWS DMS console.
  3. Configure the target PostgreSQL Aurora database endpoint with SSL in AWS DMS.
  4. Configure data replication.
  5. Validate the data.

This post uses an Oracle database running on an EC2 instance and an Oracle RDS instance. Additionally, the target PostgreSQL instance is already created and has schemas and its objects’ structure created. You can use AWS SCT to convert your existing source database schema from one database engine to another. For more information, see What Is the AWS Schema Conversion Tool?

Other useful documentation pages are Creating an Amazon Aurora DB Cluster and Security in AWS Database Migration Service.

Configuring SSL on the Amazon RDS for Oracle instance

Amazon RDS for Oracle supports SSL. SSL configuration is done using an option group, which you can share with multiple databases. You can configure an Amazon RDS for Oracle instance to use the SSL option and create a client wallet that contains its signing certificate. Local clients use the wallet to connect to the TCPS listener. The wallet is also uploaded to AWS DMS to allow it to connect to the instance.

If you are using a source Oracle instance running on premises or on Amazon EC2, skip this step.

Creating an option group to support SSL

To create an option group to support SSL, complete the following steps:

  1. On the Amazon RDS console, under Option groups, choose Create group.
  2. For Name, enter the name of your option group.
    For this post, we enter OracleRDSSSLOptionGrp.
  3. For Description, enter a description of your group.
  4. For Engine, choose oracle-ee.
  5. For Major engine version, choose 1.
  6. Choose Create.
    The following screenshot demonstrates steps 1–6.
  7. On the Option groups page, choose the newly created option group (OracleRDSSSLOptionGrp).
  8. Choose Add option as shown in the following screenshot preview.
  9. Under Option details, for Option, choose SSL.
  10. For Port, choose 2484.
  11. For Security Groups, choose default.
  12. Leave Option settings at default.
    Option settings displays the current SSL parameter value.
  13. Choose Add option.
    The following screenshot demonstrates steps 9–13.

Modifying the Oracle RDS instance to use the SSL option group

To modify the Oracle RDS instance, complete the following steps:

  1. On the Amazon RDS console, choose Database.
  2. Choose your desired database.
  3. Choose Modify.
  4. Under Database options, for Option group, choose your new option group.
    The following screenshot demonstrates steps 1–4.
  5. Scroll down to the bottom of the page and Choose Continue as shown in the screenshot preview below.

To review the changes, complete the following steps:

  1. For Scheduling of modifications, select Apply immediately.
  2. Choose Modify DB Instance as shown in the screenshot below.

Creating a wallet

You can create a wallet that contains the Certificate Authority (CA) certificate used to sign RDS SSL certificates by using an Oracle client on any Linux machine. You upload this wallet to AWS DMS. Complete the following steps:

  1. Set the ORACLE_HOME system variables and create the wallet folder by entering the following code: 
    export ORACLE_HOME=/home/user/app/user/product/12.1.0/dbhome_1
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib            
mkdir $ORACLE_HOME/ssl_wallet
  2. Download rds-ca-2019-root.pem RDS CA certificate.
  3. To create the Oracle wallet, enter the following commands: 
    orapki wallet create -wallet $ORACLE_HOME/ssl_wallet -auto_login_only
orapki wallet add -wallet $ORACLE_HOME/ssl_wallet -trusted_cert –cert  $ORACLE_HOME/ssl_wallet/ca-cert.pem -auto_login_only

Configuring SSL on the Oracle EC2 instance

Configure the self-signed certificate for the Oracle EC2 instance and create a client wallet that contains its signing certificate. Local clients use the wallet to connect to the TCPS listener, and the wallet is uploaded to AWS DMS to allow it to connect to the instance.

If you are using an Amazon RDS for Oracle instance, skip this step.

Creating a wallet using a self-signed certificate for Oracle SSL on an EC2 Oracle instance

To create this wallet, complete the following steps:

  1. Log in to your Linux or UNIX machine as an Oracle user with your environment set for the correct database and ORACLE_HOME.
  2. Create a directory and root key for the self-signed certificate with the following code: 
    mkdir -p /u01/app/oracle/self_signed_cert
cd /u01/app/oracle/self_signed_cert
openssl genrsa -out self-rootCA.key 2048
openssl req -x509 -new -nodes -key self-rootCA.key -sha256 -days 3650 -out self-rootCA.pem

Input parameters
Country Name (2 letter code) [XX]:US
State or Province Name (full name) []:Virginia
Locality Name (eg, city) [Default City]:Reston
Organization Name (eg, company) [Default Company Ltd]:AmazonWebService
Organizational Unit Name (eg, section) []:ProServe
Common Name (eg, your name or your server's hostname) []:aws
Email Address []:<Leave Blank and press enter>
  3. Create an Oracle wallet directory and wallet with the following code: 
    mkdir -p /u01/app/oracle/wallet
orapki wallet create -wallet "/u01/app/oracle/wallet" -pwd oracle123 -auto_login_local
  4. Add the root certificate in the wallet with the following code: 
    orapki wallet add -wallet "/u01/app/oracle/wallet" -pwd oracle123 -trusted_cert -cert /u01/app/oracle/self_signed_cert/self-rootCA.pem
  5. Verify that the certificate is added successfully. See the following code: 
    orapki wallet display -wallet /u01/app/oracle/wallet -pwd oracle123

Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
Trusted Certificates:
Subject:        CN=aws,OU=ProServe,O=AmazonWebService,L=Reston,ST=Virginia,C=US
  6. Generate the Certificate Signing Request (CSR) with the following code: 
    orapki wallet add -wallet "/u01/app/oracle/wallet" -pwd oracle123 -dn "CN=aws" -keysize 2048 -sign_alg sha256

openssl pkcs12 -in /u01/app/oracle/wallet/ewallet.p12 -nodes -out /u01/app/oracle/wallet/nonoracle_wallet.pem
  7. Put “dms” as the common name with the following code: 
    openssl req -new -key /u01/app/oracle/wallet/nonoracle_wallet.pem -out certdms.csr
  8. Get the encryption signature with the following code: 
    openssl req -noout -text -in certdms.csr | grep -i signature

    The signature key for this post is sha256WithRSAEncryption.

  1. Run the following command to generate the certificate (.crt) file: 
    openssl x509 -req -in certdms.csr -CA self-rootCA.pem -CAkey self-rootCA.key -CAcreateserial -out certdms.crt -days 365 -sha256
  1. Add the certificate to the Oracle wallet with the following code: 
    orapki wallet add -wallet /u01/app/oracle/wallet -pwd oracle123 -user_cert -cert certdms.crt
  1. View the wallet; it should have two entries. See the following code: 
    orapki wallet display -wallet /u01/app/oracle/wallet -pwd oracle123
  1. Modify the sqlnet.ora file. See the following code: 
    cd $ORACLE_HOME/network/admin
mv sqlnet.ora sqlnet.ora.nonssl

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /u01/app/oracle/wallet)
     )
   )

SQLNET.AUTHENTICATION_SERVICES = (NONE)
SSL_VERSION = 1.0
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA)
SQLNET.INBOUND_CONNECT_TIMEOUT=0
  1. Modify the listener.ora file (add wallet location and SSL port with TCPS as protocol). See the following code: 
    lsnrctl stop LISTENER
cp listener.ora listener.ora.nonssl

# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SSL_CLIENT_AUTHENTICATION = FALSE
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/wallet)
    )
  )
SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
   (GLOBAL_DBNAME = ORCL)
   (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
   (SID_NAME = orcl)
  )
 )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.0.126)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = 10.1.0.126)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
  1. Modify the tnsnames.ora file to test the local connection using SSL. See the following code: 
    ORCLSSL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = 10.1.0.126)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.ec2.internal)
    )
  )

lsnrctl start LISTENER
  1. Test local connectivity using SQLPLUS and confirm that it is using SSL connections sqlplus system@orclssl. See the following code: 
    SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 20 20:31:51 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Wed Feb 20 2019 17:19:03 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> SELECT SYS_CONTEXT('USERENV', 'network_protocol') FROM DUAL;

SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
--------------------------------------------------------------------------------
tcps
  1. Create the client wallet for AWS DMS with the following code: 
    cd /u01/app/oracle/self_signed_cert
orapki wallet create -wallet ./ -auto_login_only
orapki wallet add -wallet ./ -trusted_cert -cert self-rootCA.pem -auto_login_only
orapki wallet display -wallet ./
  1. Download the cwallet.sso certificate to your local desktop to import it into the AWS DMS tool.

Configuring the source Oracle database endpoint using the AWS DMS console

In the first steps, you generated a certificate for either for Amazon RDS for Oracle or Oracle running on an EC2 instance. This post uses the Amazon RDS as the source for the Oracle SSL certificate.

To configure the database endpoint, first create a replication instance via the AWS DMS console.

Importing the certificate

To import the certificate, complete the following steps:

  1. On the AWS DMS console, choose Certificates.
  2. Choose Import Certificate as shown in the screenshot below.
  3. For Certificate Identifier, enter RDSSSLCERT.
  4. For Import file, choose Browse.
  5. Locate cwallet.sso.
  6. Choose Add new CA certificate as shown below.
    When the certificate is imported successfully, you see the certificate listed with the ARN. See the following screenshot.

Configuring the source endpoint

To configure the source endpoint with SSL mode enabled as verify-ca, complete the following steps:

  1. On the AWS DMS console, choose Endpoints.
  2. Choose Create endpoint as shown below.
  3. For Endpoint type, choose Source.
  4. Select the Select RDS DB Instance check box.
  5. For RDS Instance, choose the orcl – oracle-ee.
  6. For Endpoint Identifier, enter a name.
     This post enters the name orcl-source.
  7. For Source engine, choose oracle.
  8. For Server name, enter the Oracle RDS instance endpoint.
  9. For Port, enter the SSL port 2484.
  10. For SSL mode, choose verify-ca.
  11. For CA certificate, choose the imported certificate RDSSSLCERT.
  12. For User name, enter the Oracle RDS user.
  13. For Password, enter the user password.
  14. Choose Run test.
    The following screenshot demonstrates steps 3–14.
  15. After test run is successful, choose Create endpoint.

Confirm that the AWS DMS connections are using SSL

To confirm that the AWS DMS connections are using SSL, complete the following steps:

  1. On the Amazon RDS console, choose Database.
  2. Choose your database identifier.
  3. Under Configuration, under CloudWatch Logs, choose Listener, as shown in the screenshot below.
  4. Verify the Oracle listener.log to confirm that connections coming from AWS DMS is using TCPS protocol, which indicates that established connection is on SSL.
    If you used an SSL certificate of Oracle on EC2 instance as the source, verify the Oracle server listener.log to confirm that the connection coming from AWS DMS is TCPS. The following screenshot shows the listener.log output.

Configuring the target PostgreSQL Aurora database endpoint

To configure the target endpoint, complete the following steps:

  1. On the AWS DMS console, choose Endpoints.
  2. Choose Create endpoint as shown in the screenshot below.
  3. For Endpoint type, select Target.
  4. Select the Select RDS DB Instance check box.
  5. For RDS Instance, choose the Aurora PostgreSQL instance identifier auroradb – aurora-postgresql.
  6. For Endpoint identifier, enter auroradb-target.
  7. For Target engine, choose aurora-PostgreSQL.
  8. For Server name, enter the Aurora PostgreSQL endpoint.
  9. For Port, enter the SSL port 5432.
  10. For SSL mode, choose require.
  11. For User name, enter the Aurora PostgreSQL user.
  12. For Password, enter the user password.
    The following screenshot demonstrates steps 3–12.
  13. Choose Create endpoint.
    On the Endpoints page, choose the target endpoint.
  14. Choose Test connection as shown below.
  15. Choose Run test as shown below.

Configuring data replication

To configure data replication, complete the following steps:

  1. Log in to the source Oracle database and gather the table row count before enabling replication. See the following command: 
    [[select count(*) from repltest;]

    The following screenshot provides a preview of the output.

  2. Log in to the target Aurora PostgreSQL database and ensure that the table is empty. See the following command: 
    [[select count(*) from replssltest.repltest;]]

    The following screenshot provides a preview of the output.

  3. On the AWS DMS console, choose Database migration tasks.
  4. Choose Create task.
  5. Under Modify Task, for Task name, enter repltest.
  6. Keep all other values at their default.
    The following screenshot demonstrates steps 5 and 6.
    Your next step is to create the selection rules and transformation rules.
  7. In the JSON editor, enter the following example code (replace REPLSSLTEST with your schema name): 
    {
  "rules": [
    {
      "rule-type": "transformation",
      "rule-id": "1",
      "rule-name": "1",
      "rule-target": "column",
      "object-locator": {
        "schema-name": "REPLSSLTEST",
        "table-name": "%",
        "column-name": "%"
      },
      "rule-action": "convert-lowercase",
      "value": null,
      "old-value": null
    },
    {
      "rule-type": "transformation",
      "rule-id": "2",
      "rule-name": "2",
      "rule-target": "table",
      "object-locator": {
        "schema-name": "REPLSSLTEST",
        "table-name": "%"
      },
      "rule-action": "convert-lowercase",
      "value": null,
      "old-value": null
    },
    {
      "rule-type": "transformation",
      "rule-id": "3",
      "rule-name": "3",
      "rule-target": "schema",
      "object-locator": {
        "schema-name": "REPLSSLTEST",
        "table-name": "%"
      },
      "rule-action": "convert-lowercase",
      "value": null,
      "old-value": null
    },
    {
      "rule-type": "selection",
      "rule-id": "4",
      "rule-name": "4",
      "object-locator": {
        "schema-name": "REPLSSLTEST",
        "table-name": "%"
      },
      "rule-action": "include",
      "filters": []
    }
  ]
}
  8. Choose Save. The following screenshot demonstrates steps 7 and 8.
  9. Under Table mappings, choose the Guided
  10. Review the information and choose Create task as shown in the screenshot below.
  11. Ensure that the task has started and is running successfully.
    The following screenshots show the repltest task status changing from Starting to Running.

Checking the source and target database connections

Check on the source and target database to ensure that the connections are using SSL. Complete the following steps:

  1. Use SQL*PLUS or the SQL developer tool to connect to the Amazon RDS for Oracle source database, and enter the following SQL query: 
    select a.username,a.program,a.logon_time,a.machine,a.status,a.osuser 
from v$session a,v$session_connect_info b 
where a.sid=b.sid and a.serial#=b.serial#
and b.NETWORK_SERVICE_BANNER is null;

    The query should list all the connections coming from the AWS DMS replication instance. The following screenshot shows the output.

  2. Use the psql tool to connect to the Aurora target instance, and enter the following SQL query: 
    select * from pg_stat_ssl;

    The query should list all the connections coming from DMS replication instance. The following screenshot shows the output.

  3. On the Amazon RDS console, choose Database.
  4. Choose your database identifier.
  5. Under Configuration, under CloudWatch Logs, choose
    The listener.log lists the TCPS protocol connections. See the following screenshot.

Validating the data

To validate that the data is replicated from source to target, complete the following steps:

  1. On the AWS DMS console, choose Database migration tasks.
  2. Review the AWS DMS task status and ensure that it is completed successfully.
    The following screenshot shows the task status.
  3. Log in to the target Aurora PostgreSQL database.
  4. Confirm that all rows were loaded into the target database.
    The following screenshot shows the output.

Conclusion

This post showed how to configure SSL encryption for AWS DMS. Configuring SSL encryption secures data in transit when your database’s data is transferred from on premises to Amazon RDS. It also ensures better compliance and alignment with your organization’s security policies and guidelines.

Try this approach in your environment, and please feel free to reach out with questions or thoughts in the comments.

 

About the Author

 

Bhavesh Rathod is an Oracle Database Cloud Architect with the Professional Services team at Amazon Web Services. He works as database migration specialist to help Amazon customers to move their on-premises database environment to AWS cloud database solutions.

 

 

 

Jeevith Anumalla is an Oracle Database Cloud Architect with the Professional Services team at Amazon Web Services. He works as database migration specialist to help internal and external Amazon customers to move their on-premises database environment to AWS data stores.