Manage MySQL and Related Database Servers with LDAP

This is a refreshed article which initially only focused on integrating the community edition of MySQL with LDAP. That’s no longer possible, so we’ve provided guidance to connect to a variety of popular relational databases that are similar or compatible. You should choose the one that works best for your use case. This article was contributed to by TJ Webb, Cody Pritchard, and Stephen Brown.

MySQL is a leading relational database system that’s available in commercial and open source offerings that are driven by Oracle. Its ubiquity has spawned several forks, providing several distinct alternatives. Managing users for MySQL is typically a manual chore resulting in extra time, systems, and effort — and, potentially a security risk when users created in the database are independent of a business’s identity management system and user provisioning process. The commercial distribution of MySQL supports external user authentication including LDAP using its bundled connectors. The community edition has fewer native options, but LDAP management is still possible when very specific prerequisites and security requirements are met. An unsupported GitHub project can be used to configure a free Linux PAM plugin. 

There are other choices for your project within the MySQL “family” such as MariaDB and Percona, or another open source SQL database such as Postgres. MariaDB is a free and open source software (FOSS) fork of the MySQL database that has a PAM authentication option. It’s evolved in its own direction and, while compatible, isn’t a direct substitution of MySQL. Percona is a performance-tuned edition of MySQL that also provides a plugin. We’ve included Postgres as an alternative relational database. Your requirements and budget will determine what solution is the best fit for the job at hand. The common thread is the user management benefits of LDAP, a common user directory that organizations may have, based upon mature and open standards. Connecting SQL databases to LDAP services benefits IT admins in a number of ways:

• One central place to manage your user credentials – Saves time and effort and makes it possible to easily add another authentication factor and/or business rules for enhanced security following zero-trust principles
• SSO – Your users effectively get single sign-on for their technical applications
• Increased security – Reduces risk of users having access that shouldn’t

JumpCloud’s cloud directory platform makes it easy to manage, authenticate, and authorize MySQL users. The platform’s LDAP service makes it possible for users to be populated in our directory and then MySQL can authenticate via a secure LDAP endpoint requiring only minimal configuration necessary on the application side. Multi-factor authentication (MFA) then adds an additional layer of security.

This article covers:

• How to manage MySQL users with JumpCloud
  • Using the plugins included in the commercial edition of MySQL
  • How to manage MySQL users with JumpCloud using an open source plugin for the community edition
• How to manage MariaDB users with JumpCloud
• How to manage Percona users with JumpCloud
• How to manage Postgres users with JumpCloud

Considerations:

If you haven’t done so already, please sign up for the JumpCloud service here. You can evaluate this entire process below with no commitment. We give you 10 free users forever with the full functionality of the JumpCloud platform and the option for 10 days of live chat support to help you along the way if you get stuck with the integration(s).

Setting Up an LDAP Server

First, you’ll need to have an LDAP server to manage your users. You can easily leverage JumpCloud for this purpose by following the steps below. You may also manually provision and configure LDAP if you prefer using OpenLDAP, as an example LDAP server. That configuration option is also outlined here.

Cloud LDAP Using JumpCloud

Step 1: Preparing JumpCloud as the LDAP Directory

Before MySQL can be integrated with JumpCloud’s LDAP endpoint, the following steps need to be completed to ensure it can communicate effectively via ldapsearch.

1. Log in to the JumpCloud Admin Portal
2. Go to USER AUTHENTICATION > LDAP
3. Click ( + )

You may see the message: “To enable LDAP, you need to have at least one user that’s enabled as LDAP Bind DN.” A Bind DN is a Distinguished Name (DN). This is a credential you’ll authenticate against in LDAP and you may consider creating a “service” user account specifically for this purpose with a very strong password.

Step 2: Create an LDAP Service User Account

Create an LDAP Binding user

The LDAP Binding user is created to allow the application to gain access to the LDAP directory in order to facilitate authentication requests when a regular LDAP user is attempting to login. JumpCloud does not support anonymous binds. When a user is designated as the Bind DN, they are automatically bound to the JumpCloud LDAP directory. It’s advisable to have a dedicated service account, with a strong password, separate from your regular user accounts.

