MySQL

Microsoft Active Directory Authentication using LDAP for MySQL Enterprise

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

Introduction

MySQL Enterprise Edition supports an authentication method that enables MySQL Server to use LDAP (Lightweight Directory Access Protocol) to authenticate MySQL users by accessing directory services such as X.500. MySQL uses LDAP to fetch user, credential, and group information.

The same user names, passwords and permissions can be used to enhance the security by leveraging existing AD rules for strong password enforcement, password expiry, etc.

LDAP authentication enables MySQL Server to accept connections from users defined outside the MySQL grant tables in LDAP directories.

Integrate existing AD to MySQL Enterprise hosted in Linux in 2 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 LDAP port: 389
  • ~ 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/Install LDAP Client Libraries in Linux OS

  • Install openldap Client Libraries in MySQL Server.
Copied to clipboard!

[ec2-user@ip-10-10-60-217 ~]$ sudo yum install openldap-clients.x86_64
 
  • 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 is successful to DC (Port: 389) 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
 
  • Test ldap authentication by using ldapsearch to AD Server.
  • ~ ldapsearch using SIMPLE authentication. For example below we have user john.smith@tessellpoc.com registered in AD
Copied to clipboard!

[ec2-user@ip-10-10-54-42 ~]$ ldapsearch -x  -h 172.31.19.107 -D "cn=john smith, ou=users, ou=tessell, dc=tessellpoc, dc=com" -b "cn=john smith, ou=users, ou=tessell, dc=tessellpoc, dc=com" -W
Enter LDAP Password:
# extended LDIF
#
# LDAPv3
# base cn=john smith, ou=users, ou=tessell, dc=tessellpoc, dc=com with scope subtree
# filter: (objectclass=*)
# requesting: ALL
#

# john smith, Users, tessell, tessellpoc.com
dn: CN=john smith,OU=Users,OU=tessell,DC=tessellpoc,DC=com
objectClass: top
objectClass: person
objectClass: organizationalPerson
objectClass: user
cn: john smith
sn: smith
givenName: john
distinguishedName: CN=john smith,OU=Users,OU=tessell,DC=tessellpoc,DC=com
instanceType: 4
whenCreated: 20240131120639.0Z
whenChanged: 20240131121658.0Z
displayName: john smith
uSNCreated: 59907
uSNChanged: 59953
name: john smith
objectGUID:: 4s0CMuyRo0ecQ6MvPpvGPQ==
userAccountControl: 512
badPwdCount: 0
codePage: 0
countryCode: 0
badPasswordTime: 133518346365243210
lastLogon: 133518346452412448
pwdLastSet: 133511763990930643
primaryGroupID: 513
objectSid:: AQUAAAAAAAUVAAAAbQoP3GssrQfjyE8lXwYAAA==
accountExpires: 9223372036854775807
logonCount: 0
sAMAccountName: john.smith
sAMAccountType: 805306368
userPrincipalName: john.smith@tessellpoc.com
objectCategory: CN=Person,CN=Schema,CN=Configuration,DC=tessellpoc,DC=com
dSCorePropagationData: 16010101000000.0Z
lastLogonTimestamp: 133511770189880265

# search result
search: 2
result: 0 Success

# numResponses: 2
# numEntries: 1
 

Enable LDAP Server Side plugin in MySQL Enterprise

  • MySQL supports two types of authentication methods using LDAP - LDAP Simple and LDAP SASL. We will demonstrate the use of LDAP Simple method due to its compatibility with Microsoft AD Server.  
  • Server side plugin file authentication_ldap_simple.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_ldap_simple.so
authentication_ldap_simple_server_host="IP-C6130110.tessellpoc.com"
authentication_ldap_simple_bind_base_dn="OU=Users,OU=tessell,DC=tessellpoc,DC=com"
 
  • Alternatively we can load the plugin at runtime using below steps.
Copied to clipboard!

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 23
Server version: 8.0.33-commercial MySQL Enterprise Server - Commercial
No default schema selected; type \use schema to set one.
 MySQL  localhost:3306 ssl  SQL > INSTALL PLUGIN authentication_ldap_simple SONAME 'authentication_ldap_simple.so';
Query OK, 0 rows affected (0.0047 sec)
 MySQL  localhost:3306 ssl  SQL > SET PERSIST authentication_ldap_simple_server_host="IP-C6130110.tessellpoc.com";
Query OK, 0 rows affected (0.0024 sec)
 MySQL  localhost:3306 ssl  SQL > SET PERSIST authentication_ldap_simple_bind_base_dn="OU=Users,OU=tessell,DC=tessellpoc,DC=com" ;
Query OK, 0 rows affected (0.0026 sec)
 MySQL  localhost:3306 ssl  SQL >
 
  • Check Plugin status
Copied to clipboard!

MySQL  localhost:3306 ssl  SQL > SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%ldap%';
+----------------------------+---------------+
| PLUGIN_NAME                | PLUGIN_STATUS |
+----------------------------+---------------+
| authentication_ldap_simple | ACTIVE        |
+----------------------------+---------------+
1 row in set (0.0008 sec)
 
  • Create MySQL User with same user name as AD user as below. You can repeat this for all the users to be added for accessing databases.
  • ~ The Distinguished Name suffix you can obtain from AD Server using ldapsearch (example given above).
  • ~ For john.smith@tessellpoc.com we have DN as  CN=john smith,OU=Users,OU=tessell,DC=tessellpoc,DC=com
Copied to clipboard!

MySQL  localhost:3306 ssl  SQL > CREATE USER 'john.smith' IDENTIFIED WITH authentication_ldap_simple AS 'CN=john smith,OU=Users,OU=tessell,DC=tessellpoc,DC=com';
Query OK, 0 rows affected (0.0108 sec)
 
  • Use MySQL Client to authenticate. Here we are using the MySQL client which resides in the MySQL Server.
  • ~ For LDAP simple authentication configured with the use of  server-side authentication_ldap_simple plugin, invoke client programs (mysql client) with the --enable-cleartext-plugin option to enable the client-side mysql_clear_password plugin.  For example below:
Copied to clipboard!

[ec2-user@ip-10-10-54-42 ~]$ mysql -u 'john.smith' -p --enable-cleartext-plugin --protocol=tcp
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 49
Server version: 8.0.33-commercial MySQL Enterprise Server - Commercial

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>
 

We can now successfully integrate AD authentication using LDAP 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 :: 6.4.1.7 LDAP Pluggable Authentication

Follow us
Youtube Button