AWS Database Blog

Set up SSL/TLS client connections to Amazon RDS for SQL Server and Amazon RDS for Oracle

It is important to safeguard the integrity and confidentiality of communication between clients and databases. The deployment of SSL (Secure Sockets Layer) for this purpose, however, can sometimes face resistance, either due to actual complexities or perceived challenges associated with the specific tools used by individuals or organizations.

In this post, we address these concerns by demonstrating how to configure SSL/TLS (Transport Layer Security) connections to Amazon Relational Database Service (Amazon RDS) instances running RDS for SQL Server or RDS for Oracle from widely used client applications. To replicate real-world customer environments, we guide you through the steps to enable SSL/TLS using common database administration tools such as:

Overview of SSL

SSL is a standard security technology for establishing an encrypted link between a server and a client. SSL is now replaced by TLS, which fixes existing SSL vulnerabilities and performs the authentication efficiently. For more information, refer to What’s the Difference Between SSL and TLS?.

SSL/TLS certificates are digital documents that are signed by a trusted Certificate Authority (CA), which allows systems to verify the identity and subsequently establish an encrypted network connection to another system using the SSL/TLS protocol. A secure database connection, also known as encryption in transit, uses the SSL/TLS protocol, and the SSL/TLS certificate must be loaded in the database. Because the database server tends to be provisioned in a private network without exposure to the internet, sometimes a self-signed certificate is used. Self-signed certificates provide encryption, but don’t provide identity verification from a trusted CA.

A certificate chain is an ordered list of certificates containing an SSL/TLS certificate and CA certificates, which enables the receiver to verify that the sender and all CAs are trustworthy. The chain begins with the SSL/TLS certificate, and each certificate in the chain is signed by the entity identified by the next certificate in the chain. The intermediate certificate is the signer/issuer of the SSL/TLS certificate, and the root CA certificate is the signer/issuer of the intermediate certificate. The following diagram illustrates a certificate chain.

Solution overview

For all Amazon RDS database engines, an SSL certificate is created when a DB instance is provisioned. It includes the DB instance endpoint as the Common Name (CN) for the SSL certificate to guard against spoofing attacks. In order to configure SSL:

  • For Amazon RDS for SQL Server, use the rds.force_ssl parameter in the parameter group
  • For Amazon RDS for Oracle, in the options group, use the parameters SQLNET.CIPHER_SUITE, FIPS.SSLFIPS_140, and SQLNET.SSL_VERSION, and disable access on port 1521 in the instance’s security group configuration.

Each DB engine has its own process for implementing SSL/TLS. To learn how to implement SSL/TLS for your DB instance, refer to Using SSL/TLS to encrypt a connection to a DB instance and open the link corresponding to your database engine.

In order for any client to connect to a database using SSL, the client and server must use the same root certificate. The client only needs the root certificate; the server contains the SSL/TLS certificate, intermediate certificate, and root certificate. The client stores the root certificate in a certificate store, which can be managed by the OS (Windows certificate store) or the client tool. The root certificates from a common trusted CA like Verisign, Digicert, or GoDaddy, which are usually included by Windows and client tools. To download the Amazon RDS root certificate, refer to Certificate bundles for all AWS Regions.

Prerequisites

Configure an SSL/TLS connection to Amazon RDS for SQL Server

This section demonstrates how to configure an SSL connection to Amazon RDS for SQL Server using SSMS and DBeaver. SSMS uses a Windows certificate store. Complete the following steps to import your certificate into the Windows certificate store:

  1. In SSMS, open certmgr.msc.
  2. Expand Certificates and open the context menu (right-click) for Trusted Root Certification Authorities.
  3. Choose All Tasks, then choose Import.
  4. On the first page of the Certificate Import Wizard, choose Next.
  5. Choose Browse.
  6. In the browse window, change the file type to All files (*.*) because .pem is not a standard certificate extension.
  7. Locate the .pem file that you downloaded previously.
  8. Choose Open to select the certificate file, then choose Next.
  9. Choose Finish. A dialog box appears indicating that the import was successful.
  10. In the certificate management console, expand Certificates, expand Trusted Root Certification Authorities, and choose Certificates.
  11. Locate the certificate to confirm it exists.
  12. On the SSMS Connection Properties tab, select Encrypt connection and Trust server certificate.

To connect to RDS for SQL Server using DBeaver.

  1. In DBeaver, on the Main tab for Connection settings, select Trust Server Certificate.
  2. On the SSL tab, select Use SSL.
  3. For Keystore, enter your keystore path.

