AWS Database Blog

Setting up passwordless login from Amazon EC2 Windows and Linux instances to Amazon RDS Oracle database instances

In today’s world, every organization uses a centralized location to store and manage user credentials. The most commonly used service for this is Microsoft Active Directory (AD). Organizations use LDAP protocol to authenticate users to their peripheral devices, but fewer companies use this centralized credential store to allow users to log in to their databases. If you don’t use a centralized Active Directory for database authentication, the database administrator has to maintain separate key chain for every database a user needs access to. And implementing security measures such as changing passwords periodically becomes a nightmare.

Kerberos is a network authentication protocol that functions by implementing secret key cryptography. This system is used to verify the identity of a user or a host. System administrators can use AWS Directory Service for Microsoft Active Directory to manage the Active Directory. You can use the same AD credentials to log in to an Amazon Elastic Compute Cloud (Amazon EC2) instance and further authenticate into an Amazon Relational Database Service (Amazon RDS) Oracle database instance. This is all done by using tokens. With Kerberos, after the first authentication, the client holds a ticket so that additional authentication attempts don’t overload the AWS Managed Microsoft AD authentication server. In addition, Kerberos facilitates a strong and secure authentication without transmitting passwords. As an additional benefit, you get access to a centralized place to store and manage credentials for multiple database instances.

The purpose of this post is to assist you in setting up Kerberos authentication for Amazon RDS using Oracle database instances from scratch. It delineates specific steps for creating an AWS Managed Microsoft AD, AD users, setting up an Amazon EC2 Linux or Windows instance to connect to an AWS Managed Microsoft AD, and using Kerberos authentication to log in to a database instance from an EC2 instance.

The steps in this walkthrough are structured to complete the setup with minimum configuration changes. For this post, you use Windows Server 2019 AMI for Amazon EC2 Windows, an Amazon Linux 2 AMI for Amazon EC2 Linux, and Oracle Enterprise Edition 12.2.0.1 for Amazon RDS Oracle instance.

Solution overview

After the solution is implemented, users can use the same AD credentials to log in to an EC2 instance and log in to the database with the same credentials. A database administrator still has to create a user account at the database level with the same name as in Active Directory. You also still manage the required grants and privileges of the user at the database level as you would for any other database level user. The only difference is that you don’t manage the user credentials. This provides two-layer security protection. When the user no longer needs database access, you can simply revoke access at the database level. If the user is no longer needed and is removed from Active Directory, the database access is removed automatically.

The following diagram illustrates the solution architecture.

The workflow includes the following steps:

  1. The user logs in to an EC2 instance using the AD credentials.
  2. Upon being authenticated by the AWS Managed Microsoft AD, the user gets a ticket granting ticket from the KDC (Key Distribution Center).
  3. Upon initiating a SQL*Plus or SQL Developer connection, it requests KDC to provide a service ticket.
  4. The KDC provides a service ticket.
  5. The SQL client presents this service ticket to the Amazon RDS Oracle database.
  6. The database instance checks the validity of the ticket against the KCD.
  7. The user is granted access to the database.

This Oracle feature has been tested with Oracle Client (SQL*Plus) and Oracle SQL Developer with a JDBC thin client. For other clients, refer to vendor support or the documentation for Kerberos support.

Walkthrough overview

The following are the high-level steps to configure an Amazon EC2 Linux or Windows machine that connects to an AWS Managed Microsoft AD and uses Kerberos authentication to log in to an Amazon RDS Oracle database instance:

  1. Create an AWS Managed Microsoft AD.
  2. Launch an Amazon EC2 Windows machine (for Active Directory Administration) and install the Active Directory Administration Tools.
  3. Create AD users with the Active Directory Users and Computers tool.
  4. Launch an Amazon EC2 Linux or Windows instance (which connects to the database) and make it part of the AD domain.
  5. Install Oracle Client on Amazon EC2.
  6. Modify or create an Amazon RDS Oracle DB instance to use Kerberos authentication.
  7. Configure the EC2 instance to enable Kerberos authentication.
  8. Connect to the database using Kerberos authentication (SQL*Plus or SQL Developer).

After you complete these steps, you can log in to an Amazon RDS Oracle database instances using passwordless login.

These steps include setting up an AWS Managed Microsoft AD and launching an EC2 instance as part of the AD domain. If your environment already has an EC2 instance set up that is part of the AD domain, you can jump to Step 5: Installing Oracle Client. These steps work using an AWS Managed Microsoft AD.

To set up Kerberos authentication using an on-premises or self-hosted Microsoft AD, create a forest trust or external trust. The trust can be one-way or two-way. For more information about setting up forest trusts using AWS Directory Service, see When to Create a Trust Relationship.

