AWS Database Blog

Use Kerberos authentication with Amazon Aurora MySQL

Amazon Aurora MySQL-Compatible Edition offers multiple authentication methods to securely authenticate database user access and meet different security needs. The most common method of authentication is using a user name and password. This can create additional overhead for both users and database administrators to manage and rotate these credentials; it also requires additional investments in auditing and governance.

Amazon Aurora MySQL now supports Microsoft Active Directory (AD) authentication using Kerberos for Amazon Aurora MySQL version 3.03 and higher. With support for AWS Directory Service for Microsoft Active Directory integration, you can manage database users in a centralized location and use single sign-on to authenticate database users.

In this post, we discuss how to integrate self-managed Microsoft AD with AWS Managed Microsoft AD, enable Kerberos authentication in Amazon Aurora MySQL, and authenticate with Kerberos from Windows and Linux clients.

Kerberos authentication

Kerberos functions as a network authentication protocol enabling resources to mutually verify identities across untrusted networks. This capability allows Amazon Aurora MySQL to integrate with centralized authentication services such as Microsoft AD, which inherently supports Kerberos.

The Kerberos authentication workflow consists of the following steps in MySQL:

  1. The client application receives its service principal name (SPN) from Amazon Aurora MySQL.
  2. Kerberos authentication in MySQL uses Generic Security Application Program Interface (GSSAPI). With GSSAPI, the client application initiates a Kerberos client-side authentication session and communicates using Kerberos messages with the Kerberos Key Distribution Center (KDC).
  3. Using a ticket-granting ticket (TGT), the client obtains a service ticket for Amazon Aurora MySQL from the ticket-granting service.
  4. Through GSSAPI, the client application forwards the MySQL service ticket to Amazon Aurora MySQL.
  5. Through GSSAPI, Amazon Aurora MySQL initiates a Kerberos service-side authentication session. It verifies the user’s identity and the legitimacy of the user’s request.

For detailed information on Kerberos authentication in MySQL, see Kerberos Pluggable Authentication.

Solution overview

To simulate the self-managed Microsoft AD environment, you can create the Active Directory in an Amazon Elastic Compute Cloud (Amazon EC2) instance running in a VPC, AWS Managed Microsoft AD in a separate VPC, and a one-way forest trust between them. For more information about preparing the environment, see Preparing on-premises and AWS environments for external Kerberos authentication for Amazon RDS.

The following diagram illustrates the high-level architecture of the self-managed and AWS-managed environment.

Highlevel Architecture

Prerequisites

In this post, we assume you already have a self-managed Active Directory and have established a trust relationship between the self-managed and AWS-managed AD. For this post, we use an AWS Managed Microsoft AD domain named ad.myaws.com and a self-managed AD domain named onprem.local. To access the Aurora MySQL cluster located in a separate VPC, we enabled VPC peering to establish network connectivity and enabled a one-way forest trust to facilitate name suffix routing. This will allow Kerberos authentication between two Active Directory forests. To enable the forest trust, complete the following steps:

  1. In your self-managed AD, create a new conditional forwarder to forward the DNS request to the AWS-managed AD.
  2. Create a one-way incoming forest trust to the AWS-managed AD.
  3. Create a one-way outgoing forest trust to the self-managed AD from your AWS-managed AD.

For detailed information about these steps, see Preparing on-premises and AWS environments for external Kerberos authentication for Amazon RDS.

The following screenshot shows our AWS-managed AD.

AWS-managed AD

Create a new Aurora cluster

Create a new Aurora MySQL cluster with 3.03 version or higher and enable the Kerberos authentication, then choose the AWS-managed AD that you want to associate with the cluster. If the Aurora MySQL cluster is already created, you can modify it to allow Kerberos authentication, as shown in the following screenshot.

Aurora MySQL database authentication

It will take a few minutes for the new change to take into effect. You can confirm the modification is successfully applied to the instance either using the AWS Management Console or AWS Command Line Interface (AWS CLI). The following AWS CLI command shows the domain membership of the Aurora MySQL cluster:

aws rds describe-db-clusters —db-cluster-identifier yourcluster-identifier\
--region region-name\
--query 'DBClusters[*].[DomainMemberships]'
[
    [
        {
            "Domain": "d-xxyyyzzz1234",
            "Status": "kerberos-enabled",
            "FQDN": "ad.myaws.com",
            "IAMRoleName": "rds-directoryservice-kerberos-access-role"
        }
    ]
]

When enabling Kerberos authentication for an existing DB cluster, reboot all the instances within the cluster after applying the modification. See Setting up Kerberos authentication for Aurora MySQL clusters for instructions.

