Authenticate Amazon RDS for Db2 instances using on-premises Microsoft Active Directory and Kerberos

Db2 is an IBM relational database that supports large-scale transactional and analytical workloads. Amazon Relational Database Service (Amazon RDS) for Db2 makes it easy to set up, operate, and scale Db2 deployments in the cloud. Amazon RDS automates time-consuming database administration tasks, such as provisioning, backups, software patching, monitoring, and more, to free up time to innovate and drive business value. It also offers high availability with Multi-AZ deployment, disaster recovery solutions with cross-Region backups, and security features to support your business-critical workloads. In addition, you can integrate with other IBM and AWS services to gain new insights and scale your analytics workloads.

A number of enterprise customers enable single sign-on (SSO) and centralized Kerberos authentication of database users using Microsoft Active Directory (AD). As these customers migrate their Db2 databases to Amazon RDS for Db2 , there is a need to extend this sign-on capability to Amazon RDS for Db2. For database users, this allows them to use SSO to log in to RDS instances. For administrators, they can manage user authentication and (optionally) manage database authorization centrally used Microsoft AD.

In this post we demonstrate, how you can extend your existing AD infrastructure and Kerberos authentication to Amazon RDS for Db2.

Amazon RDS supports Kerberos authentication for Db2, PostgreSQL, MySQL, Oracle, and SQL Server. For a complete list of supported database engines and AWS Regions, see Supported Regions and DB engines for Kerberos authentication in Amazon RDS.

Solution Overview

This solution involves using AWS Managed Microsoft AD (MANAGED-AD.COM) to establish a forest-level one-way outgoing trust (or two-way trust) to an on-premises AD (ANYCOMPANY.COM). We can create or modify RDS supported database engines to join MANAGED-AD with Kerberos enabled. For database authorization, database-level permissions are created for respective ANYCOMPANY groups and AD users. For more information on trusts, see Everything you wanted to know about trusts with AWS Managed Microsoft AD.

The following diagram illustrates the solution architecture.

This solution uses Terraform code to deploy the architecture, available in the accompanying GitHub repo.

The steps to deploy the solution are as follows:

We deploy two active directories and establish a forest-level one-way trust between two directories:
1. ANYCOMPANY.COM as the on-premises AD
2. MANAGED-AD.COM as the AWS managed AD
We create Amazon RDS for DB2 instance that joins MANAGED-AD. IBM license information is required to create the instance. For more details on IBM licensing, see Amazon RDS for Db2 licensing options
We also create a Windows virtual desktop using Amazon WorkSpaces as the end-user desktop, for user ID Admin. This desktop is joined to the on-premises AD ANYCOMPANY.COM and represents a database user’s desktop that they will use to access the database.
We create local permissions for the Admin user in RDS for Db2 database.
Optionally, we can create local permissions for the DBADMIN group in RDS for DB2 database instance. We then log in to the ANYCOMPANY AD domain controller and create a DBADMIN user group make user ID Admin a member of this group.
Lastly, we deploy IBM data Studio database client on a Windows virtual desktop and successfully test Kerberos authentication.

You can extend the setup in this solution for other Amazon RDS database engines that support Kerberos (PostgreSQL, MySQL, Oracle, and SQL Server).

Prerequisites

You should have the following prerequisites:

An AWS account
Git installed
Terraform installed
An integrated development environment (IDE) such as VS Code

Deploy the solution

Complete the following steps to deploy the solution:

