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:
- 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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
And there you have it! I hope you found this useful and thank you again for reading.
- Kyle H.