MySQL

Microsoft Active Directory Authentication using Kerberos for MySQL Enterprise

February 1, 2024

7 min read

TL;DR

MySQL Enterprise Edition 8.0.26+ supports Kerberos pluggable authentication via the authentication_kerberos.so plugin for Active Directory integration.
The plugin maps MySQL users to AD accounts using a Service Principal Name and keytab file, supporting both MIT GSSAPI and Microsoft SSPI implementations.
Kerberos authentication lets MySQL inherit AD password policies, single sign-on, and centralized credential management without separate user provisioning.
Setup requires installing krb5 client libraries on the Linux MySQL server, configuring krb5.conf with the AD realm, and generating a keytab file via ktpass on Windows.
Choose MySQL Kerberos authentication when integrating Enterprise Edition with Microsoft Active Directory or Azure AD for unified identity and access management.

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.

Kerberos Server Side plugin used for Microsoft AD integration with MySQL is only available for MySQL Enterprise Edition from 8.0.26.

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.
~ Windows AD DNS port: 53
~ Windows AD Kerberos port: 88,750
~ MySQL Server port for Client: Default 3306.

For example below we have used Microsoft AD implementation from AWS Directory Services.

For creating a new AD in AWS please refer Active Directory – AWS Directory Service – AWS.

Enable Kerberos in Linux and authenticate to Active Directory Server

Install Kerberos Client Libraries in MySQL Server.

[ec2-user@ip-10-10-60-217 ~]$ sudo yum install krb5-workstation
Obtain AD Domain Controller Server IP address by login to AD Server.

PS C:\Users\admin> Get-ADDomainController ComputerObjectDN : CN=IP-C6130110,OU=Domain Controllers,DC=tessellpoc,DC=com DefaultPartition : DC=tessellpoc,DC=com Domain : tessellpoc.com Enabled : True Forest : tessellpoc.com HostName : IP-C6130110.tessellpoc.com InvocationId : 94ec4b1f-b172-47fd-8f84-a7538a2f27ce IPv4Address : 172.31.19.107 IPv6Address : IsGlobalCatalog : True IsReadOnly : False LdapPort : 389 Name : IP-C6130110 NTDSSettingsObjectDN : CN=NTDS Settings,CN=IP-C6130110,CN=Servers,CN=Default-First-Site-Name,CN=Sites,CN=Configuration,DC=tessellpoc,DC=com OperatingSystem : Windows Server 2019 Datacenter OperatingSystemHotfix : OperatingSystemServicePack : OperatingSystemVersion : 10.0 (17763) OperationMasterRoles : {SchemaMaster, DomainNamingMaster, PDCEmulator, RIDMaster...} Partitions : {DC=ForestDnsZones,DC=tessellpoc,DC=com, DC=DomainDnsZones,DC=tessellpoc,DC=com, CN=Schema,CN=Configuration,DC=tessellpoc,DC=com, CN=Configuration,DC=tessellpoc,DC=com...} ServerObjectDN : CN=IP-C6130110,CN=Servers,CN=Default-First-Site-Name,CN=Sites,CN=Configuration,DC=tessellpoc,DC=com ServerObjectGuid : 6ad48e82-e469-49ec-8f53-fd05a7a3eb91 Site : Default-First-Site-Name SslPort : 636 PS C:\Users\admin>
Test whether telnet successful to DC from MySQL Server.
~ Make a manual entry in /etc/hosts file in in MySQL Server if unable to resolve DNS
172.13.19.107 IP-C6130110.tessellpoc.com
Configure Kerberos Client file (krb5.conf) as following.
~ Configure REALM name (in uppercase).
~ default_realm if needed to authenticate with only user name instead of user@REALM
~ default_enctypes, permitted_enctypes as per requirement. We are using AES256_CTS - 256 bit encryption that is compatible in current use case.
~ More info: krb5.conf — MIT Kerberos Documentation