Step 1: Creating an AWS Managed Microsoft AD

To create a new directory, perform the following steps. Before starting this procedure, make sure that you have completed the prerequisites identified in AWS Managed Microsoft AD Prerequisites.

  1. On the AWS Directory Service console, choose Directories.
  2. Choose Set up directory.
  3. On the Select directory type page, choose AWS Managed Microsoft AD.
  4. Choose Next.
  5. For Edition, choose Standard Edition.
    AWS Managed Microsoft AD (Standard Edition) is optimized to be a primary directory for small and midsize businesses with up to 5,000 employees. It provides you enough storage capacity to support up to 30,000 directory objects, such as users, groups, and computers. Upper limits are approximations. Your directory may support more or less directory objects depending on your objects’ size and your applications’ behavior and performance needs. For more information about editions, see AWS Directory Service for Microsoft Active Directory.
  6. Enter a DNS name.
    This is the fully qualified name for the directory. For this post, we enter test.com.
  7. Enter a directory NetBIOS name (for this post, we enter TEST).
  8. Enter a directory description.
  9. Enter the admin user password.
    You use this credential to log in to Amazon EC2 Windows instance to manage other domain users and manage the AD.
  10. Choose a VPC where all the other resources are also available.
  11. Choose Next.
  12. Confirm the information and choose Create directory.
    After direction creation is complete, the directory details page displays the two assigned DNS IP addresses. Both the directory DNS name and Kerberos DNS name (KDC) is test.com. The directory NetBIOS is TEST. It additionally creates a new security group that you can add to the Amazon RDS and EC2 instances. At the minimum, DNS port 53 and Kerberos ports (88 and 464) over TCP/UDP must be kept open.

For more information, see Create Your AWS Managed Microsoft AD directory.

Step 2: Launching an Amazon EC2 Windows instance and installing the Active Directory Administration Tools

To manage the Active Directory from an EC2 Windows instance, complete the following steps.

  1. Add the Amazon EC2 Windows instance to Active Directory. For instructions, see Seamlessly Join an Amazon EC2 Windows instance.
  2. Install Active Directory Administration Tools and a telnet client for checking the connectivity to RDS instance. For instructions, see Installing the Active Directory Administration Tools.

Step 3: Creating AD users with the Active Directory Users and Computers tool

Complete the following steps to create additional users as necessary. By default, all users get access to log in to any Amazon EC2 Linux instance. To get RDP access to connect to the Amazon EC2 Windows instance, the users need to be added to the appropriate AWS delegated groups in the AD.

  1. Log in to the Amazon EC2 Windows instance using the Admin See the following code: 
    Username: TEST\Admin
Password: The password set during directory creation
  2. Create your AD users. For instructions, see Create a User.
    For this post, we create the user name troy@test.com.
    You can leave Make sure the User must change password at next logon unselected. You can also just use the Admin user to complete the setup and test the Kerberos authentication.

Step 4: Launching an Amazon EC2 Windows or Linux instance and configuring it to join AWS Managed Microsoft AD

The following steps add an EC2 instance to be part of the AWS Managed Microsoft AD. This post shows how to use an Amazon Linux 2 AMI and Windows Server 2019. After you add the instance, log in using an AD user and verify the connectivity between Amazon EC2 and Amazon RDS instance with a utility like telnet.

Linux

To use Linux, complete the following steps:

  1. Configure the Amazon EC2 Linux instance to use the DNS server IP addresses of the AWS Managed Microsoft AD. For instructions, see options 1 and 2 in My private Amazon EC2 instance is running Amazon Linux, Ubuntu, or RHEL. How do I assign a static DNS server to the EC2 instance that persists during reboot?
    The DNS configuration may get removed after stopping, starting, and rebooting the instance. You should confirm these settings after the restart of the instance.
  2. Join the Amazon EC2 Linux instance. For instructions, see Manually Join a Linux instance.
    To join the instance to the directory, you can use the Admin@test.com account.
    If you see a timeout error while running the realm command, recheck the DNS configuration and confirm that they are in place correctly.
    Adhere to the following capitalization while running the realm command: Admin@TEST.COM.
  3. Test the connection using the following code: 
    ssh TEST\\Admin@ec2-xxxxxxx.compute-1.amazonaws.com

Windows

For instructions on joining an Amazon EC2 Windows instance to an AWS Managed Microsoft AD, see Seamlessly Join an Amazon EC2 Windows instance.

Step 5: Installing Oracle Client

In this step, you install the appropriate Oracle client software on the Amazon EC2 Linux or Windows instance, which is a part of AWS Managed Microsoft AD. For this post, we tested the solution on an Oracle 12.2.0.1 client version.

