Pythian Blog: Technical Track

Authenticating MySQL 8.0 (Enterprise) against active directory

by Matthias Crauwels on Sep 17, 2018 12:00:00 AM

Recently our team was tasked to implement MySQL 8.0 in production for a client. While MySQL 8.0 is looking very promising and has a lot of cool new features and revamped old features, it’s still pretty young. The MySQL development team is working very hard on stabilizing the product but, reading the latest release notes for version 8.0.12, it is still very much a bugfix release and not something you will want to put in production immediately. Also, given the fact that a lot of tools such as Percona Xtrabackup do not support MySQL 8.0 yet, we were a bit reluctant to proceed with this idea. But … the client had a strong use case to make use of roles and resource groups and we like challenges so we decided to give it a go. Another use case that was presented is the one where they wanted to authenticate against Active Directory. Where Active Directory can be used as an LDAP server, we notified the client that in order to use the LDAP pluggable authentication plugin, we would need to use the enterprise version of MySQL which includes this particular plugin. Having this enterprise subscription immediately fixed our backup issue, as this would allow us to use MySQL Enterprise Backup as our primary backup solution. So we got started. And pretty soon we found out that we were not going to be able to use the secure version of this plugin: authentication_ldap_sasl. This plugin only supports the SCRAM-SHA-1 authentication method and this is (currently) not supported by Active Directory. The authentication_ldap_simple plugin does work but this will send the user’s passwords in plaintext to the Active Directory domain controller. This solution got a well-deserved no-go from the client’s security team. Back to the drawing board it was… A few other options, including SSH-tunnels to the domain controller, were evaluated but none of the options were considered to be suitable for use in the production environment. While analyzing the issue I came across another MySQL Enterprise’s pluggable authentication plugins, the PAM pluggable authentication plugin. The manual for this plugin states the following: “MySQL Enterprise Edition supports an authentication method that enables MySQL Server to use PAM (Pluggable Authentication Modules) to authenticate MySQL users. PAM enables a system to use a standard interface to access various kinds of authentication methods, such as Unix passwords or an LDAP directory”. Mmmm.... LDAP… let’s try this.

Join server to Active Directory