In your IDE, open a terminal and clone the GitHub repo:
```
git clone https://github.com/aws-samples/rds-kerberos-authentication-using-onprem-active-directory.git
```
Let’s look at the code structure. This repo is structured as follows:
- locals.tf – Contains the default database name, virtual private cloud (VPC) configuration, and so on.
- vpc.tf – Creates the VPC and subnets, and configures security group rules.
- active-directory.tf – Creates two ADs (ANYCOMPANY.COM and AWS-MANAGED.COM), establishes trust between the two ADs, and stores AD passwords in AWS Secrets Manager.
- databases.tf – Deploys Amazon RDS for Db2 instance. This instance joins AWS-MANAGED.COM. The database admin password is stored in Secrets Manager.
- workspaces.tf – Creates a virtual Windows desktop for user ID Admin and joins this desktop to the external AD ANYCOMPANY.COM.
The next step is to deploy infrastructure, including the AD, trust relationship, databases, and virtual desktop user.
From the root directory of the GitHub repo, download the Terraform dependencies:
```
terraform init
```
Deploy Amazon RDS for DB2 (the IBM customer ID and site ID required in the following code are related to IBM DB2 licensing details):
```
terraform apply -var='deployRdsForDb2={ibm_customer_id="XXXXXXXX",ibm_site_id="XXXXXX"}' 
```
The infrastructure creation takes about 60–90 minutes to complete.
The output should look like the following:
```
Apply complete! Resources: 48 added, 0 changed, 0 destroyed.
```
Now you can download and configure your database client.
Download a WorkSpaces client.
On the WorkSpaces console, navigate to the workspace ID created for the user Admin and note the registration code.

This information is also available in the Terraform console output shown earlier.
On the Secrets Manager console, navigate to your secret and note the password.
The password for the virtual desktop is the same as active_directory_password for the AD domain controllers.
Log in to the virtual desktop using the WorkSpaces application.

The next step is to create a Kerberos configuration file. This file contains Kerberos configuration information, including the locations of KDCs and admin servers for the Kerberos realms of interest, defaults for the current realm and for Kerberos applications, and mappings of hostnames onto Kerberos realms
Go to C:\Windows and create the krb5.ini with the following text:

[libdefaults]
default_realm = ANYCOMPANY.COM

[realms]
ANYCOMPANY.com = {
kdc = anycompany.com
admin_server = anycompany.com
}

MANAGED-AD.COM = {
kdc = managed-ad.com
admin_server = managed-ad.com
}

[domain_realm]
.anycompany.com = ANYCOMPANY.COM
anycompany.com = ANYCOMPANY.COM
.managed-ad.com = MANAGED_AD.COM
managed-ad.com = MANAGED-AD.COM
.rds.amazonaws.com = MANAGED-AD.COM

[capaths]
ANYCOMPANY.COM = {
    MANAGED-AD.COM = .
}
MANAGED-AD.COM = {
    ANYCOMPANY.COM = .
}

Download Db2 client : download and install IBM Data Studio, and update the JAAS configuration file to use Kerberos.
Lastly, you create local users in the database using IBM data Studio on the Windows virtual desktop.
1. Get the primary database user and password from Secrets Manager for Db2.
2. From IBM Data Studio, connect to DB2 using the primary database and password, create a new user called ADMIN, and grant it connect privileges.

Test Kerberos

To test Kerberos, complete the following steps:

Close IBM Data Studio.
Open IBM data Studio using elevated privileges, IBM Data Studio à (right-click) à choose Run as administrator.
New connection -> For Driver, choose IBM Data Server Driver for JDBC and SQLJ (JDBC 4.0) using Kerberos security Default.
Enter the database and endpoint details.
Select Use cached ticket-granting ticket.
Choose Test Connection.

The connection should succeed.

Clean up

To tear down and remove the created resources, run the following code:

terraform destroy -auto-approve

Conclusion

In this post, we showed how you can extend your existing Microsoft AD infrastructure to Amazon RDS for Db2 and enable Kerberos authentication.

This solution allows your users to log in to RDS for Db2 instances using existing SSO capabilities. For administrators, this means they can use on-prem AD to centrally manage database authentication and (optionally) database authorization for RDS DB instances.

Try out this solution for your own use cases, and let us know your feedback and questions in the comments.

About the authors

Vikrant Dhir is an AWS Solutions Architect, helping systemically important financial services institutions innovate on AWS. He specializes in containers and container security using Amazon EKS. He is an avid programmer proficient in a number of languages such as Java, NodeJS and Terraform.

Vikram S Khatri is a Sr. DBE for Amazon RDS for Db2. Vikram has over 20 years of experience in Db2. He enjoys developing new products from the ground up. In his spare time, he practices meditation and enjoys listening to podcasts.

Sumit Kumar is a Senior Solutions Architect at AWS, and enjoys solving complex problems. He has been helping customers across various industries to build and design their workloads on the AWS Cloud. He enjoys cooking, playing chess, and spending time with his family.

AWS Database Blog