By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

How do I connect to an Amazon RDS for Oracle DB instance?

10 minute read
0

I want to connect to my Amazon Relational Database Service (Amazon RDS) for Oracle DB instance.

Resolution

Use any of the following methods to connect to your Amazon RDS for Oracle DB instance.

SQL*Plus with a Linux or Windows host

Use the SQL*Plus client command line interface (CLI) to connect to your database. Complete the following steps to install and configure SQL*Plus from either a Linux or Windows host:

Linux host

  1. Launch and connect to an Amazon Elastic Compute Cloud (Amazon EC2) Linux machine.

  2. Download the Instant Client Basic, Instant Client Tools, and Instant Client sqlplus rpm files from the Oracle website. To download each file, right click the rpm file, and then choose copy link address to copy the URL.

  3. Copy the URL for each download into a command, and then run the command:

    curl https://URL-COPIED-IN-STEP-2-instantclient-basic.rpm —output instant-client.rpm
    curl https://URL-COPIED-IN-STEP-2-instantclient-basic-sqlplus.rpm —output instant-client-sqlplus.rpm

  4. Install each rpm file downloaded with sudo permissions:

    sudo rpm -i instant-client.rpm
    sudo rpm -i instant-client-sqlplus.rpm

  5. Use sqlplus to connect to the RDS for Oracle DB instance:

    sqlplus 'user_name@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dns_name)(PORT=port))(CONNECT_DATA=(SID=database_name)))'

Windows host

  1. Launch and connect to an Amazon EC2 Windows machine.
  2. Download the Basic Package and the SQL*Plus Package zip files from the Oracle website.
  3. Extract the zip files.
  4. Modify the windows PATH variable to include the SQL*Plus Instant Client directory. For more information, see path on the Microsoft website.
  5. Open the command prompt window and use sqlplus to connect to the RDS for Oracle DB instance: 
    sqlplus user_name@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dns_name)(PORT=port))(CONNECT_DATA=(SID=database_name)))
sqlplus user_name@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dns_name)(PORT=port))(CONNECT_DATA=(SID=database_name)))

SQL Developer with a Windows host

SQL Developer is a Graphic User Interface that you install on your host machine to interact with an RDS for Oracle DB instance. Complete the following steps to install and configure SQL Developer. Then, connect to an RDS for Oracle DB instance from a Windows host. For more information see Connect to your DB instance using Oracle SQL developer.

  1. Launch and connect to an Amazon EC2 Windows machine.
  2. Download SQL Developer from the Oracle website, and then extract the zip file.
  3. Open sqldeveloper.exe from the extracted location.
  4. Choose Connections, and then choose New Connection.
  5. In the New/Select Database Connection dialog box, enter a connection name, username, and password.
  6. For the Hostname, enter the endpoint for your RDS for Oracle DB instance.
  7. Enter the port and SID for your RDS for Oracle DB instance.
  8. Choose Connect.

If you experience errors when you connect to the RDS for Oracle DB instance, then see Troubleshooting connections to your Oracle DB instance.

Kerberos with a Linux or Windows host

When you use Kerberos with your DB instances, you have the option to forward authentication requests to AWS Directory Service for Microsoft Active Directory. Complete the following steps to configure clients on either a Linux or Windows host. Then, connect to your RDS for Oracle DB instance with Kerberos turned on. For more information see Using external Kerberos authentication with Amazon RDS for Oracle.

Note: Before you complete the following steps, you must configure Kerberos on your RDS for Oracle DB instance. For more information, see Setting up Kerberos authentication for RDS for Oracle DB instances.

Linux host (AWS Managed Microsoft AD joined)

  1. Log in to your Linux host and confirm that SQL*Plus is installed on the host.

  2. Install the kerberos kinit utility:

    sudo yum install  krb5-libs krb5-workstation

  3. Create a krb5.conf file that points to the on-premises domain and the AWS Managed Microsoft AD domain:

    # Configuration snippets may be placed in this directory as well 