In order for this plugin to work, we need to configure the local PAM system to use Active Directory as an authentication source. We are using CentOS 7 for this installation, so a little bit of Googling landed me on this blog post. We were to join our Linux server into the Active Directory domain. (For other Linux distributions, methods to achieve this can be found online, for example, Ubuntu Xenial.) The blog told us to install some packages (the list provided in the blog was longer, but the list below is sufficient to get the job done): yum install sssd realmd oddjob oddjob-mkhomedir adcli samba-common-tools -y A prerequisite to joining the server to the Active Directory domain is that you should be able to at least resolve the domain. Maybe you can try to ping it if the domain controller’s firewall accepts ICMP requests: [code]# ping domain.local PING domain.local (x.x.x.x) 56(84) bytes of data. 64 bytes from dc1.domain.local (x.x.x.x): icmp_seq=1 ttl=128 time=0.444 ms 64 bytes from dc1.domain.local (x.x.x.x): icmp_seq=2 ttl=128 time=0.499 ms 64 bytes from dc1.domain.local (x.x.x.x): icmp_seq=3 ttl=128 time=0.569 ms 64 bytes from dc1.domain.local (x.x.x.x): icmp_seq=4 ttl=128 time=0.643 ms 64 bytes from dc1.domain.local (x.x.x.x): icmp_seq=5 ttl=128 time=0.640 ms 64 bytes from dc1.domain.local (x.x.x.x): icmp_seq=6 ttl=128 time=0.515 ms ^C --- domain.local ping statistics --- 6 packets transmitted, 6 received, 0% packet loss, time 5001ms rtt min/avg/max/mdev = 0.444/0.551/0.643/0.078 ms[/code] The domain resolves and the domain controller responds to our pings which is good. If this is not working for you, check with your domain or network administrator to find out what is the correct DNS server for the AD domain. Now that everything seems okay, we can attempt to join our server to this domain: [code]# realm join --user=administrator domain.local Password for administrator:[/code] To be able to join a server to an Active Directory domain, you may require some elevated privileges. The default configuration is that any authenticated user can join up to 10 machines to the domain. In my test environment, I am the only admin so I used my administrator account for simplicity, even though this is not a best practice. Don’t go shouting at your domain admins that you need the password for the administrator account, because they will likely not give it to you… Warning: the maximum length of a computer name in Active Directory is 15 characters. So make sure your server hostname is not exceeding that length. Active Directory will allow you to join the host to the domain but it will just chop off the hostname at 15 characters. This might cause problems with multiple hosts trying to use the same computer account because the difference in hostname was only visible after the 15th char (ex: myapp-mysql-server-01 will be chopped to myapp-mysql-ser). If you see errors like the following in your /var/log/messages file, it might be that your computer account was changed, possibly by an issue like this. [code][sssd[ldap_child[12104]]]: Failed to initialize credentials using keytab [MEMORY:/etc/krb5.keytab]: Preauthentication failed. Unable to create GSSAPI-encrypted LDAP connection.[/code] To verify if you successfully joined your server to the domain, you can run: [code]# realm list domain.local type: kerberos realm-name: DOMAIN.LOCAL domain-name: domain.local configured: kerberos-member 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 login-formats: %U login-policy: allow-realm-logins[/code] Note the “required-package” entries in this command output. These are the packages that we installed just a bit earlier. At this point, our server can talk kerberos to the domain (controller), and we are able to search for our domain users [code]# id administrator id: administrator: no such user # id administrator@domain.local uid=1829600500(administrator@domain.local) gid=1829600513(domain users@domain.local) groups=1829600513(domain users@domain.local),1829600512(domain admins@domain.local),1829600572(denied rodc password replication group@domain.local),1829600519(enterprise admins@domain.local),1829600518(schema admins@domain.local),1829600520(group policy creator owners@domain.local)[/code] We don’t want the user to have to specify the fully qualified domain name so we edit the /etc/sssd/sssd.conf file. We update following entries: [code]use_fully_qualified_names = True fallback_homedir = /home/%u@%d[/code] To [code]use_fully_qualified_names = False fallback_homedir = /home/%u[/code] And we restart the sssd service: [code]# systemctl restart sssd[/code] Now we don’t need to specify the FQDN anymore: [code]# id administrator uid=1829600500(administrator gid=1829600513(domain users) groups=1829600513(domain users),1829600512(domain admins),1829600572(denied rodc password replication group),1829600519(enterprise admins),1829600518(schema admins),1829600520(group policy creator owners)[/code] We can now use this active directory user to log in to the server: [code]$ ssh administrator@server administrator@server’s password: Creating home directory for administrator.[/code] Even though I am currently logged in with the domain admin account, this account will not get elevated privileges. You can use the local root account to provide sudo access to the AD users for them to be able to elevate their privileges. Now, giving the AD users access to the server command line via SSH was not part of our project scope. To limit their ability to do so, we can either change the default_shell option in the sssd.conf file to something like /bin/false or /bin/nologin. This will block any AD users from logging in. Or, if we need more control overwho will be able to use the SSH access, we can use configuration options in sshd_config like AllowGroups or AllowUsers. None of these login restrictions will interfere with what is coming next.

Back to MySQL

