Setting Up Percona PAM with Active Directory for External Authentication

In this blog post, we’ll look at how to set up Percona PAM with Active Directory for external authentication.

In my previous article on Percona PAM, I demonstrated how to use Samba as a domain, and how easy it is to create domain users and groups via the samba-tool. Then we configured nss-pam-ldapd and nscd to enumerate user and group information via LDAP calls, and authenticate users from this source.

This time around, I will demonstrate two other ways of using Active Directory for external authentication by joining the domain via SSSD or Winbind. System Security Services Daemon (SSSD) allows you to configure access to several authentication hosts such as LDAP, Kerberos, Samba and Active Directory and have your system use this service for all types of lookups. Winbind, on the other hand, pulls data from Samba or Active Directory only. If you’re mulling over using SSSD or Winbind, take a look at this article on what SSSD or Winbind support.

For both methods, we’ll use realmd. That makes it easy to join a domain and enumerate users from it.

My testbed environment consists of two machines:

Samba PDC
OS: CentOS 7
IP Address: 172.16.0.10
Hostname: samba-10.example.com
Domain name: EXAMPLE.COM
DNS: 8.8.8.8(Google DNS), 8.8.4.4(Google DNS), 172.16.0.10(Samba)
Firewall: none

Note: Please follow the steps in the last article for setting up the Samba PDC environment.

Percona Server 5.7 with LDAP authentication via SSS or WinBind
OS: CentOS 7
IP Address: 172.16.0.21
Hostname: ps-ldap-21.example.com
DNS: 172.16.0.10(Samba PDC)

Installing realmd and Its Dependencies

1. 1. First, we need to make sure that the time is in sync (since this is a requirement for joining domains). Install NTP and make sure that it starts up at boot time:
      
      Shell

      [root@ps-ldap-21 ~]# yum -y install ntp * * * Installed: ntp.x86_64 0:4.2.6p5-25.el7.centos.2 * * * [root@ps-ldap-21 ~]# ntpdate 0.centos.pool.ntp.org systemctl enable ntpd.service systemc 3 Jul 03:48:35 ntpdate[3708]: step time server 202.90.132.242 offset 1.024550 sec [root@ps-ldap-21 ~]# systemctl enable ntpd.service Created symlink from /etc/systemd/system/multi-user.target.wants/ntpd.service to /usr/lib/systemd/system/ntpd.service. [root@ps-ldap-21 ~]# systemctl start ntpd.service

      1 2 3 4 5 6 7 8 9 10 11

      [root@ps-ldap-21 ~]# yum -y install ntp * * * Installed: ntp.x86_64 0:4.2.6p5-25.el7.centos.2 * * * [root@ps-ldap-21 ~]# ntpdate 0.centos.pool.ntp.org systemctl enable ntpd.service systemc 3 Jul 03:48:35 ntpdate[3708]: step time server 202.90.132.242 offset 1.024550 sec [root@ps-ldap-21 ~]# systemctl enable ntpd.service Created symlink from /etc/systemd/system/multi-user.target.wants/ntpd.service to /usr/lib/systemd/system/ntpd.service. [root@ps-ldap-21 ~]# systemctl start ntpd.service

1. 1. Install realmd and its dependencies for SSSD or Winbind.
      For SSSD:
      
      Shell

      yum -y install realmd oddjob oddjob-mkhomedir sssd adcli samba-common-tools

      1	yum -y install realmd oddjob oddjob-mkhomedir sssd adcli samba-common-tools

      
      For Winbind:
      
      Shell

      yum -y install realmd oddjob oddjob-mkhomedir samba-winbind-clients samba-winbind samba-common-tools

      1	yum -y install realmd oddjob oddjob-mkhomedir samba-winbind-clients samba-winbind samba-common-tools

Joining the Domain via SSSD and Preparing It for Percona PAM