includedir /etc/krb5.conf.d/ 
includedir /var/lib/sss/pubconf/krb5.include.d/ 
[logging] 
default = FILE:/var/log/krb5libs.log 
kdc = FILE:/var/log/krb5kdc.log 
admin_server = FILE:/var/log/kadmind.log 
[libdefaults] 
dns_lookup_realm = false 
ticket_lifetime = 24h 
renew_lifetime = 7d 
forwardable = true 
rdns = false 
default_realm = ONPREM.LOCAL 
default_ccache_name = /tmp/kerbcache 
[realms] 
AD.MYAWS.COM = { kdc = ad.myaws.com admin_server = ad.myaws.com } 
ONPREM.LOCAL = { kdc = onprem.local admin_server = onprem.local }
[domain_realm] 
.ad.myaws.com = AD.MYAWS.COM 
ad.myaws.com = AD.MYAWS.COM 
.onprem.local = ONPREM.LOCAL 
onprem.local = ONPREM.LOCAL

  4. Configure the sqlnet.ora file under the $ORACLE_HOME/network/admin directory:

    SQLNET.KERBEROS5_CC_NAME = /tmp/kerbcache 
SQLNET.AUTHENTICATION_SERVICES = (KERBEROS5PRE,KERBEROS5) 
SQLNET.KERBEROS5_CONF = /etc/krb5.conf 
SQLNET.KERBEROS5_CONF_MIT = TRUE

  5. To manually generate a Kerberos ticket, run the following command:

    kinit user@onprem.com

  6. Enter the password for the AWS Managed Microsoft AD user, and then connect to the RDS for Oracle DB instance:

    sqlplus /@//RDSEndpoint:1521/dbname

Linux host (non-AD joined)

  1. Connect to the Linux host.

  2. Make sure that the DHCP option that's set for your virtual private cloud (VPC) includes the DNS for your domain. For more information, see Create a DHCP option set.

  3. To confirm the communication to your domain, run the following command:

    ping ONPREM.COM

  4. Install the Kerberos kinit utility on the Linux host:

    sudo yum install  krb5-libs krb5-workstation

  5. Create a krb5.conf file that points to the on-premises domain and the AWS Managed Microsoft AD domain:

    # Configuration snippets may be placed in this directory as well 
includedir /etc/krb5.conf.d/ 
includedir /var/lib/sss/pubconf/krb5.include.d/ 
[logging] 
default = FILE:/var/log/krb5libs.log 
kdc = FILE:/var/log/krb5kdc.log 
admin_server = FILE:/var/log/kadmind.log 
[libdefaults] 
dns_lookup_realm = false 
ticket_lifetime = 24h 
renew_lifetime = 7d 
forwardable = true 
rdns = false 
default_realm = ONPREM.LOCAL 
default_ccache_name = /tmp/kerbcache 
[realms] 
AD.MYAWS.COM = { kdc = ad.myaws.com admin_server = ad.myaws.com } 
ONPREM.LOCAL = { kdc = onprem.local admin_server = onprem.local }
[domain_realm] 
.ad.myaws.com = AD.MYAWS.COM 
ad.myaws.com = AD.MYAWS.COM 
.onprem.local = ONPREM.LOCAL 
onprem.local = ONPREM.LOCAL

  6. To configure the sqlnet.ora file under the $ORACLE_HOME/network/admin directory, run the following command:

    SQLNET.KERBEROS5_CC_NAME = /tmp/kerbcache 
SQLNET.AUTHENTICATION_SERVICES = (KERBEROS5PRE,KERBEROS5) 
SQLNET.KERBEROS5_CONF = /etc/krb5.conf 
SQLNET.KERBEROS5_CONF_MIT = TRUE

  7. To manually generate a Kerberos ticket, run the following command:

    kinit user@onprem.com

  8. Enter the password for the AWS Managed Microsoft AD user.

  9. Connect to the RDS for Oracle DB instance:

    sqlplus /@//RDSEndpoint:1521/dbname

