Using external Kerberos authentication with Amazon RDS for Oracle

In the first post in this series, Preparing on-premises and AWS environments for external Kerberos authentication for Amazon RDS, we built the infrastructure for a one-way forest trust between an on-premises Microsoft Active Directory (AD) domain (trust: incoming) and an AWS Managed Microsoft AD domain (trust: outgoing) provided by AWS Directory Service. In this post, we demonstrate how to join an Amazon Relational Database Service (Amazon RDS) for Oracle instance to an AWS Managed Microsoft AD and use external Kerberos and Microsoft AD to authenticate database (DB) users.

Prerequisites

Before we start, make sure that you have prepared your on-premises and AWS environments, as shown in the following architectural diagram, including one on-premises AD user joedoe@onprem and one Windows and one Linux client, which are joined to the on-premises AD domain onprem.local.

Creating RDS for Oracle instances and joining them to an AWS Managed Microsoft AD domain

We first create RDS for Oracle instances in the aws-acc-1 and aws-acc-2 accounts and join the instances to the AWS Managed Microsoft AD domain.

1. Log in to the Amazon RDS console from aws-acc-1.
2. Create the DB subnet group aws-acc-1-db-subnet-group, which includes the three subnets in aws-acc-1-rds-vpc.

3. Create the security group aws-acc-1-rds-vpc-allow-connection to allow connection from on-premises clients (in on-prem-client-vpc) to access Amazon RDS for Oracle via TCP on port 1521.

4. On the Amazon RDS console, create the RDS for Oracle instance oracle-acc-1-kerberos-19-0 in the DB subnet group aws-acc-1-db-subnet-group.
5. For VPC security group, select Choose existing.
6. For Existing VPC security groups, enter the security group you created.
7. For Database authentication options, select Password and Kerberos authentication.
8. Choose Browse Directory.

9. In the Choose directory section, select myaws.com.
10. Choose Choose.

You can now see ad.myaws.com in the Directory field.

After successfully creating the RDS for Oracle instance, we see that the instance has joined the AWS Managed Microsoft AD, and the directory status shows as kerberos-enabled.

We can also verify the directory status via the AWS Command Line Interface (AWS CLI). See the following code:

[joedoe@ip-10-11-1-23 ~]$ aws rds describe-db-instances \
> --db-instance-identifier oracle-acc-1-kerberos-19-0 \
> --query 'DBInstances[*].DomainMemberships' \
> --output table
---------------------------------------------------------------------------------------------------
|                                       DescribeDBInstances                                       |
+--------------+---------------+---------------------------------------------+--------------------+
|    Domain    |     FQDN      |                 IAMRoleName                 |      Status        |
+--------------+---------------+---------------------------------------------+--------------------+
|  d-97671c6bae|  ad.myaws.com |  rds-directoryservice-kerberos-access-role  |  kerberos-enabled  |
+--------------+---------------+---------------------------------------------+--------------------+

You can also create the RDS for Oracle instance via the AWS CLI:

aws rds create-db-instance \
--db-instance-identifier oracle-acc-1-kerberos-19-0 \
--db-instance-class db.r5.large \
--engine oracle-ee \
--license-model bring-your-own-license \
--master-username admin \
--master-user-password mysecurepassword \
--allocated-storage 100 \
--db-subnet-group-name aws-acc-1-db-subnet-group \
--vpc-security-group-ids sg-0f3e336a3baeab672 \
--port 1521 \
--domain d-97671c6bae \
--domain-iam-role-name rds-directoryservice-kerberos-access-role

For Amazon RDS for Oracle to call AWS Directory Service for you, you need an AWS Identity and Access Management (IAM) role that uses the managed IAM policy AmazonRDSDirectoryServiceAccess. When you create a DB instance on the AWS Management Console and the console user has the iam:CreateRole permission, the console creates this role automatically. For this use case, the role name is rds-directoryservice-kerberos-access-role.

For instructions on creating the role manually, see Create an IAM Role for Amazon Aurora to Access the AWS Directory Service. When you create this role, choose Directory Service, and attach the AWS managed policy AmazonRDSDirectoryServiceAccess to it.