1. Log in to the JumpCloud Admin Portal
2. Go to USER MANAGEMENT > Users 
3. Click ( + ), then select Manual user entry 
4. Input user information
   • First name
   • Last name
   • Username (required)
   • Email (required)
   • Description
5. Under Security Settings and Permission​ > Permission Settings​​​​​​
   • Click the checkbox next to Enable as LDAP Bind DN. When enabled, this user acts to bind and search to JumpCloud LDAP directory; one or more users can enable this option.

Considerations:

• It’s not required that this user be a “service account”. Any JumpCloud user can be set as a binding user, although it’s generally recommended to treat this account as privileged for use only to facilitate the application’s ability to bind/search the LDAP directory.
• This option does not grant users access to LDAP. To grant access, see Binding Users to Resources.
• More than one user may be designated as an LDAP Binding user, some applications require this designation for all users of the application. This can be the case if the Bind DN is able to login, but others cannot even though they are bound to the LDAP directory.
• The LDAP Binding user can be excluded from the password expiration policy by selecting Password Never Expires. Note: you cannot set that condition until after the binding user is saved and created. All other password policies, such as complexity requirements and minimum age, are global and will apply automatically.

Adding users to the JumpCloud directory

In this next step, you will add users to the directory. These will be the users you desire to provision to MySQL and elsewhere. Follow the steps above in Step 1, yet in this case, you do not need to elect these users as an LDAP Binding account. The MySQL documentation notes that users that will be managed by LDAP must already exist within the directory.

Every cloud LDAP integration discussed below requires implementing the steps outlined above.

Manual Setup

The other option is to do it yourself where you’ll be required to stand up and manage an LDAP server to utilize these authentication methods. You’ll have to manage uptime and maintenance, which creates the potential for a single point of failure (directory availability) within the authentication chain. Rolling your own MFA for added security will involve installing and configuring a FreeRADIUS server and LinOTP to establish a second authentication factor for LDAP. 

Linux distributions are capable of configuring LDAP to use STARTTLS to secure authentication between the LDAP client and server, but you’ll have to also take on the management of provisioning the certificate and managing keys yourself. For Windows environments, you can configure the Windows native LDAP library either in Windows Server or a VM hosted by a cloud provider through the Add Roles and Features Wizard interface. AD LDS (Active Directory Lightweight Directory Services) must be set up as well as LDAPS.

Linux

Here are the commands required to install the packages in Ubuntu Linux.

The subsequent steps can be found here.

Windows Server

Windows users can configure the Windows native LDAP library either in Windows Server or a VM hosted by a cloud provider through the Add Roles and Features Wizard interface. AD LDS (Active Directory Lightweight Directory Services) must be set up as well as LDAPS.

Microsoft’s documentation is available here.

You can easily leverage JumpCloud’s hosted LDAP service instead of standing up your own LDAP server and managing it, all part of Directory-as-a-Service. Connect it to all of your critical apps, and you are good to go. Your users will also appreciate this, as their single account can now be used for technical applications as well. If you are utilizing MySQL within your organization, give JumpCloud a try to simplify user management and increase database security.

MySQL Commercial Edition

Oracle provides MySQL Enterprise as a subscription-based service, which you can configure MySQL to authenticate to the JumpCloud LDAP endpoint. There are two basic options to integrate the commercial MySQL edition with an LDAP directory: 

• LDAP Pluggable Authentication
• Privileged Access Management (PAM) 

LDAP Pluggable Authentication is the most secure method out of the box, using TLS to communicate with the cloud directory. However, all approaches listed here do not use encryption on the client side and must be configured correctly to not become a security problem. The involved plugin must also be explicitly enabled to avoid inadvertent password exposure. 

Please note that you won’t be able to install the plugins for LDAP Pluggable Authentication if you’re running the community build, which lacks the necessary server-side functionality. There are additional steps involved to successfully deploy TLS with the other two methods.