[ec2-user@ip-10-10-60-217 ~]$ more /etc/krb5.conf # To opt out of the system crypto-policies configuration of krb5, remove the # symlink at /etc/krb5.conf.d/crypto-policies which will not be recreated. includedir /etc/krb5.conf.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 = true ticket_lifetime = 24h renew_lifetime = 7d forwardable = true rdns = true pkinit_anchors = FILE:/etc/pki/tls/certs/ca-bundle.crt spake_preauth_groups = edwards25519 default_realm = TESSELLPOC.COM default_ccache_name = KEYRING:persistent:%{uid} default_tgs_enctypes = rc4-hmac des3-hmac-sha1 arcfour-hmac des-hmac-sha1 des-cbc-md5 des-cbc-crc default_tkt_enctypes = rc4-hmac des3-hmac-sha1 arcfour-hmac des-hmac-sha1 des-cbc-md5 des-cbc-crc permitted_enctypes = rc4-hmac des3-hmac-sha1 arcfour-hmac des-hmac-sha1 des-cbc-md5 des-cbc-crc aes256-cts-hmac-sha1-96 aes256-cts-hmac-sha384-192 camellia256-cts-cmac aes128-cts-hmac-sha1-96 aes128-cts-hmac-sha256-128 camellia128-cts-cmac [realms] TESSELLPOC.COM = { kdc = IP-C6130110.tessellpoc.com admin_server = IP-C6130110.tessellpoc.com default_domain = TESSELLPOC.COM } [domain_realm] .tessellpoc.com = TESSELLPOC.COM tessellpoc.com = TESSELLPOC.COM
Create AD user in Windows AD. This user is also going to be used for connecting to MySQL Server. Ignore if user is already created.

PS C:\Users\admin> $splat = @{ >> Name = 'mysqladuser' >> AccountPassword = ( ConvertTo-SecureString -AsPlainText 'R3allyStr0ngPa$$w0rD' -Force) >> EmailAddress = 'mysqladuser@tessellpoc.com' >> PasswordNeverExpires = 1 >> KerberosEncryptionType = 'AES128, AES256' >> ChangePasswordAtLogon = 0 >> Enabled = $true >> } PS C:\Users\admin> New-ADUser @splat

KerberosEncryptionType variable needs to adjusted as per encryption method configured.

Test AD user authentication in Linux server using kinit.

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

[mysqld] plugin-load-add=authentication_kerberos.so authentication_kerberos_service_principal=mysql/IP-C6130110.tessellpoc.com@TESSELLPOC.COM authentication_kerberos_service_key_tab=/mysql/data/data/mysql.keytab

Format for SPN is <service name>/<AD Server>@<REALM NAME>

Alternatively we can load the plugin at runtime using below steps.

[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

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

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.
PS C:\Users\admin> ktpass -out mysql.keytab -princ mysql/IP-C6130110.tessellpoc.com@TESSELLPOC.COM -ptype KRB5_NT_PRINCIPAL -mapuser pguser -pass Tessell@123 /crypto AES256-SHA1 Targeting domain controller: IP-C61302BA.tessellpoc.com Using legacy password setting method Successfully mapped mysql/IP-C6130110.tessellpoc.com to mysql_aduser. Key created. Output keytab to mysql.keytab: Keytab version: 0x502 keysize 98 mysql/IP-C6130110.tessellpoc.com@TESSELLPOC.COM ptype 1 (KRB5_NT_PRINCIPAL) vno 10 etype 0x12 (AES256-SHA1) keylength 32 (0xb381c1832113e68417bd7d70d082e42f068c8eea5ce059a6ec0cb3aad613bbf1) PS C:\Users\admin>
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.

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

[ec2-user@ip-10-10-60-217 data]$ sudo chown mysql:mysql mysql.keytab
Get TGT tickets using kinit and klist in Linux Server.

[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

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

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

‍

FAQs

MySQL Kerberos authentication is an Enterprise Edition method available from MySQL 8.0.26 that lets users authenticate using Kerberos tickets, integrating MySQL with Microsoft Active Directory or Azure AD.

MySQL integrates with Active Directory through the authentication_kerberos.so plugin, mapping a Service Principal Name (SPN) to an AD user via a keytab file generated using ktpass on the Domain Controller.

MySQL Kerberos authentication with Active Directory requires DNS port 53, Kerberos ports 88 and 750, and the MySQL client port (default 3306) open between the Domain Controller and Linux server.

MySQL supports both MIT (GSSAPI) and Microsoft (SSPI) Kerberos implementations, with the Server Side plugin for Microsoft AD integration available only in MySQL Enterprise Edition from version 8.0.26.

The Kerberos plugin is enabled by adding plugin-load-add=authentication_kerberos.so, authentication_kerberos_service_principal, and authentication_kerberos_service_key_tab to my.cnf, or by running INSTALL PLUGIN authentication_kerberos SONAME at runtime.

Related Blogs