1. 1. Run realm discover domain for realmd to discover what type of server it’s connecting to and what packages dependencies need to be installed:
      
      Shell

      [root@ps-ldap-21 ~]# realm discover example.com example.com type: kerberos realm-name: EXAMPLE.COM domain-name: example.com configured: no server-software: active-directory client-software: sssd required-package: oddjob required-package: oddjob-mkhomedir required-package: sssd required-package: adcli required-package: samba-common-tools

      1 2 3 4 5 6 7 8 9 10 11 12 13

      [root@ps-ldap-21 ~]# realm discover example.com example.com type: kerberos realm-name: EXAMPLE.COM domain-name: example.com configured: no server-software: active-directory client-software: sssd required-package: oddjob required-package: oddjob-mkhomedir required-package: sssd required-package: adcli required-package: samba-common-tools

      
      Our Samba PDC is detected as an Active Directory Controller, and the packages required have been installed previously.

1. 1. The next step is to join the domain by running realm join domain. If you want to get more information, add the --verbose option. You could also add the -U user option if you want to use a different administrator account.
      
      Shell

      [root@ps-ldap-21 ~]# realm join example.com --verbose  * Resolving: _ldap._tcp.example.com  * Performing LDAP DSE lookup on: 172.16.0.10  * Successfully discovered: example.com Password for Administrator:  * Required files: /usr/sbin/oddjobd, /usr/libexec/oddjob/mkhomedir, /usr/sbin/sssd, /usr/bin/net  * LANG=C LOGNAME=root /usr/bin/net -s /var/cache/realmd/realmd-smb-conf.DM6W2Y -U Administrator ads join example.com Enter Administrator's password: Using short domain name -- EXAMPLE Joined 'PS-LDAP-21' to dns domain 'example.com'  * LANG=C LOGNAME=root /usr/bin/net -s /var/cache/realmd/realmd-smb-conf.DM6W2Y -U Administrator ads keytab create Enter Administrator's password:  * /usr/bin/systemctl enable sssd.service Created symlink from /etc/systemd/system/multi-user.target.wants/sssd.service to /usr/lib/systemd/system/sssd.service.  * /usr/bin/systemctl restart sssd.service  * /usr/bin/sh -c /usr/sbin/authconfig --update --enablesssd --enablesssdauth --enablemkhomedir --nostart && /usr/bin/systemctl enable oddjobd.service && /usr/bin/systemctl start oddjobd.service  * Successfully enrolled machine in realm

      1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

      [root@ps-ldap-21 ~]# realm join example.com --verbose  * Resolving: _ldap._tcp.example.com  * Performing LDAP DSE lookup on: 172.16.0.10  * Successfully discovered: example.com Password for Administrator:  * Required files: /usr/sbin/oddjobd, /usr/libexec/oddjob/mkhomedir, /usr/sbin/sssd, /usr/bin/net  * LANG=C LOGNAME=root /usr/bin/net -s /var/cache/realmd/realmd-smb-conf.DM6W2Y -U Administrator ads join example.com Enter Administrator's password: Using short domain name -- EXAMPLE Joined 'PS-LDAP-21' to dns domain 'example.com'  * LANG=C LOGNAME=root /usr/bin/net -s /var/cache/realmd/realmd-smb-conf.DM6W2Y -U Administrator ads keytab create Enter Administrator's password:  * /usr/bin/systemctl enable sssd.service Created symlink from /etc/systemd/system/multi-user.target.wants/sssd.service to /usr/lib/systemd/system/sssd.service.  * /usr/bin/systemctl restart sssd.service  * /usr/bin/sh -c /usr/sbin/authconfig --update --enablesssd --enablesssdauth --enablemkhomedir --nostart && /usr/bin/systemctl enable oddjobd.service && /usr/bin/systemctl start oddjobd.service  * Successfully enrolled machine in realm

      
      As you can see from the command above, the realm command simplifies SSSD configuration and uses existing tools such as net and authconfig to join the domain and use it as an identity provider.