Configure an SSL/TLS connection to Amazon RDS for Oracle

This section demonstrates how to configure an SSL connection to Amazon RDS for Oracle using Oracle SQL Developer and the SQL*PLUS command line.

To connect to Oracle securely via Oracle SQL Developer, we first perform the actions needed to connect via the command line. The prerequisites are as follows:

  • A new option group for the RDS database that will be used to configure parameters for the RDS instance.
  • Oracle client software that will be used to connect to the RDS instance.
  • OpenSSL software used to create the SSL connection to the RDS instance.
  • Java cryptography extension (JCE) software. JCE provides a framework and implementation for encryption, key generation and key agreement, and Message Authentication Code (MAC) algorithms.
  • Oracle SQL Developer for a graphical user interface (GUI) to the RDS instance.
  • AWS Certificate bundles to use when connecting to the database.

Complete the following steps:

  1. Navigate to the RDS Console
  2. Under options groups, select the option to create a new option group with the SSL option as shown in the following screenshot.

  3. Download the Oracle client .zip files (download the file named WINDOWS.X64_193000_client.zip) and extract the client software into any directory.
  4. Run the setup.exe file from the extracted file.
  5. Download openssl-1.0.2j-fips-x86_64/openssl-1.0.2j-fips-x86_64.zip and extract the OpenSSL software into C:\.
    The software will be extracted into C:\openssl.
  6. Download the JCE files and extract the files into any directory. We use two files, local_policy.jar and US_export_policy.jar
  7. Download Oracle SQL Developer and extract the software into C:\. The software will be extracted into C:\sqldeveloper.
  8. Download certificates from AWS. Pick the certificate for your respective Region. In this example, we use an AWS GovCloud (US) Region. If you are using a non-GovCloud Region, you can download Region-specific certificates.
  9. Locate the following files that are needed for the setup. The locations used in this post are provided in parenthesis. Your location may vary depending on the installation location on your server.
    • orapki.bat (C:\oracle\bin)
    • openssl.exe (C:\OpenSSL\bin)
    • keytool.exe (C:\oracle\jdk\bin)
    • sqlnet.ora (C:\oracle\network\admin)
    • JCE files (C:\sqldeveloper-ssl)
    • sqldeveloper.conf (C:\sqldeveloper\sqldeveloper\bin)
  10. Create the wallet with the following command (in the Windows command window): 
    C:\oracle\bin\orapki wallet create -wallet 
C:\oracle\ssl_wallet -auto_login_only
  11. Add the Amazon RDS certificate to the wallet with the following command: 
    C:\oracle\bin\orapki wallet add -wallet 
C:\oracle\ssl_wallet -trusted_cert -cert C:\sqldeveloper-ssl\us-gov-west-1-bundle.pem -auto_login_only
  12. Add the database to the tnsnames.ora file. Note that on a fresh installation of the client, the tnsnames.ora file may not exist. Create a plain text file named tnsnames.ora in the same location as the sqlnet.ora file and add the following entries: 
    ORCLSSL =(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = <orclssl.xxxxx.us-gov-west-1.rds.amazonaws.com>)(PORT = 2484))
(CONNECT_DATA=
(SERVER = DEDICATED)
(SID = <orclssl>))
)
)
  13. Add the wallet location to the sqlnet.ora file with the following entries: 
    WALLET_LOCATION=