This diagram shows all the components except the LDAP server (or ldap.jumpcloud.com) itself: The PAM icon is replaced with the LDAP server since it connects directly.

Please be mindful of this warning and follow all directions. MySQL provides several options for authentication and each has its own documentation as well as advanced options.

• Documentation for LDAP Pluggable Authentication is found in section 6.4.1.7 of the MySQL Reference Manual.
  • Its capacity for external authentication (using a cloud-based directory) would be used to integrate the database with JumpCloud. 
  • The connection can also map users from the directory to native users within MySQL using Proxy user support to avoid recreating users. The instructions outline the prerequisites.
• You can override the OpenLDAP TLS setting and use StartTLS with the LDAP MySQL plugin.
• PAM is another option to access external LDAP services, and the documentation is outlined in section 6.4.1.5 of the Reference Manual.
  • It makes the application (MySQL) independent of whichever authentication stack the host OS’s admin is using to set your preferences on a per-service basis. 
  • The hostname for the server must be resolvable while you’re configuring PAM authentication.
  • Note that communication between the PAM stack and LDAP are unencrypted unless you also utilize Linux’s System Security Services Daemon (SSSD) to manage the MySQL LDAP configuration or use LDAPS to secure the connection. 
  • The PAM LDAP module can be configured for LDAPS by adding the following options within the PAM configuration file “/etc/pam_ldap.conf”:

Just like with the HTTP protocol, the LDAP protocol can be written using a Uniform Resource Locator or Identifier (URL/URI) and contain additional information about the directory and associated queries. The customer’s JumpCloud LDAP Directory will always need to point to the DNS name of our OpenLDAP-based Directory Servers: ldap.jumpcloud.com

LDAP URL for unencrypted LDAP Directory Access:  

