Beginning with MySQL 8.0.26, MySQL Enterprise Edition supports an authentication method that enables users to authenticate to MySQL Server using Kerberos, provided that appropriate Kerberos tickets are available or can be obtained. Kerberos-based pluggable authentication is a part of MySQL Enterprise security. This method of authentication essentially enables you to integrate MySQL security with existing security infrastructure like Microsoft Active Directory or Azure AD.
The same user names, passwords, and permissions can be used to enhance security by leveraging existing AD rules for strong password enforcement, password expiry, etc.
MySQL Native Kerberos Authentication enables customers to leverage existing Kerberos authentication infrastructure such as single sign-on. Both MIT (GSSAPI) and Microsoft (SSPI) Kerberos implementations are supported.
<p class="info">Kerberos Server Side plugin used for Microsoft AD integration with MySQL is only available for MySQL Enterprise Edition from 8.0.26</p>
Integrate existing AD to MySQL Enterprise hosted in Linux in 3 steps
Prerequisites
Microsoft AD or Azure AD.
Sufficient privileges to create a AD user in Windows AD Domain Controller or Azure.
Connectivity between Domain Controller (DC) to Linux Server. Appropriate TCP/UDP ports needs to be allowed, and whitelisted to Security Groups or Firewalls.
<p class="info">KerberosEncryptionType variable needs to adjusted as per encryption method configured.</p>
Test AD user authentication in Linux server using kinit.
Copied to clipboard!
[ec2-user@ip-10-10-60-217 ~]$ kinit mysqladuser@TESSELLPOC.COM -V
Using existing cache: 1000
Using principal: mysqladuser@TESSELLPOC.COM
Password for mysqladuser@TESSELLPOC.COM:
Authenticated to Kerberos v5
[ec2-user@ip-10-10-60-217 ~]$
Enable Kerberos Server Side plugin in MySQL Enterprise
Server side plugin file authentication_kerberos.so is already present with MySQL Enterprise software distribution under <default installation path>/lib/plugin/
~ Modify or Add following Server side variables in my.cnf.
<p class="info">Format for SPN is <service name>/<AD Server>@<REALM NAME> </p>
Alternatively we can load the plugin at runtime using below steps.
Copied to clipboard!
[ec2-user@ip-10-10-60-217 plugin]$ mysqlsh root@localhost:3306 --sql
MySQL Shell 8.0.33
Copyright (c) 2016, 2023, 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 '\?' for help; '\quit' to exit.
Creating a session to 'root@localhost:3306'
Fetching global names for auto-completion... Press ^C to stop.
Your MySQL connection id is 404
Server version: 8.0.34 MySQL Community Server - GPL
No default schema selected; type \use schema to set one.
MySQL localhost:3306 ssl SQL > INSTALL PLUGIN authentication_kerberos SONAME 'authentication_kerberos.so';
Query OK, 0 rows affected (0.0181 sec)
MySQL localhost:3306 ssl SQL > SET PERSIST authentication_kerberos_service_principal='mysql/IP-C6130110.tessellpoc.com@TESSELLPOC.COM';
Query OK, 0 rows affected (0.0029 sec)
Check Plugin status
Copied to clipboard!
MySQL localhost:3306 ssl SQL > 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.0008 sec)
MySQL localhost:3306 ssl SQL >
Create MySQL User with same user name as AD user as below. You can repeat this for
Copied to clipboard!
MySQL localhost:3306 ssl SQL > CREATE USER mysqladuser IDENTIFIED WITH 'authentication_kerberos' BY 'TESSELLPOC.COM';
Query OK, 0 rows affected (0.0068 sec)
Map MySQL SPN to AD user for authentication
In Windows AD, create a keytab file for AD user and map it with MySQL Kerberos SPN (Service Principal Name). Only one user can be mapped to SPN. Typically this user should be MySQL master/admin user or AD Admin user.
Copy Keytab file to MySQL Server directory as mentioned in the MySQL variable. This will validate SPN registered in MySQL variable with the AD Server.
Copied to clipboard!
MySQL localhost:3306 ssl SQL > show global variables like '%key%tab%';
+-----------------------------------------+-------------------------------+
| Variable_name | Value |
+-----------------------------------------+-------------------------------+
| authentication_kerberos_service_key_tab | /mysql/data/data/mysql.keytab |
+-----------------------------------------+-------------------------------+
Ensure permissions and owner for mysql.keytab file
Get TGT tickets using kinit and klist in Linux Server.
Copied to clipboard!
[ec2-user@ip-10-10-60-217 ~]$ kinit mysqladuser@TESSELLPOC.COM -V
Using default cache: 1000
Using principal: mysqladuser@TESSELLPOC.COM
Password for mysqladuser@TESSELLPOC.COM:
Authenticated to Kerberos v5
Validate if TGT ticket is available and created
Copied to clipboard!
[ec2-user@ip-10-10-60-217 ~]$ klist -A
Ticket cache: KCM:1000
Default principal: mysqladuser@TESSELLPOC.COM
Valid starting Expires Service principal
01/23/24 11:34:01 01/23/24 21:34:01 krbtgt/TESSELLPOC.COM@TESSELLPOC.COM
renew until 01/30/24 11:33:56
[ec2-user@ip-10-10-60-217 ~]$
Use MySQL Client to authenticate. Here we are using the MySQL client which resides in the MySQL Server.
Copied to clipboard!
[ec2-user@ip-10-10-60-217 ~]$ mysql -h mysqladdocumentation-aekjt.cs.tessell-stage.com -u mysqladuser --default-auth='authentication_kerberos_client' --plugin-dir='/mysqlbin/lib/plugin' --password
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 437
Server version: 8.0.34 MySQL Community Server - GPL
Copyright (c) 2000, 2023, 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> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.01 sec)
mysql>
Login success with Kerberos based AD authentication.
For use with client different with Server host, follow same steps for enabling Kerberos client in Linux.
We can now successfully integrate AD authentication leveraging advanced security features and ease of user credential management using readily available plugins for MySQL Enterprise edition.