For existing RDS for Oracle instances, we can modify the instance to join the AWS Managed Microsoft AD from the Amazon RDS console, or the AWS CLI:

aws rds modify-db-instance \
--db-instance-identifier oracle-acc-1-kerberos-19-0 \
--domain d-97671c6bae \
--domain-iam-role-name rds-directoryservice-kerberos-access-role \
--apply-immediately

11. Repeat these steps in aws-acc-2 to create an RDS for Oracle instance and join the instance to the shared AWS Managed Microsoft AD, with following details:

• • DB instance name – oracle-acc-2-kerberos-19-0
  • DB subnet groups – aws-acc-2-db-subnet-group
  • Security group – aws-acc-2-rds-vpc-allow-connection
  • Database authentication options – Password and Kerberos authentication
  • Directory – ad.myaws.com (d-97671c1f8c), shared by aws-acc-1

12. On the Amazon RDS console, make sure the RDS for Oracle instance in aws-acc-2 has joined the shared AWS Managed Microsoft AD successfully.

We can also verify the status via the AWS CLI:

[joedoe@ip-10-11-1-23 ~]$ aws rds describe-db-instances \
> --db-instance-identifier oracle-acc-2-kerberos-19-0 \
> --query 'DBInstances[*].DomainMemberships' \
> --output table
---------------------------------------------------------------------------------------------------
|                                       DescribeDBInstances                                       |
+--------------+---------------+---------------------------------------------+--------------------+
|    Domain    |     FQDN      |                 IAMRoleName                 |      Status        |
+--------------+---------------+---------------------------------------------+--------------------+
|  d-97671c1f8c|  ad.myaws.com |  rds-directoryservice-kerberos-access-role  |  kerberos-enabled  |
+--------------+---------------+---------------------------------------------+--------------------+

13. In on-prem-account, log in to the Windows client and install SQL Developer and Oracle Client.
14. Configure tnsnames.ora for the two RDS for Oracle instances:

    oracle-acc-1-kerberos-19-0 =
     (description=
       (address_list =
         (address = (protocol = TCP)
    			 (host = oracle-acc-1-kerberos-19-0.cz54v71h2eg5.ap-southeast-2.rds.amazonaws.com)
    			 (port = 1521))
       )
     (connect_data =
       (service_name = ORCL)
     )
    )
    
    oracle-acc-2-kerberos-19-0 =
     (description =
       (address_list =
         (address = (protocol = TCP)
    			 (host = oracle-acc-2-kerberos-19-0.cdsq7zfnuv4y.ap-southeast-2.rds.amazonaws.com)
    			 (port = 1521))
       )
     (connect_data =
       (service_name = ORCL)
     )
    )

15. Log in to the RDS for Oracle instance created in aws-acc-1 with the primary user account.
16. Create an on-premises AD user in the database. The username created in the database must be in uppercase.

17. Log in to RDS for Oracle instance created in aws-acc-2 with the primary user account.
18. Create an on-premises AD user in the database in uppercase.

Logging in to RDS for Oracle instances with SQL*Plus on a Windows client

To use external Kerberos and Microsoft AD to log in to RDS for Oracle instances with SQL*Plus on a Windows client, complete the following steps:

