How to setup Active Directory Authentication in MySQL running on Linux

This document was written using Microsoft Windows Active Directory 2012, Mysql 5.6 and CentOS 6.5. MySQL is running on CentOS 6.5. This document describes how to map an Active Directory Group to a MySQL User and authenticate against Active Directory. The completion of these steps allows Active Directory groups to be utilized within MySQL.

In this demonstration the following groups will be referenced.

mySQL_AD – This is the MySQL user which will be mapped/associated to the Active Directory Group.

AD_mysql_users – This is the Active Directory user which will be mapped to mySQL_AD.

STEP 1. Prior to getting started you need the following pam module installed.


STEP 2. After installing the module update the my.cnf file, my.cnf is normally located in /etc/my.cnf


STEP 3. Restart mysql and verify that the PAM modules is installed.

service mysql restart
mysql -u root
select * from information_schema.PLUGINS\G

The following output should be returned from the PLUGIN query

*************************** 47. row ***************************
           PLUGIN_NAME: authentication_pam
        PLUGIN_VERSION: 1.0
         PLUGIN_AUTHOR: Georgi Kodinov
    PLUGIN_DESCRIPTION: PAM authentication plugin
           LOAD_OPTION: ON
47 rows in set (0.00 sec)

Create the following file: vim /etc/pam.d/mysql with the content below. This assumes you already have the MySQL Linux system joined to the Microsoft Active Directory Domain. The is a PAM module that can authenticate users against the local domain by talking to the Winbind daemon.

auth   required
account required

STEP 5. Create a MySQL user with a password. The password is not important because it will never be used.
mySQL_AD is the MySQL user which will be mapped to the Active Directory group.


STEP 6. Run the CREATE USER statement to add the MySQL user to Active Directory mapping. In this example the Active Directory group AD_mysql_users is being mapped to the MySQL user: mySQL_AD
The “mysql” in front of “AD_mysql_users=mySQL_AD” of should match the name of pam.d file e.g. (/etc/pamd./mysql)

IDENTIFIED WITH authentication_pam
AS 'mysql, AD_mysql_users=mySQL_AD';

STEP 7. Grant mySQL_AD PROXY access. This gives users in Active Directory the ability to impersonate the user: mySQL_AD. To run this command you must be logged in as MySQL root: (e.g. mysql -u root -p). Even if you are logged in with DBA access this step will not work, must be logged in under MySQL root user. I tired logging into MySQL with a user which had equivalent privileges to MySQL root and the GRANT failed. Only when I logged in with MySQL root did the GRANT succeed.

mysql -u root
GRANT PROXY ON 'mySQL_AD'@'%' TO ''@'';

Do a SELECT on the table and see which MySQL users are mapped to Active Directory Groups

SELECT * FROM mysql.user;

Fig 1.
MySQL Workbench_2015-03-17_19-46-36

Set the appropriate user access on the MySQL server for the MySQL user mySQL_AD. For example, add the appropriate permissions for the user to do a select on a MySQL database. Remember that mySQL_AD is the MySQL user which we created earlier and is mapped to the Active Directory Group AD_mysql_users.

STEP 10. Flush security privileges, this causes the user settings to become active

flush privileges;

You can then perform a test connection.
You may receive the message below when performing a test connection.

[root@MYSQL01 log]# mysql -u testuser -p
Enter password:
ERROR 2059 (HY000): Authentication plugin 'mysql_clear_password' cannot be loaded: plugin not enabled

Set the flag to allow cleartext.


If you are connecting from MySQL workbench edit your connection, select “Advanced” then “Enable Clear text Authentication Plugin”. Log into MySQL using your Active Directory Username / Password. You can verify which AD user and MySQL user you are logged in as. In Fig 2 I am authenticated as user testuser mapped to the MySQL user mySQL_AD. Note if you enable clear text authentication plugin it is recommended that you connect to MySQL using SSL via workbench.


Fig 2
MySQL Workbench_2015-03-18_19-57-35

The configuration below were used in support of the above configurations.

Additional configuration information in /etc/samba/smb.conf


        workgroup = LAB
        server string = Samba Server Version %v
        realm   =       LAB.NET
        netbios name = MYSQL01
        encrypt passwords = yes
        password server =
        #idmap config * : backend = rid
        #idmap config * : range = 10000-20000
        idmap config * : range = 16777216-33554431
        winbind use default domain = Yes
        winbind enum users = Yes
        winbind enum groups = Yes
        winbind nested groups = Yes
        winbind separator = +
        winbind refresh tickets = yes
        winbind offline logon = true
        template shell = /bin/bash
        template homedir = /home/%D/%U
        preferred master = no
        dns proxy = no
        wins server =
        wins proxy = no
        inherit acls = Yes
        map acl inherit = Yes
        acl group control = yes
        security = ads
        passdb backend = tdbsam

Additional configuration information in /etc/krb5.conf

 default = FILE:/var/log/krb5libs.log
 kdc = FILE:/var/log/krb5kdc.log
 admin_server = FILE:/var/log/kadmind.log

 default_realm = LAB.NET
 dns_lookup_realm = true
 dns_lookup_kdc = true
 ticket_lifetime = 24h
 renew_lifetime = 7d
 forwardable = true
 proxiable = true

 LAB.NET = {
  default_domain = LAB.NET
  kdc = DC01.LAB.NET
  admin_server = DC01.LAB.NET

[domain_realm] = LAB.NET = LAB.NET
 lab = LAB.NET

        pam = {
        ticket_lifetime         = 1d
        renew_lifetime          = 1d
        forwardable             = true
        proxiable               = true
        retain_after_close      = false
        minimum_uid             = 0
        debug                   = true

Additional configuration information in /etc/nsswitch.conf

passwd:     files sss ldap winbind
shadow:     files sss ldap winbind
group:      files sss ldap winbind

Additional configuration information in /etc/pam.d/system-auth

auth        required
auth        sufficient
auth        sufficient nullok try_first_pass
auth        sufficient use_first_pass
auth        sufficient cached_login use_first_pass
auth        requisite uid >= 500 quiet
auth        required

account     required
account     sufficient
account     sufficient uid < 500 quiet
account     [default=bad success=ok user_unknown=ignore]
account     [default=bad success=ok user_unknown=ignore] cached_login
account     required

password    requisite try_first_pass retry=3 type=
password    sufficient sha512 shadow nullok try_first_pass use_authtok
password    sufficient use_authtok
password    sufficient cached_login use_authtok
password    required

session     optional revoke
session     required
session     optional skel=/etc/skel umask=0027
session     [success=1 default=ignore] service in crond quiet use_uid
session     required
session     optional

Reference :


One thought on “How to setup Active Directory Authentication in MySQL running on Linux

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s