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.
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.
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.
Looking to optimize your MySQL use?