Setting up passwordless login from Amazon EC2 Windows and Linux instances to Amazon RDS Oracle database instances – idk.dev

In todays 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 dont 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 dont 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.

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 dont 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:

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.

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:

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.

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.

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

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

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.

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.

To use Linux, complete the following steps:

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

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).

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

Alternatively, enter the following code:

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.

To use Linux, complete the following steps.

For example, see the modified code:

Oracle doesnt understand the KEYRING cache format. Therefore, we changed it to FILE format.

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.

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 doesnt already exist as a file or a directory.The following example code is of the krb5.ini file contents:

Its 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. Dont set the KRB5CCNAME system variable when the default cache name is defined in the configuration file. Additionally, you cant use %{uid} as a file name format when passed as a system variable.

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

If youre 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.

To connect to the database, complete the following steps:

For Windows, enter the following code:

For Windows, no additional environment variables are required.

For Windows, enter the following code:

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

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.

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

See more here:
Setting up passwordless login from Amazon EC2 Windows and Linux instances to Amazon RDS Oracle database instances - idk.dev