1. 1. Let’s test if we enumerate existing accounts by using the id command:
      
      Shell

      [root@ps-ldap-21 ~]# id jervin id: jervin: no such user [root@ps-ldap-21 ~]# id jervin@example.com uid=343401115(jervin@example.com) gid=343400513(domain users@example.com) groups=343400513(domain users@example.com),343401103(support@example.com)

      1 2 3 4

      [root@ps-ldap-21 ~]# id jervin id: jervin: no such user [root@ps-ldap-21 ~]# id [email protected] uid=343401115(jervin@example.com) gid=343400513(domain users@example.com) groups=343400513(domain users@example.com),343401103(support@example.com)

      
      As you can see, the user can be queried if the domain is specified. So if you want to log in as ‘[email protected]’, in Percona Server for MySQL you’ll need to create the user as ‘[email protected]’ and not ‘jervin’. For example:
      
      Shell

      # Creating user 'jervin@example.com' CREATE USER 'jervin@example.com'@'%' IDENTIFIED WITH auth_pam; # Logging in as 'jervin@example.com' mysql -u 'jervin@example.com'

      1 2 3 4

      # Creating user '[email protected]' CREATE USER '[email protected]'@'%' IDENTIFIED WITH auth_pam; # Logging in as '[email protected]' mysql -u '[email protected]'

      
      If you want to omit the domain name when logging in, you’ll need to replace “use_fully_qualified_names = True” to “use_fully_qualified_names = False” in /etc/sssd/sssd.conf, and then restart SSSD. If you do this, then the user can be found without providing the domain:
      
      Shell

      [root@ps-ldap-21 ~]# id jervin uid=343401115(jervin) gid=343400513(domain users) groups=343400513(domain users),343401103(support) [root@ps-ldap-21 ~]# id jervin@example.com uid=343401115(jervin) gid=343400513(domain users) groups=343400513(domain users),343401103(support)

      1 2 3 4

      [root@ps-ldap-21 ~]# id jervin uid=343401115(jervin) gid=343400513(domain users) groups=343400513(domain users),343401103(support) [root@ps-ldap-21 ~]# id [email protected] uid=343401115(jervin) gid=343400513(domain users) groups=343400513(domain users),343401103(support)

      
      When you create the MySQL user, you don’t need to include the domain anymore:
      
      Shell

      # Creating user 'jervin' CREATE USER 'jervin'@'%' IDENTIFIED WITH auth_pam; # Logging in as 'jervin' mysql -u jervin

      1 2 3 4

      # Creating user 'jervin' CREATE USER 'jervin'@'%' IDENTIFIED WITH auth_pam; # Logging in as 'jervin' mysql -u jervin

1. 1. Optionally, you can specify which users and groups can log in by adding these settings to SSSD:
      Domain access filter
      Under “[domain/example.com]” /etc/sssd/sssd.conf, you can add the following to specify that only users that are members of support and dba are allowed to use SSSD. For example:
      
      Shell

      ad_access_filter = (|(memberOf=CN=dba,CN=Users,DC=example,DC=com)(memberOf=CN=support,CN=Users,DC=example,DC=com))

      1	ad_access_filter = (|(memberOf=CN=dba,CN=Users,DC=example,DC=com)(memberOf=CN=support,CN=Users,DC=example,DC=com))

      
      Simple filters
      You can use realm permit or realm permit -g to allow particular users or groups. For example:
      
      Shell

      realm permit jervin realm permit -g support realm permit -g dba

      1 2 3

      realm permit jervin realm permit -g support realm permit -g dba

      
      You can check sssd.conf on how these ACLs are implemented:
      
      Shell

      access_provider = simple simple_allow_groups = support, dba simple_allow_users = jervin

      1 2 3

      access_provider = simple simple_allow_groups = support, dba simple_allow_users = jervin