(SOURCE=
(METHOD=file)
(METHOD_DATA=(DIRECTORY= C:\oracle\ssl_wallet)))
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 1.2
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA)
SSL_SERVER_DN_MATCH = NO
SQLNET.AUTHENTICATION_SERVICES = (TCPS,TNS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
  14. Open a Windows command window and set an environment variable with the location of the tnsnames.ora file via the following commands: 
    set TNS_ADMIN= C:\oracle\network\admin
  15. Confirm with the following code: 
    ECHO %TNS_ADMIN%

    This entry can be made permanent by adding the location of the tnsnames.ora file to the system environment variables.

  16. Establish a secure connection to the database via SQL*PLUS with the below sqlplus command and confirm that a secure connection has been made with the SELECT SQL statement: 
    sqlplus admin@ORCLSSL
SELECT SYS_CONTEXT('USERENV', 'DB_NAME') || ' connected with ' || SYS_CONTEXT('USERENV', 'network_protocol') FROM DUAL;

    The response should be a string with <dbname> connected with tcps.

  17. Convert the certificate from AWS in .pem format to .der format with the following commands: 
    set OPENSSL_CONF=C:\OpenSSL\bin\openssl.cnf
C:\OpenSSL\bin\openssl x509 -outform der -in 
C:\sqldeveloper-ssl\us-gov-west-1-bundle.pem -out 
C:\oracle\ssl_wallet\us-gov-west-1-bundle.der
dir 
C:\oracle\ssl_wallet
  18. Create a keystore with the following command: 
    C:\oracle\jdk\bin\keytool -keystore 
C:\oracle\jdk\jre\lib\security\clientkeystore -genkey -alias client

    You are prompted for a password. Keep this password—it will be required in the following steps.

  19. Confirm that the file has been created: 
    dir C:\oracle\jdk\jre\lib\security\clientkeystore
  20. Import the AWS certificate (in .der format) : 
    C:\oracle\jdk\bin\keytool -import -alias rds-root -keystore 
C:\oracle\jdk\jre\lib\security\clientkeystore -file 
C:\oracle\ssl_wallet\us-gov-west-1-bundle.der
  21. Copy the JCE files: 
    copy C:\sqldeveloper-ssl\local_policy.jar C:\oracle\jdk\jre\lib\security
copy C:\sqldeveloper-ssl\US_export_policy.jar C:\oracle\jdk\jre\lib\security
  22. Edit the Oracle SQL Developer configuration file (sqldeveloper.conf) and add the following entries: 
    AddVMOption -Djavax.net.ssl.trustStore=C:\oracle\jdk\jre\lib\security\clientkeystore
AddVMOption -Djavax.net.ssl.trustStoreType=JKS
AddVMOption -Djavax.net.ssl.trustStorePassword=<keystore password>
AddVMOption -Doracle.net.ssl_cipher_suites=TLS_RSA_WITH_AES_256_CBC_SHA
  23. Configure Oracle SQL Developer database advanced features by launching Oracle SQL Developer and navigating to Tool, Preferences, Database, Advanced, and set up the Oracle client location as shown in the following screenshot.

    The location of the Oracle Home may be different in your server.
    After you make this change, choose the pop-up button to test. If the correct values have been provided, a success message will be displayed similar to the following screenshot.
  24. Create a secure connection to the database as shown in the following screenshot.

    For ease of use, the value for Connect Identifier is as follows:

    (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=<orclssl.xxxxxxx.us-gov-west-1.rds.amazonaws.com>)(PORT=2484))(CONNECT_DATA=(SID=orclssl))(SECURITY = (MY_WALLET_DIRECTORY = C:\oracle\ssl_wallet)))
  25. Confirm that the connection is via TCPS with the following SQL:SELECT 
    SYS_CONTEXT('USERENV', 'DB_NAME') || ' connected with ' || 
SYS_CONTEXT('USERENV', 'network_protocol') FROM DUAL;


    At this point, the database is listening on two ports, 1521 for TCP (unsecure) connections, and 2484 for TCPS (secure) connections. To prevent connections on port 1521, edit the inbound rules of the security group to prevent access on port 1521.

Cleanup

After the testing has been concluded, delete the RDS instances and any EC2 instances that were created for use as bastions.

Conclusion

In this post, we demonstrated how you can configure multiple database client tools to use SSL encryption with Amazon RDS for SQL Server and Amazon RDS for Oracle. SSL secures data in transit when connecting to Amazon RDS. It also ensures compliance and alignment with your organization’s security policies and guidelines.

If you have any questions or comments, post your thoughts in the comments section.

About the authors

Dean Capps is a Database Consultant at AWS and has experience providing database support on legacy, open systems, and cloud platforms. Dean is currently a member of the AWS Global Competency Centre, providing homogeneous and heterogenous database migration support to commercial and public sector clients.

Feng Cai is a Database Consultant at AWS. He has a wide background in providing database services to different clients. Feng is currently a member of the AWS Global Competency Center, providing homogeneous and heterogenous database migration support to customers.

Shirin Ali is a Sr. Customer Solutions Manager at AWS. As Technical CSM and business-strategic leader in AWS, Shirin helps accelerate the customer’s journey to the cloud, execute strong program management and ensure AWS customers are realizing business value.

Sushant Deshmukh is a Database Consultant with the AWS Professional Services. He works with AWS customers and partners to build highly available, scalable, and secure database architectures on AWS. He provides technical design and implementation expertise in running database workloads on AWS, and helps customers migrate and modernize their databases to the AWS Cloud. Outside of work, he enjoys traveling and exploring new places, playing volleyball, and spending time with his family and friends.