(ldap:// implies port 389 so specifying the port is not required in most cases.)

ldap://ldap.jumpcloud.com

ldap://ldap.jumpcloud.com:389

LDAPS URL for encrypted LDAP Directory Access:

(ldaps:// implies port 636 so specifying the port is not required in most cases.)

ldaps://ldap.jumpcloud.com

ldaps://ldap.jumpcloud.com:636

JumpCloud’s LDAP services would be the hostname used in this configuration.

Port 389 will operate over plain-text if StartTLS is not negotiated.

MySQL Community Edition

Since there are no server-side plugins available in this distribution, another possibility is to use an open source plugin for the community version that’s developed and maintained by an open source development company. The caveat is that the community isn’t large and a single vendor is the primary contributor. The configuration file looks like this with the cacert file being required for an LDAPS connection, which will encrypt LDAP data. It’s an extra step (highlighted below), but is a requisite for good security practice:

The capitalized letters in the bind_dn indicate where to enter your organization’s parameters.

Note that the password between the client and server is in plaintext by default just as with the MySQL Enterprise PAM module. The LDAP server performs hashing and authentication versus the default of the password being sent onto the MySQL itself.

Please see this knowledge base article for more information about including a certificate.

JumpCloud’s LDAP services would be the hostname used in this configuration, using either of these options:

Using StartTLS (ldap://ldap.jumpcloud.com:389) or 

TLS / SSL (ldaps://ldap.jumpcloud.com:636)

Port 389 will operate over plain-text if StartTLS is not negotiated.

LDAP groups created in JumpCloud are stored under the same OU as Users. The constructed bind dn reference is the same: ou=users, o=JC_ORD_ID, dc=jumpcloud, dc=com. LDAP is a tree so the customer’s o=JC_ORG_ID, dc=jumpcloud, dc=com is where their LDAP information is stored. Some configurations ask for explicit locations for users and groups or ask for a single Base DN. The best option here is ou=users. It would work either way, but is more efficient higher up the tree.

Other FOSS SQL Databases

The following databases either provide full compatibility with MySQL or can be optimized for different use cases that may be of benefit to your project. They have active open source communities where support is available and builds are updated to be in line with changes made to MySQL.

MariaDB

First, install the database. These instructions follow the syntax of Ubuntu and other RPM distributions.

1a) We recommend approaching this integration using pam_ldap for better security.

1. The assumption is that an LDAP server is already set up (such as JumpCloud LDAP) but you’ll have to install the PAM packages as another prerequisite.

2. You’ll then have to configure the plugins.

3. Next, you’ll install pam_user_map and configure that service as well.

Please see the documentation here for this step.

4. Install the auth plugin.

Then configure the service in /etc/pam.d/mariadb as outlined in Step E.

5. Only configure pam_ldap (We don’t recommend the pam_unix step)

6. The final step is to check your work and test the configuration.

1b) MariaDB also includes a PAM plugin that can be retrieved from the project’s Git repository.

You then follow steps to create a PAM policy and the rest happens primarily on the server side with the installation of the required plugins and creation of service accounts. You can then set up a proxy configuration to map user names between the database and LDAP and set up other policy variables, such as using LDAPS, by following these directions. Additional documentation about those settings is available here. 

This approach executes on local Unix authentication, which, same as the other Pam_unix examples above, must be configured correctly due to potential security concerns and best practices. The directions also indicate turning off SELinux (Security-enhanced Linux), a Linux kernel security module that provides access policies such as MAC, necessitating additional configuration steps to avoid a full disable.

JumpCloud’s LDAP services would be the hostname used in this configuration, using either of these options:

Using StartTLS (ldap://ldap.jumpcloud.com:389) or 

TLS / SSL (ldaps://ldap.jumpcloud.com:636)

Port 389 will operate over plain-text if StartTLS is not negotiated.

Percona

The Percona server for MySQL is also open source and fully compatible with the primary Oracle distribution. However, it’s more purpose-built and is optimized to handle large amounts of data and memory management for large scale implementations. It’s built to achieve parity with Oracle’s commercial edition for its overall availability, backup, scalability, and security functionality. Another attribute that differentiates it is added visibility monitoring of server operations.

Its PAM plugin is documented here and the following is an example of the setup that you’ll see:

There are a few minor changes necessary to connect with the cloud directory. JumpCloud leverages LDAP Bind DNs, which means that we construct our DNs differently than the example Percona provides above. We also do not use the “standard” ou=People, and instead use  ou=Users for both User objects and Groups for full membership authorization reflected per JumpCloud-managed LDAP groups. The syntax is as follows:

JumpCloud’s LDAP services would be the hostname used in this configuration, using either of these options:

Using StartTLS (ldap://ldap.jumpcloud.com:389) or 

TLS / SSL (ldaps://ldap.jumpcloud.com:636)

Port 389 will operate over plain-text if StartTLS is not negotiated.

Postgres

PostgreSQL is a more SQL compliant relational database than MySQL, which makes it more reliable for data due to its stricter nature by default. It can authenticate directly with LDAP (or LDAPS) and has several configuration options outlined here.

You’ll want to follow the mode “simple bind mode” to integrate with JumpCloud.

The following example is from the documentation and will work with OpenLDAP.

“In the first mode, which we will call the simple bind mode, the server will bind to the distinguished name constructed as prefix username suffix. Typically, the prefix parameter is used to specify cn=, or DOMAIN\ in an Active Directory environment. suffix is used to specify the remaining part of the DN in a non-Active Directory environment.”

As noted above, make the following changes to the LDAP Bind DN user to configure your connection for JumpCloud LDAP:

JumpCloud’s LDAP services would be the hostname used in this configuration, using either of these options:

Using StartTLS (ldap://ldap.jumpcloud.com:389) or 

TLS / SSL (ldaps://ldap.jumpcloud.com:636)

Port 389 will operate over plain-text if StartTLS is not negotiated.

Try JumpCloud

Learn about other JumpCloud LDAP configurations for your applications. We hope that you’ll try our platform for its convenience, the time you’ll save, and security capabilities. You can sign up today to experiment with these integrations and add additional security for safer authentications through the JumpCloud platform without having to stand up additional server infrastructure.