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.

/usr/lib64/mysql/plugin/authentication_pam.so

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

[mysqld]
plugin-load=authentication_pam.so

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_STATUS: ACTIVE
           PLUGIN_TYPE: AUTHENTICATION
   PLUGIN_TYPE_VERSION: 1.0
        PLUGIN_LIBRARY: authentication_pam.so
PLUGIN_LIBRARY_VERSION: 1.4
         PLUGIN_AUTHOR: Georgi Kodinov
    PLUGIN_DESCRIPTION: PAM authentication plugin
        PLUGIN_LICENSE: PROPRIETARY
           LOAD_OPTION: ON
47 rows in set (0.00 sec)

STEP 4.
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 pam_winbind.so is a PAM module that can authenticate users against the local domain by talking to the Winbind daemon.

auth   required    pam_winbind.so
account required    pam_winbind.so

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.

CREATE USER 'mySQL_AD'@'%' IDENTIFIED BY 'SomePassw0rd';

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)
AD_mysql_users=mySQL_AD

CREATE USER ''@''
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 ''@'';

STEP 8.
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

STEP 9.
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.

[root@MYSQL01 log]# export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1

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.

SELECT USER(),CURRENT_USER();

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

[global]

        workgroup = LAB
        server string = Samba Server Version %v
        realm   =       LAB.NET
        netbios name = MYSQL01
        encrypt passwords = yes
        password server = dc01.lab.net
        #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 = dc01.lab.net
        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

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

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

[realms]
 LAB.NET = {
  default_domain = LAB.NET
  kdc = DC01.LAB.NET
  admin_server = DC01.LAB.NET
 }

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

[appdefaults]
        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      pam_env.so
auth        sufficient    pam_fprintd.so
auth        sufficient    pam_unix.so nullok try_first_pass
auth        sufficient    pam_krb5.so use_first_pass
auth        sufficient    pam_winbind.so cached_login use_first_pass
auth        requisite     pam_succeed_if.so uid >= 500 quiet
auth        required      pam_deny.so

account     required      pam_unix.so
account     sufficient    pam_localuser.so
account     sufficient    pam_succeed_if.so uid < 500 quiet
account     [default=bad success=ok user_unknown=ignore] pam_krb5.so
account     [default=bad success=ok user_unknown=ignore] pam_winbind.so cached_login
account     required      pam_permit.so

password    requisite     pam_cracklib.so try_first_pass retry=3 type=
password    sufficient    pam_unix.so sha512 shadow nullok try_first_pass use_authtok
password    sufficient    pam_krb5.so use_authtok
password    sufficient    pam_winbind.so cached_login use_authtok
password    required      pam_deny.so

session     optional      pam_keyinit.so revoke
session     required      pam_limits.so
session     optional      pam_mkhomedir.so skel=/etc/skel umask=0027
session     [success=1 default=ignore] pam_succeed_if.so service in crond quiet use_uid
session     required      pam_unix.so
session     optional      pam_krb5.so

Reference : http://dev.mysql.com/doc/refman/5.6/en/proxy-users.html

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s