MySQL

Microsoft Active Directory Authentication using Kerberos for MySQL Enterprise

Priyank Parikh
Priyank Parikh
,
February 1, 2024
Share this blog
arrow icon
Microsoft Active Directory Authentication using Kerberos for MySQL Enterprise

Introduction

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.
  • ~ Windows AD DNS port: 53
  • ~ Windows AD Kerberos port: 88,750
  • ~ MySQL Server port for Client: Default 3306.
  • For this experiment, we used AD domain/forest as tessellpoc.com and user as <user_name>@tessellpoc.com for credentials.

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.
Copied to clipboard!

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

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
Copied to clipboard!

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
Copied to clipboard!

[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.
Copied to clipboard!

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
 

<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.
Copied to clipboard!

[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
 

<p class="info">Format for SPN is &lt;service name&gt;&#x2F;&lt;AD Server&gt;&#x40;&lt;REALM NAME&gt;
</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.
Copied to clipboard!

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.
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
Copied to clipboard!

[ec2-user@ip-10-10-60-217 data]$ sudo chown mysql:mysql mysql.keytab
 
  • 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.  

References:

MySQL :: MySQL 8.0 Reference Manual :: 8.4.1.8 Kerberos Pluggable Authentication

Kerberos: The Network Authentication Protocol

Follow us
Youtube Button