On Windows, it also works with SQL Developer (without the need to install Oracle client with it).

Step 6: Configuring Kerberos on the Amazon RDS Oracle instance

In this step, you modify the Amazon RDS Oracle instance from the console to enable Kerberos authentication.

  1. In the Database authentication section, choose Password and Kerberos authentication.
  2. Choose Browse Directory.
  3. Select the directory (test.com).
    For more information on enabling Kerberos, see Create or Modify an Oracle DB Instance.
  4. Connect to the Amazon RDS Oracle instance using SQL*Plus.
  5. Create one DB user for every AD user who wants to log in to the database (use all caps for the username). See the following code: 
    [oracle]

$ sqlplus admin@orcl

CREATE USER "TROY@TEST.COM" IDENTIFIED EXTERNALLY;
GRANT CREATE SESSION TO "TROY@TEST.COM";

    Alternatively, enter the following code:

    create user troy identified externally as 'troy@TEST.COM';
GRANT CREATE SESSION TO troy;

# For above command keep username capitalization same as that in AD

Step 7: Configuring Kerberos on the EC2 instance to work with Oracle client

The following steps set up the Kerberos configuration files and configure sqlnet.ora to enable Kerberos authentication service. This post presents the steps for both Linux and Windows.

Linux

To use Linux, complete the following steps.

  1. Modify the Kerberos configuration file.
    The default contents of the /etc/krb5.conf file may differ from different OS versions. This post uses Amazon Linux 2 AMI. This file exists; just edit the file and add or modify the following code:

    [root]

vi /etc/krb5.conf

[libdefaults]
default_ccache_name = FILE:/tmp/kerbcache-%{uid}

[realms]
TEST.COM = {
kdc = test.com
admin_server = test.com
}

    For example, see the modified code:

    # 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 
pkinit_anchors = /etc/pki/tls/certs/ca-bundle.crt

# ccache_type=4
# default_realm = EXAMPLE.COM
# default_ccache_name = KEYRING:persistent:%{uid} 
default_ccache_name = FILE:/tmp/kerbcache-%{uid} 
default_realm = TEST.COM

[realms]
# EXAMPLE.COM = {
#  kdc = kerberos.example.com
#  admin_server = kerberos.example.com
# }

TEST.COM = {  
kdc = test.com  
admin_server = test.com 
}

[domain_realm]
# .example.com = EXAMPLE.COM
# example.com = EXAMPLE.COM 

test.com = TEST.COM 
.test.com = TEST.COM

    Oracle doesn’t understand the KEYRING cache format. Therefore, we changed it to FILE format.

  2. Reboot the EC2 instance or restart the Kerberos service for the configuration changes to take effect.
  3. Edit sqlnet.ora to add the following parameters (for this post, we used an Oracle 12.2.0.1 client): 
    [oracle]

vi sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES=(KERBEROS5)
SQLNET.KERBEROS5_CONF=/etc/krb5.conf
SQLNET.KERBEROS5_CONF_MIT=TRUE
SQLNET.FALLBACK_AUTHENTICATION=TRUE

*In case you only want to allow Kerberos authentication then modify FALLBACK_AUTHENTICATION parameter to FALSE

Windows

To use Windows, first download and install MIT Kerberos for Windows 4.1. For more information about Kerberos and downloading links for the installer, see Kerberos: The Network Authentication Protocol.

  1. Download and install the appropriate Kerberos installer.
    For instructions, see MIT Kerberos Distribution Page.
    Use the latest version available. The current release is MIT Kerberos for Windows 4.1. For a 64-bit machine, use MSI Installer kfw-4.1-amd64.msi. For a 32-bit machine, use MSI Installer kfw-4.1-i386.msi.
    The 64-bit installer includes both 32-bit and 64-bit libraries. The 32-bit installer includes 32-bit libraries only.
  2. To run the installer, double-click the .msi file that you downloaded and follow the instructions.
  3. As an optional step, you can customize the default settings by editing the Kerberos configuration file located under C:\ProgramData\MIT\Kerberos5\krb5.ini (normally this is a hidden directory). You can also provide a custom location of configuration file and cache file by setting system environment variables:
    –        Choose PC Properties
    –            Choose Advanced System Settings
    –            On the Advanced tab, choose Environment Variables
    Update KRB5_CONFIG and KRB5CCNAME. For example, see the following code:

    KRB5_CONFIG=C:\ProgramData\MIT\Kerberos5\krb5.ini