SQL*Plus with a Windows host

  1. Use your AWS Managed Microsoft AD user to log in to the Windows host.

  2. Create a krb5.conf file that points to the on-premises domain and the AWS Managed Microsoft AD domain:

    # Configuration snippets may be placed in this directory as well 
includedir /etc/krb5.conf.d/ 
includedir /var/lib/sss/pubconf/krb5.include.d/ 
[logging] 
default = FILE:/var/log/krb5libs.log 
kdc = FILE:/var/log/krb5kdc.log 
admin_server = FILE:/var/log/kadmind.log 
[libdefaults] 
dns_lookup_realm = false 
ticket_lifetime = 24h 
renew_lifetime = 7d 
forwardable = true 
rdns = false 
default_realm = ONPREM.LOCAL 
default_ccache_name = /tmp/kerbcache 
[realms] 
AD.MYAWS.COM = { kdc = ad.myaws.com admin_server = ad.myaws.com } 
ONPREM.LOCAL = { kdc = onprem.local admin_server = onprem.local }
[domain_realm] 
.ad.myaws.com = AD.MYAWS.COM 
ad.myaws.com = AD.MYAWS.COM 
.onprem.local = ONPREM.LOCAL 
onprem.local = ONPREM.LOCAL

  3. Modify the sqlnet.ora file to make sure that the path locations match your environment's path locations:

    SQLNET.AUTHENTICATION_SERVICES = (KERBEROS5PRE,KERBEROS5)
SQLNET.KERBEROS5_CONF = C:\Oracle_Home\krb5.conf 
SQLNET.KERBEROS5_CONF_MIT = true 
SQLNET.KERBEROS5_CC_NAME = OSMSFT: 
SQLNET.FALLBACK_AUTHENTICATION = TRUE

  4. Open the command prompt, and use sqlplus to connect to your RDS for Oracle DB instance:

    sqlplus /@//RDSEndpoint:1521/dbname

SQL Developer with a Windows host

  1. Use your AWS Managed Microsoft AD user to log in to the Windows host.

  2. Modify the sqlnet.ora file to include the following one:

    SQLNET.AUTHENTICATION_SERVICES = (KERBEROS5PRE,KERBEROS5) 
SQLNET.KERBEROS5_CONF = C:\Oracle_Home\krb5.conf 
SQLNET.KERBEROS5_CONF_MIT = true

  3. Create a krb5.conf file that points to the on-premises domain and the AWS Managed Microsoft AD domain:

    # Configuration snippets may be placed in this directory as well 
includedir /etc/krb5.conf.d/ 
includedir /var/lib/sss/pubconf/krb5.include.d/ 
[logging] 
default = FILE:/var/log/krb5libs.log 
kdc = FILE:/var/log/krb5kdc.log 
admin_server = FILE:/var/log/kadmind.log 
[libdefaults] 
dns_lookup_realm = false 
ticket_lifetime = 24h 
renew_lifetime = 7d 
forwardable = true 
rdns = false 
default_realm = ONPREM.LOCAL 
default_ccache_name = /tmp/kerbcache 
[realms] 
AD.MYAWS.COM = { kdc = ad.myaws.com admin_server = ad.myaws.com } 
ONPREM.LOCAL = { kdc = onprem.local admin_server = onprem.local }
[domain_realm] 
.ad.myaws.com = AD.MYAWS.COM 
ad.myaws.com = AD.MYAWS.COM 
.onprem.local = ONPREM.LOCAL 
onprem.local = ONPREM.LOCAL

  4. Open SQL Developer, and then under Tools, choose Preferences.

  5. Expand Database, and then choose Advanced.

  6. For Config File (krb5.conf), enter the path of the Kerberos krb5.conf file that you created.

  7. For Credential Cache File, enter a path to the temporary file that holds your client's Kerberos ticket credential cache.

  8. Choose OK.

  9. Open the Command Prompt and use the okinit command to generate a Kerberos ticket.

  10. Enter the password for the AWS Managed Microsoft AD user that you're logged in on.

  11. In SQL Developer, choose new connection.

  12. For authentication type, choose Kerberos.

  13. For hostname, enter the Amazon RDS endpoint for your RDS for Oracle DB instance.

  14. Enter the port and SID for your DB instance, and then choose Connect.