1. 1. Finally, configure Percona Server for MySQL to authenticate to SSSD by creating /etc/pam.d/mysqld with this content:
      
      Shell

      auth required pam_sss.so account required pam_sss.so

      1 2	auth required pam_sss.so account required pam_sss.so

1. 1. Done. All you need to do now is to install Percona Server for MySQL, enable the auth_pam and auth_pam_compat plugins, and add PAM users. You can then check for authentication errors at /var/log/secure for troubleshooting. You could also get verbose logs by adding debug_level=[1-9] to [nss], [pam], or [domain] and then restarting SSSD. You can view the logs from /var/log/sssd.

Joining the Domain via Winbind and Preparing it for Percona PAM

1. 1. The realm command assumes that SSSD is used. To change the client software, use --client-software=winbind instead:
      
      Shell

      [root@ps-ldap-21 ~]# realm --client-software=winbind discover example.com example.com     type: kerberos     realm-name: EXAMPLE.COM     domain-name: example.com     configured: no       server-software: active-directory     client-software: winbind     required-package: oddjob-mkhomedir     required-package: oddjob     required-package: samba-winbind-clients     required-package: samba-winbind     required-package: samba-common-tools

      1 2 3 4 5 6 7 8 9 10 11 12 13

      [root@ps-ldap-21 ~]# realm --client-software=winbind discover example.com example.com     type: kerberos     realm-name: EXAMPLE.COM     domain-name: example.com     configured: no       server-software: active-directory     client-software: winbind     required-package: oddjob-mkhomedir     required-package: oddjob     required-package: samba-winbind-clients     required-package: samba-winbind     required-package: samba-common-tools

1. 1. Since the required packages have already been installed, we can now attempt to join this host to the domain:
      
      Shell

      [root@ps-ldap-21 ~]# realm --verbose --client-software=winbind join example.com  * Resolving: _ldap._tcp.example.com  * Performing LDAP DSE lookup on: 172.16.0.10  * Successfully discovered: example.com Password for Administrator:  * Required files: /usr/libexec/oddjob/mkhomedir, /usr/sbin/oddjobd, /usr/bin/wbinfo, /usr/sbin/winbindd, /usr/bin/net  * LANG=C LOGNAME=root /usr/bin/net -s /var/cache/realmd/realmd-smb-conf.9YEO2Y -U Administrator ads join example.com Enter Administrator's password: Using short domain name -- EXAMPLE Joined 'PS-LDAP-21' to dns domain 'example.com'  * LANG=C LOGNAME=root /usr/bin/net -s /var/cache/realmd/realmd-smb-conf.9YEO2Y -U Administrator ads keytab create Enter Administrator's password:  * /usr/bin/systemctl enable winbind.service Created symlink from /etc/systemd/system/multi-user.target.wants/winbind.service to /usr/lib/systemd/system/winbind.service.  * /usr/bin/systemctl restart winbind.service  * /usr/bin/sh -c /usr/sbin/authconfig --update --enablewinbind --enablewinbindauth --enablemkhomedir --nostart && /usr/bin/systemctl enable oddjobd.service && /usr/bin/systemctl start oddjobd.service  * Successfully enrolled machine in realm

      1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

      [root@ps-ldap-21 ~]# realm --verbose --client-software=winbind join example.com  * Resolving: _ldap._tcp.example.com  * Performing LDAP DSE lookup on: 172.16.0.10  * Successfully discovered: example.com Password for Administrator:  * Required files: /usr/libexec/oddjob/mkhomedir, /usr/sbin/oddjobd, /usr/bin/wbinfo, /usr/sbin/winbindd, /usr/bin/net  * LANG=C LOGNAME=root /usr/bin/net -s /var/cache/realmd/realmd-smb-conf.9YEO2Y -U Administrator ads join example.com Enter Administrator's password: Using short domain name -- EXAMPLE Joined 'PS-LDAP-21' to dns domain 'example.com'  * LANG=C LOGNAME=root /usr/bin/net -s /var/cache/realmd/realmd-smb-conf.9YEO2Y -U Administrator ads keytab create Enter Administrator's password:  * /usr/bin/systemctl enable winbind.service Created symlink from /etc/systemd/system/multi-user.target.wants/winbind.service to /usr/lib/systemd/system/winbind.service.  * /usr/bin/systemctl restart winbind.service  * /usr/bin/sh -c /usr/sbin/authconfig --update --enablewinbind --enablewinbindauth --enablemkhomedir --nostart && /usr/bin/systemctl enable oddjobd.service && /usr/bin/systemctl start oddjobd.service  * Successfully enrolled machine in realm