Create an AD domain user

Create the AD domain user based on your business security policies. For this post, we create the user joedoe@ONPREM.LOCAL, as shown in the following screenshot. We use this user to connect to the Aurora MySQL instance using Kerberos authentication.

Create a database user

Connect to the Aurora MySQL cluster using the admin user and verify Kerberos authentication is enabled:

[ec2-user@ip-10-0-3-4 ~]$ mysql -h aurora-kerberos.cluster-xxyzzz.ap-southeast-2.rds.amazonaws.com -u admin -p

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'authentication_kerberos';
+-------------------------+----------------+
| PLUGIN_NAME             | PLUGIN_STATUS  |
+-------------------------+----------------+
| authentication_kerberos | ACTIVE         |
+-------------------------+----------------+
1 row in set (0.00 sec)

Create a user for Kerberos authentication and confirm its validity. Provide the user with the essential permissions. The following command illustrates the process of creating a database user named joedoe to connect via Kerberos authentication from the ONPREM.LOCAL domain:

mysql> CREATE USER joedoe@'%' IDENTIFIED WITH 'authentication_kerberos' BY 'ONPREM.LOCAL';
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT USER, HOST, PLUGIN, AUTHENTICATION_STRING FROM mysql.user WHERE USER='joedoe';
+--------+------+-------------------------+-----------------------+
| USER   | HOST | PLUGIN                  | AUTHENTICATION_STRING |
+--------+------+-------------------------+-----------------------+
| joedoe | %    | authentication_kerberos | ONPREM.LOCAL          |
+--------+------+-------------------------+-----------------------+
1 row in set (0.00 sec)

Connect to Amazon Aurora MySQL from Windows

To connect from Windows, RDP into the Windows client machine using the domain user joedoe that was created in the self-managed AD. Create a Kerberos configuration file named krb5.conf on the Windows client machine. In this setup, the krb5.conf file should include the following details:

[libdefaults]
	dns_lookup_realm = false
	ticket_lifetime = 24h
	renew_lifetime = 7d
	forwardable = true
	rdns = false
	doNotPrompt = false
	default_realm = AD.MYAWS.COM