SSL option with the RDS for Oracle DB instance

Some environments require that you establish only secure database connections. Add the SSL option to the option group of your RDS for Oracle DB instance to meet this criterium.

After you add the SSL option to your DB instance, you must configure your clients to correctly connect to your instance. To connect your RDS for Oracle DB instance when the SSL option is configured, complete the following steps. Use either SQL*Plus or SQL Developer.

SQL*Plus

  1. Use the Orapki utility to create an Oracle wallet and add the necessary certificate bundles.

  2. Modify SQLNET.ORA to include the location of the wallet that you created:

    WALLET_LOCATION 
    =(source 
        =(method=file)
         (method_data=(DIRECTORY=—path-to-wallet))
     )

  3. Modify TNSNAMES.ORA to include an entry for the RDS for Oracle DB instance with the SSL_SERVER_CERT_DN parameter:

    ORCL = (DESCRIPTION = (ADDRESS_LIST = ADDRESS = (PROTOCOL = TCPS)
(HOST = INSERT-RDS-ENDPOINT) (PORT = 1521)))(CONNECT_DATA = (SID = ORCL))
(SECURITY = (SSL_SERVER_CERT_DN = "C=US,ST=Washington,L=Seattle,O=Amazon.com,OU=RDS,CN=RDSendpoint")

  4. Use the alias in tnsnames.ora to connect to your instance:

    sqlplus admin@ORCL

  5. Confirm that the connection is tcps:

    select sys_context(‘userenv’, ‘network_protocol’) from dual;

SQL Developer

  1. Use the keytool utility to create a keystore in JKS format. For more information, see Creating a KeyStore in JKS format on the Oracle website.

    keytool -keystore clientkeystore -genkey -alias client

  2. Convert the Amazon RDS root certificate authority (CA) certificate to .der format:

    openssl x509 -outform der -in rds-ca-2019-root.pem -out rds-ca-2019-root.der

  3. Use the keytool utility to import the certificate into the keystore. For more information on how to trust the Amazon RDS root CA, see Setting up an SSL connection over JDBC.

    keytool -import -alias rds-root -keystore clientkeystore.jks -file rds-ca-2019-root.der

  4. Locate the sqldeveloper.conf file on the Windows host, and then append the following lines:

    AddVMOption -Djavax.net.ssl.trustStore=
AddVMOption -Djavax.net.ssl.trustStoreType=JKS
AddVMOption -Djavax.net.ssl.trustStorePassword=

  5. Restart SQL Developer, and then create a new connection.

  6. For connection type, choose Custom JDBC.

  7. In the box for custom JDBC, enter the following to match your domain:

    jdbc:oracle:thin:@(DESCRIPTION= (ADDRESS=
 (PROTOCOL=TCPS)(PORT=1521)(HOST=RDS-ENDPOINT))
(CONNECT_DATA = (SID = ORCL))
(SECURITY=(ssl_server_cert_dn="C=US,ST=Washington,L=Seattle,O=Amazon.com,OU=RDS,CN=RDSendpoint")))

  8. Choose Connect.

  9. Confirm that the connection is tcps:

    select sys_context(‘userenv’, ‘network_protocol’) from dual;
Topics
Database
Tags
Oracle
Language
English
AWS OFFICIAL
AWS OFFICIALUpdated a year ago
No comments

Relevant content