KRB5CCNAME=C:\Temp\krb5cache

    krb5cache is a file (not a directory) managed by the Kerberos software, and it should not be created by the user. If you receive a permission error when you first use Kerberos, make sure that the krb5cache file doesn’t already exist as a file or a directory.
    The following example code is of the krb5.ini file contents:

    [libdefaults]
 default_realm = TEST.COM
 default_ccache_name = C:\Temp\krb5cache-%{uid}
 
[realms]
 TEST.COM = {
  kdc = test.com
  admin_server = test.com
 }

[domain_realm]
 test.com = TEST.COM
 .test.com = TEST.COM

    It’s preferable to set the cache location via configuration file using the %{uid} file name format. This ensures that the file names are unique for every user. Don’t set the KRB5CCNAME system variable when the default cache name is defined in the configuration file. Additionally, you can’t use %{uid} as a file name format when passed as a system variable.

  4. Reboot the EC2 instance or restart the Kerberos service for the configuration changes to take effect.

If Oracle Client is installed

If Oracle Client is installed, edit sqlnet.ora to add the following parameters:

SQLNET.AUTHENTICATION_SERVICES=(KERBEROS5)
SQLNET.KERBEROS5_CONF=AUTO_DISCOVER
SQLNET.KERBEROS5_CONF_MIT=TRUE
SQLNET.FALLBACK_AUTHENTICATION=TRUE

*In case you only want to allow Kerberos authentication then modify FALLBACK_AUTHENTICATION parameter to FALSE

If SQL Developer is installed

If you’re using SQL Developer, no additional configuration changes are required (you can also skip the step to install MIT Kerberos software).

In the connection string properties, for Authentication Type, choose Kerberos and enter the credentials.

Step 8: Connecting to the database

To connect to the database, complete the following steps:

  1. Log in to EC2 instance using the AD user credentials.
    For Linux, enter the following code:

    ssh TEST\\troy@ecx-x-xx-xxx-xx.compute-1.amazonaws.com

    For Windows, enter the following code:

    User name: TEST\troy
  2. Set the environment variables.
    For Linux, enter the following code:

    [troy@test.com]

export ORACLE_BASE=/u01/app/oracle/; export ORACLE_BASE
export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db; export ORACLE_HOME
export ORACLE_TERM=xterm; export ORACLE_TERM
export PATH=$ORACLE_HOME/bin:$PATH:.; export PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

    For Windows, no additional environment variables are required.

  3. Employ the existing Kerberos token used while logging into the EC2 instance to log in to the Oracle database.
    If no token is listed upon entering oklist, generate a new token by entering okinit (refer to the following section on troubleshooting for more details). By default, a token is valid for 10 hours, after which you have to regenerate it.
    For Linux, enter the following code:

    [troy@test.com]

$ sqlplus /@orcl

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 3 18:40:35 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show user
SQL> USER is "TROY@TEST.COM"

    For Windows, enter the following code:

    C:\Users\Admin>sqlplus /@orcl

SQL*Plus: Release 12.2.0.1.0 Production on Mon May 11 07:59:57 2020
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Last Successful login time: Mon May 11 2020 07:59:06 +00:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show user
SQL> USER is "TROY@TEST.COM"

Troubleshooting login issues

The following troubleshooting steps are the same for both Linux and Windows (in Windows, Oracle Client must be installed).

  1. Log in to the EC2 instance using the AD credentials.
    You can create a new token or destroy and recreate a Kerberos token and reattempt connecting to the database.
  2. Verify if Oracle can read the existing Kerberos token. See the following code: 
    $ oklist

Kerberos Utilities for Linux: Version 12.2.0.1.0 - Production on 07-MAY-2020 05:48:16
Copyright (c) 1996, 2016 Oracle.  All rights reserved.
Configuration file : /etc/krb5.conf.
Ticket cache: FILE:/tmp/krb5cache-271601113
Default principal: troy@TEST.COM
Valid starting     Expires            Service principal
05/07/20 05:47:59  05/07/20 15:47:59  krbtgt/TEST.COM@TEST.COM
        renew until 05/14/20 05:47:59
  3. Generate a new Kerberos token and attempt to log in. See the following code: 
    $ okinit TROY@TEST.COM

# Enter the AD user password

$ oklist

$ sqlplus /@orcl
  4. If you want to destroy all the existing Kerberos tokens, enter the following code: 
    $ okdstry

Summary

This post described how to set up Kerberos authentication for Amazon RDS Oracle database instances. For more information, see Using Kerberos authentication with Amazon RDS for Oracle and Configuring Kerberos Authentication.

If you have any questions, concerns, or comments, please leave your thoughts in the comments section.

 

 

About the Author

 

Tirthadeep Roy is a Cloud Support Engineer with Amazon Web Services.