[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
	.rds.amazonaws.com = AD.MYAWS.COM
	.amazonaws.com.cn = AD.MYAWS.COM
	.amazon.com = AD.MYAWS.COM

Additionally, set the environment variable KRB5_CONFIG to point to the location of the krb5.conf file. For example, we set the variable in the Windows client machine as follows:

SET KRB5_CONFIG=C:\Program Files\MySQL\MySQL Server 8.0\krb5.conf

To set this variable permanently, create a Windows system environment variable. To create a ticket granting ticket and save it in the Kerberos credential cache, install MIT Kerberos ticket manager from the MIT Kerberos Distribution Page. After installation, generate the ticket for the domain user, in this case joedoe@ONPREM.LOCAL.

MIT Kerberos

We have configured all the prerequisites to connect to Amazon Aurora MySQL from the Windows client machine. Now let’s go connect to the Aurora MySQL cluster using Windows command line.

mysql -h aurora-kerberos.cluster-xxyzzz.ap-southeast-2.rds.amazonaws.com -u joedoe --plugin-authentication-kerberos-client-mode=GSSAPI \ 
--plugin-dir=”C:\Program Files\MySQL Server 8.0\lib\plugin”
Parameter Description
-h Aurora cluster end-point
-u AD user name
-plugin-authentication-kerberos-client-mode Specifies client authentication plugin mode. GSSAPI mode to enable authentication through MIT Kerberos library.
-plugin-dir Specifies the client program the location of the authentication_kerberos_client plugin.

MySQL Windows

To connect from MySQL Workbench using Kerberos authentication, complete the following steps:

  1. For Connection Method, choose NativeKerberos.
  2. On the Parameters tab, provide the following information:
    1. For Hostname, enter the Aurora MySQL cluster endpoint.
    2. For Username, enter the Aurora MySQL user name that was created for the Kerberos authentication.
    3. For Kerberos Mode, select GSS API Authentication.Workbench-newconnection
  3. On the Advanced tab, configure the following variables:
    1. For Path to plugin directory, enter the path to MySQL Server client plugin directory.
    2. For Kerberos configuration path, enter the path to the Kerberos configuration file.Workbench-Connection
  4. Choose Test Connection to test the connection to MySQL Workbench.
  5. When the test is successful, choose OK, then choose OK to complete your connection.Workbench-success

Connect to Amazon Aurora MySQL from Linux

To connect from Linux, SSH into your Linux machine using the domain user joedoe. Verify the Linux machine joined to the domain using the following command:

[joedoe@ip-10-10-0-123 ~]$ realm list
ONPREM.LOCAL
type: kerberos
realm-name: ONPREM.LOCAL
domain-name: onprem.local
configured: kerberos-member
server-software: active-directory
client-software: sssd
required-package: oddjob
required-package: oddjob-mkhomedir
required-package: sssd
required-package: adcli
required-package: samba-common-tools
login-formats: %U
login-policy: allow-realm-logins

The Kerberos client may not be installed on all the Linux machines by default. To install the Kerberos client on Amazon Linux 2, use the following command:

yum install krb5-workstation krb5-libs krb5-auth-dialog

Configure realms and domain_realm in the krb5.conf file based on your Active Directory domain in the client machine. For example, the following is the modified krb5.conf file for our test environment:

[joedoe@ip-10-10-0-123 ~]$ 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
pkinit_anchors = FILE:/etc/pki/tls/certs/ca-bundle.crt
default_realm = AD.MYAWS.COM
default_ccache_name = KEYRING:persistent:%{uid}

[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
.rds.amazonaws.com = AD.MYAWS.COM

The Kerberos authentication method is supported from MySQL version 8.0.26 and higher, so install the mysql-client version 8.0.26 or higher. After installing, check the MySQL client version:

[joedoe@ip-10-10-0-123 ~]$ mysql --version
mysql Ver 8.0.36 for Linux on x86_64 (MySQL Community Server - GPL)

Before we connect to the Aurora MySQL server, we need to obtain the ticket-granting ticket from the Microsoft Active Directory (KDC) using the kinit command by providing a Kerberos user principal name and the principal password:

[joedoe@ip-10-10-0-123 ~]$ kinit
Password for joedoe@ONPREM.LOCAL: 
[joedoe@ip-10-10-0-123 ~]$ klist
Ticket cache: KEYRING:persistent:1708001111:krb_ccache_bJBnbkx
Default principal: joedoe@ONPREM.LOCAL

Valid starting Expires Service principal
31/01/24 03:01:22 31/01/24 13:01:22 krbtgt/ONPREM.LOCAL@ONPREM.LOCAL
renew until 07/02/24 03:01:18

Connect to the Aurora MySQL server using Kerberos authentication without specifying the password:

[joedoe@ip-10-10-0-123 ~]$ mysql -h aurora-mysql-kerberos.cluster-xxyyzzz.rds.amazonaws.com -u joedoe

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 53533
Server version: 8.0.28 Source distribution
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \s
--------------
mysql  Ver 8.0.36 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:		202560
Current database:	
Current user:			joedoe@10.10.0.123
SSL:				Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager:		stdout
Using outfile:		''
Using delimiter:		;
Server version:		8.0.28 Source distribution
Protocol version:		10
Connection:				aurora-mysql-kerberos.cluster-c52zvlhqtpjr.ap-southeast-2.rds.amazonaws.com via TCP/IP
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
TCP port:			3306
Binary data as:		Hexadecimal
Uptime:			27 days 53 min 56 sec

Threads: 7  Questions: 26524524  Slow queries: 0  Opens: 394  Flush tables: 3  Open tables: 314  Queries per second avg: 11.354
--------------

Clean up

When you’re done with the solution, delete the resources you created to avoid ongoing charges.

  1. Delete the EC2 instances.
  2. Delete AWS Managed Microsoft AD
  3. Delete Aurora DB cluster
  4. Delete VPC

Conclusion

In this post, we illustrated the authentication process for a DB user in Amazon Aurora MySQL using external Kerberos and Microsoft AD. This setup relies on a one-way forest trust established between a self-managed AD domain and an AWS Managed Microsoft AD domain. Implementing this configuration enhances flexibility and simplifies the complexity for users who choose Kerberos for their database authentication.

For more information, see Using Kerberos authentication for Aurora MySQL.

About the Authors

Surendar Munimohan is a Sr. Database Solutions Architect with Amazon Web Services. He has more than a decade of experience working with relational databases and architecting highly scalable applications in the AWS. In his current role, he works with customers to design scalable, highly available, secure, and cost-effective solutions in the AWS Cloud.

Zhen Wang is a Sr. Technical Account Manager with Amazon Web Services. He works backwards from his Enterprise Support customers to craft support strategies, deliver expert advice on AWS services with the goal of achieving operational excellence in the AWS Cloud.

Sai Kiran Kshatriyais an Amazon Web Services Database Specialist Solutions Architect who specializes in Relational Databases. He offers customers technical assistance, operational guidance, and best practices for databases.