Tech Blog

Someone recently asked me if it was possible to log into MySQL running on Linux using an active directory account.  "Interesting idea", I thought to myself so I set out to see if I could make it work.  Turns out it wasn't all that hard to do.  Time for the usual disclaimer.  I have only tested this in my home lab but I know of no reason why this wouldn't work for you.  With that said, let's dig in!

The key to making this work is the System Security Services Daemon (SSSD).  According to the SSSD wiki (https://fedoraproject.org/wiki/Features/SSSD), "This project provides a set of daemons to manage access to remote directories and authentication mechanisms, it provides an NSS and PAM interface toward the system and a pluggable backend system to connect to multiple different account sources."  I have used SSSD for years to authenticate RHEL/CentOS systems against active directory.  I think of it as a more modern way of doing it rather than setting everything up individually with Samba, LDAP, Kerberos, and PAM.  It provides a few added features and keeps most options in a single file (/etc/sssd/sssd.conf).  The information below talks about implementing this solution with Active Directory but you could also use SSSD and LDAP to accomplish the same thing.

To get started, let's install MariaDB server (the MySQL fork that now comes with CentOS) and SSSD as follows and note I am using a freshly built, minimal install of CentOS 7 that is fully patched as of this writing.  The setup should be very similar on other flavors of Linux:
# yum -y install sssd mariadb-server

That will run for a bit installing the packages and all their dependencies.  This is probably a good time to think about what all we need to have in place for this to work with active directory.  Here are a few notes to get you started:

  • Active directory must be set up and working
  • DNS must be working properly as active directory is very dependant on it
  • Make sure you don't have any firewalls blocking the necessary traffic
  • You will need to make sure you can ping the domain name by name (in my case it is theharrishome.lan) and get a response as well as the active directory domain controllers.  This usually entails pointing your Linux machine at an active directory domain controller for proper authentication as there are a lot of different types of records that must be set up correctly.
  • The 3 files config files for controlling the services SSSD uses including the SSSD config file itself must be set up correctly.  They are:
    • /etc/krb5.conf #{the Kerberos config file}
    • /etc/samba/smb.conf #{the Samba config file}
    • /etc/sssd/sssd.conf #{the SSSD config file}
  • Permissions on the SSSD config file must be set correctly which can be accomplished with the following command:
    • # chmod 600 /etc/sssd/sssd.conf
  • I'm sure there is much more I'm leaving out but hopefully this list will help

As mentioned above, the domain in my lab is theharrishome.lan.  Here are the 3 pertinent config files for my setup:

Kerberos config (/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 = THEHARRISHOME.LAN
dns_lookup_realm = true
dns_lookup_kdc = true
ticket_lifetime = 24h
renew_lifetime = 7d
rdns = false
forwardable = yes

[realms]

[domain_realm]

Note above that the domain name is in all capital letters.  This is important.

Samba config (/etc/samba/smb.conf)

[global]
   workgroup = THEHARRISHOME
   client signing = yes
   client use spnego = yes
   kerberos method = secrets and keytab
   log file = /var/log/samba/%m.log
   password server = *
   realm = THEHARRISHOME.LAN
   security = ads

Again note that the domain is in all capital letters.

SSSD config (/etc/sssd/sssd.conf)

[sssd]
config_file_version = 2
domains = theharrishome.lan
services = nss, pam
debug_level = 0

[nss]

[pam]

[domain/theharrishome.lan]
debug_level = 0

enumerate = false
id_provider = ldap
auth_provider = krb5
chpass_provider = krb5
access_provider = ldap
ldap_idmap_range_size = 800000
ldap_idmap_range_min = 200000
ldap_idmap_range_max = 2000200000

ldap_id_mapping = true
# ldap_access_filter = (memberOf=cn=LinuxAdmins,ou=Groups,dc=theharrishome,dc=lan)
default_shell = /bin/bash
fallback_homedir = /home/%u
ldap_access_order = filter

ldap_sasl_mech = GSSAPI
ldap_schema = ad
# ldap_user_search_base = dc=theharrishome,dc=lan
# ldap_group_search_base = ou=Groups,dc=theharrishome,dc=lan
ldap_user_object_class = user
ldap_user_home_directory = unixHomeDirectory
ldap_user_principal = nosuchattribute
ldap_group_object_class = group
ldap_access_order = expire
ldap_account_expire_policy = ad
ldap_force_upper_case_realm = true
krb5_realm = THEHARRISHOME.LAN
krb5_canonicalize = false

# systemctl stop sssd;rm -f /var/log/sssd/*;rm -f /var/lib/sss/db/*;systemctl start sssd

Once again, pay attention to case as it does matter.  As you can see from the SSSD config file, there are many options available and I am by no means showing them all.  SSSD is very powerful and hopefully this is just enough to get you started by substituting your domain name for mine.  I will touch on a few of the items in the config that I have commented out so you will at least know the option is available if you wish to use it.

  • ldap_access_filter - You can use this setting to filter who can access this system via SSSD
  • ldap_user_search_base - Use this to narrow down where SSSD looks for user within active directory
  • ldap_group_search_base - Use this to narrow down where SSSD looks for groups within active directory
  • The last commented line in the config is something I use often when making a change to the SSSD config file.  It stops the daemon, removes the logs and some internal cached settings, and then restarts it.  It doesn't actually belong in the config file but I just keep it there for easy copy/paste access.

Next we need to make sure we have the correct permissions set on the SSSD config file of /etc/sssd/sssd.conf as mentioned above.  We do that as follows:
# chmod 600 /etc/sssd/sssd.conf

Now we should be able to start the SSSD and enable it to run at startup:
# systemctl start sssd;systemctl enable sssd

Hopefully you won't receive any errors but if you do, you can always change the debug_level to say 9 in the two places within the SSSD config file and check the logs found under /var/log/sssd.

Next we need to enable SSSD for logins as follows:
authconfig --enablesssd --enablesssdauth --enablemkhomedir --update

Now we are ready to join the Linux system to the domain.  The following command should take care of that and note the upper case "A" in the built-in active directory Administrator account:
net ads join –U Administrator

Here is what it looks like for me and note the DNS errors are not an issue at this point:

sssd domain join

If we are successful, we should be able to issue the command # id Administrator (note the upper case "A" again) and get something like the following:

sssd id

Looking good.  Now you should be able to log into the system using the Administrator account.  You will not have sudo access unless you add the Administrator account to sudoers just as you normally would.  Once you have verified that you can successfully log in as the Administrator, we can move on to MySQL.

Let's change directories to /etc/pam.d with # cd /etc/pam.d and then list the contents with # ls -lha.  Here is what mine looks like: 

sssd ls

Notice that "system-auth" is really just a link to the file "system-auth-ac".  That file contains information that SSSD uses for system authentication.  Let's just copy that file to a new file named mysql as follows.  By default, the auth_pam plugin will make use of this file:
# cp system-auth-ac mysql

Now let's fire up MariaDB and also set it to start on boot:
# systemctl start mariadb;systemctl enable mariadb

We should now be able to log into MySQL by issueing the command # mysql and you should see the following:
sssd mysql

Now from within MySQL we should be able to issue the command > SHOW PLUGINS; and get something like the following and note the semi-colon at the end of the command: 

sssd mysql plugins

The plugin we need is named "auth_pam" and you may notice that it is not loaded by default.  We need to load it with the command > INSTALL SONAME 'auth_pam'; which looks like this:

sssd mysql plugin load

If by chance the plugin is already loaded, you may notice an error of "ERROR 1127 (HY000): Can't find symbol '(null)' in library" as shown below when issuing the command to install it, which I find to be a bit confusing:

sssd mysql plugin load error

Now let's list the loaded plugins again with the command > SHOW PLUGINS; and we should now see that auth_pam.so is now loaded as shown in the last line:

sssd mysql plugins with pam

The last step is to grant the active directory user the permissions you wish for them to have within MySQL while also telling MySQL how the user should gain access.  For this example, I chose to give the active directory user Administrator the select permission on the localhost with the command > GRANT SELECT ON *.* TO 'Administrator'@'localhost' IDENTIFIED VIA pam; which looks like this:

sssd mysql grant

Now you can exit MySQL by typing exit followed by hitting the enter key.  Try to log on using the active directory Administrator account via the command # mysql -u Administrator -p which should then prompt you for the active directory Administrator account password and with luck, you will be logged in as shown:

sssd mysql login

And there you have it!  I hope you found this useful and thank you again for reading.

- Kyle H.