1. 1. Let’s test if we enumerate existing accounts by using the id command
      
      Shell

      [root@ps-ldap-21 ~]# id jervin id: jervin: no such user [root@ps-ldap-21 ~]# id jervin@example.com uid=10000(EXAMPLEjervin) gid=10000(EXAMPLEdomain users) groups=10000(EXAMPLEdomain users),10001(EXAMPLEsupport)

      1 2 3 4

      [root@ps-ldap-21 ~]# id jervin id: jervin: no such user [root@ps-ldap-21 ~]# id [email protected] uid=10000(EXAMPLEjervin) gid=10000(EXAMPLEdomain users) groups=10000(EXAMPLEdomain users),10001(EXAMPLEsupport)

      
      Unfortunately for Winbind, users identified with their domains cannot login to Percona Server for MySQL. We need to disable this from the Samba config (performed in the next step).

1. 1. Edit /etc/samba/smb.conf, and change “winbind use default domain = no” to “winbind use default domain = yes”. Restart the Winbind service. For example:
      
      Shell

      vi /etc/samba/smb.conf #Look for: "winbind use default domain = no" #Change to: "winbind use default domain = yes" systemctl restart winbind.service

      1 2 3 4 5 6

      vi /etc/samba/smb.conf #Look for: "winbind use default domain = no" #Change to: "winbind use default domain = yes" systemctl restart winbind.service

      
      Try running id again:
      
      Shell

      [root@ps-ldap-21 ~]# id jervin uid=10000(jervin) gid=10000(domain users) groups=10000(domain users),10001(support) [root@ps-ldap-21 ~]# id jervin@example.com id: jervin@example.com: no such user

      1 2 3 4

      [root@ps-ldap-21 ~]# id jervin uid=10000(jervin) gid=10000(domain users) groups=10000(domain users),10001(support) [root@ps-ldap-21 ~]# id [email protected] id: jervin@example.com: no such user

      
      When you create the MySQL user, do not include the domain name. For example:
      
      Shell

      # Creating user 'jervin' CREATE USER 'jervin'@'%' IDENTIFIED WITH auth_pam; # Logging in as 'jervin' mysql -u jervin

      1 2 3 4

      # Creating user 'jervin' CREATE USER 'jervin'@'%' IDENTIFIED WITH auth_pam; # Logging in as 'jervin' mysql -u jervin

1. 1. Finally, configure Percona Server for MySQL to authenticate to Winbind by creating /etc/pam.d/mysqld with this content:
      
      Shell

      auth required pam_winbind.so account required pam_winbind.so

      1 2	auth required pam_winbind.so account required pam_winbind.so

You can debug authentication attempts by reviewing the logs at /var/log/secure. You may also change “auth required pam_winbind.so” to “auth required pam_winbind.so debug” in /etc/pam.d/mysqld to get verbose logging in the same file.

As for filtering who can authenticate with Winbind, you can add require_membership_of=group_name under the [global] section of /etc/security/pam_winbind.conf

You’ll need to restart winbind daemon to apply the changes.

Conclusion

Thanks to realmd, it’s easier to setup Active Directory as an identity provider. With minimal configuration tweaks, you can use the identity provider to authenticate MySQL users.