We drifted off a bit getting involved with our Windows colleagues to be able to join our server into their domain but now that we have successfully done that, we are able to configure our MySQL instance to authenticate against the local PAM system. The method we will be using is the simple one, where we create a MySQL user for each AD user that needs MySQL access and not through a “Proxy account”. The full description of what we will be doing can be found here. The first step for this is to install the PAM plugin: [code]mysql> INSTALL PLUGIN authentication_pam SONAME 'authentication_pam.so';[/code] Also, enable it in the my.cnf file to have it loaded on restart. [code]# PAM authentication plugin plugin-load-add=authentication_pam.so[/code] We can verify the successful loading of the plugin: [code]mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%pam%'; +--------------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +--------------------+---------------+ | authentication_pam | ACTIVE | +--------------------+---------------+ 1 row in set (0.21 sec) [/code] Now we can use the regular CREATE USER or GRANT syntax to create the local users: [code]mysql> CREATE USER '[user]'@'[host]' IDENTIFIED WITH authentication_pam AS 'mysql' mysql> GRANT [PRIVILEGES] on [schema].[table/*] to '[user]'@'[host]' ;[/code] I have created two test users in my testing lab: [code]mysql> SELECT user, host, plugin, authentication_string FROM mysql.user WHERE plugin = 'authentication_pam'; +----------+-----------+--------------------+-----------------------+ | user | host | plugin | authentication_string | +----------+-----------+--------------------+-----------------------+ | mcrauwel | % | authentication_pam | mysql | | mtest | localhost | authentication_pam | mysql | +----------+-----------+--------------------+-----------------------+ 2 rows in set (0.00 sec) [/code] Notice that the authentication_string mentions " mysql". This value points to a PAM service we still have to create. The MySQL manual provides us with the required service configuration. For our Centos machine, we need to create the service file in /etc/pam.d/mysql with the following content: [code]#%PAM-1.0 auth include password-auth account include password-auth [/code] For other OS distributions, there might be some different format required. Check the manual for more details. And that is it for the MySQL configuration part. We are now able to login with the newly created AD user. [code]# mysql -u mcrauwel -p --enable-cleartext-plugin Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 49 Server version: 8.0.12-commercial MySQL Enterprise Server - Commercial Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> \s -------------- mysql Ver 8.0.12-commercial for Linux on x86_64 (MySQL Enterprise Server - Commercial) Connection id: 49 Current database: Current user: mcrauwel@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.0.12-commercial MySQL Enterprise Server - Commercial Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 20 hours 48 min 20 sec Threads: 2 Questions: 1497 Slow queries: 0 Opens: 236 Flush tables: 2 Open tables: 212 Queries per second avg: 0.019 -------------- mysql> [/code] One important thing to note is that we have to enable the cleartext-plugin to send the password to the server. This is because MySQL needs to pass the password on to the PAM service to have it validated against AD using the Kerberos protocol. While Kerberos itself is a secure protocol, it still requires us to provide it with the plain text password for validation purposes. Therefore, it is important to have any TCP connections from the client to the server using SSL encryption to ensure our password remains secure at all times. Lucky for us, MySQL 8.0 has SSL support enabled by default. However, it is still possible to be really naughty and specify the --ssl-mode=DISABLED option to the client and MySQL 8.0 will still accept the connection and the password. There is a way to also close this loophole by enabling the require_secure_transport option: [code]mysql> SELECT @@require_secure_transport; +----------------------------+ | @@require_secure_transport | +----------------------------+ | 0 | +----------------------------+ 1 row in set (0.00 sec) mysql> SET GLOBAL require_secure_transport = ON; Query OK, 0 rows affected (0.01 sec) mysql> SELECT @@require_secure_transport; +----------------------------+ | @@require_secure_transport | +----------------------------+ | 1 | +----------------------------+ 1 row in set (0.00 sec) [/code] Now the server will not accept our insecure connection anymore: [code][root@mysql ~]# mysql -u mcrauwel -p --enable-cleartext-plugin -h 127.0.0.1 --ssl-mode=DISABLED Enter password: ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.[/code] That's it so far for our adventures with MySQL 8.0 and Active Directory authentication. I am really excited to have this first MySQL 8.0 installation going to production. The list of features to explore is gigantic and so I hope there will be more opportunities to work on (blog about) in the future.
Topics: AWS Technical Track

Comments (1)

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.