1. Log in as AD user joedoe@onprem to the Windows client that is joined to the on-premises domain onprem.local.
2. Create a krb5.conf file (C:\Oracle_Home\krb5.conf for this post) to point to the on-premises domain and AWS Managed Microsoft AD domain:

   [libdefaults]
    default_realm = ONPREM.LOCAL
    default_ccache_name = C:\TEMP\CCache.File
   [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 in ORACLE_HOME as the following:

   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. Connect to the RDS for Oracle instance created in aws-acc-1 with SQL*Plus; the connection is made successfully without a password.

5. Connect to the RDS for Oracle instance created in aws-acc-2 with SQL*Plus (also without a password).

Logging in to RDS for Oracle instances with SQL Developer on a Windows client

To use external Kerberos and Microsoft AD to log in to RDS for Oracle instances with SQL Developer on a Windows client, complete the following steps:

1. Log in as AD user joedoe@onprem to the Windows client that is joined to the on-premises domain onprem.local.
2. Modify the sqlnet.ora file in ORACLE_HOME as the following:

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

As of this writing, SQL Developer supports the connection through Kerberos only when the ticket is manually generated with okinit. Using Windows in-memory TGE (OSMSFT) rather than credential cache is not supported by SQL Developer [Oracle Doc ID 1609359.1]. This is why we need a different sqlnet.ora file with SQL*Plus and SQL Developer.

3. Launch SQL Developer.
4. Under Tools, choose Preferences.
5. In the Preferences section, expand Database.
6. Choose Advanced.
7. For Config File (krb5.conf), enter the path of the Kerberos krb5.conf file.
8. For Credential Cache File, enter the path to the temporary file that holds your client’s Kerberos ticket credential cache.
9. Choose OK.

10. Open DOS Prompt and generate a Kerberos ticket manually with the command okinit.
11. Provide the password for AD user joedoe@onprem.

A file holding the client’s Kerberos ticket credential cache is created; the location and filename are defined in the krb5.conf file.

12. Launch SQL Developer.
13. Choose New Connection.
14. For Authentication Type, choose Kerberos.
15. For Hostname, enter the hostname for the RDS for Oracle instance created in aws-acc-1.
16. For Port, enter 1521.
17. For SID, enter ORCL.

You can see the connection to the RDS for Oracle instance in aws-acc-1 is made as DB user JOEDOE@ONPREM.LOCAL via Kerberos authentication.

You can also see the connection to the RDS for Oracle instance in aws-acc-2 is made as DB user JOEDOE@ONPREM.LOCAL via Kerberos authentication.

Logging in to RDS for Oracle instances with SQL*Plus on a Linux client

To use external Kerberos and Microsoft AD to log in to RDS for Oracle instances with SQL*Plus on a Linux client, complete the following steps:

1. Install Oracle Client on a Linux client, which you created and configured in the post Preparing on-premises and AWS environments for external Kerberos authentication for Amazon RDS.
2. Confirm that the /etc/krb5.conf is configured as the following:

   [joedoe@ip-10-11-1-23 ~]$ cat /etc/krb5.conf
   # 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. Configure the tnsnames.ora file under the $ORACLE_HOME/network/admin directory for the two RDS for Oracle instances as the following:

   oracle-acc-1-kerberos-19-0 =
    (description =
      (address_list =
        (address = (protocol = TCP)
   			 (host = oracle-acc-1-kerberos-19-0.cz54v71h2eg5.ap-southeast-2.rds.amazonaws.com)
   			 (port = 1521))
      )
    (connect_data =
      (service_name = ORCL)
    )
   )
   
   oracle-acc-2-kerberos-19-0 =
    (description =
      (address_list =
        (address = (protocol = TCP)
   			 (host = oracle-acc-2-kerberos-19-0.cdsq7zfnuv4y.ap-southeast-2.rds.amazonaws.com)
   			 (port = 1521))
      )
    (connect_data =
      (service_name = ORCL)
    )
   )

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

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

5. Log in to the Linux client as joedoe@onprem.
6. Generate a Kerberos ticket manually with the command okinit<username>.
7. Provide the AD password for the user.

8. You can make connections to the RDS for Oracle instances created in aws-acc-1 and aws-acc-2 without a password via Kerberos authentication.

Summary

In this post, we demonstrated DB user authentication for Amazon RDS for Oracle using external Kerberos and Microsoft AD, based on a one-way forest trust between an on-premises AD domain and an AWS Managed Microsoft AD domain. For instructions on creating this trust environment, see Preparing on-premises and AWS environments for external Kerberos authentication for Amazon RDS.

About the Authors

Zhen Wang is an RDS Cloud Support Engineer at Amazon Web Services.

Sundar Raghavan is a Senior Specialist Solutions Architect at Amazon Web Services.