Configuring MySQL Enterprise Monitor to authenticate from LDAP

October 11th, 2010 by Leandro Morgado

In the last post, we saw how to create a test OpenLDAP server, populate it and secure it with SSL certificates. Now we are going to have a look at how to configure MySQL Enterprise Manager (MEM) to authenticate against LDAP. We will be examining a few different kinds of setup methods.

1. Using LDAP to fetch just the user password

The simplest form is to configure a user with MEM and set it to the LDAP type. The user’s role is setup in MEM during user creation time and is not fetched from LDAP. Below you can see the user definition page:

How to create a LDAP user in MEM - password only

How to create a LDAP user in MEM - password only

The username is user1 as specified in MEM, but where do we get the password from? We need to tell MEM how to find this user. Here is a sample user as defined by the LDIF file used in my previous post:

# user1, People, example.com
dn: uid=user1,ou=People,dc=example,dc=com
objectClass: person
objectClass: inetOrgPerson
objectClass: organizationalPerson
objectClass: top
cn: Aaren Atp
sn: Atp
description: This is the description for Aaren Atp.
employeeNumber: 1
givenName: Aaren
homePhone: +1 280 375 4325
initials: ALA
l: New Haven
mail: user.1@maildomain.net
mobile: +1 680 734 6300
ou: admin
pager: +1 850 883 8888
postalAddress: Aaren Atp$70110 Fourth Street$New Haven, OH  93694
postalCode: 936942
st: OH
street: 70110 Fourth Street
telephoneNumber: +1 390 103 6917
uid: user1
userPassword:: e1NTSEF9Z0tsZjU4cm50Wit4b045N0U4cWlldVJQK1RMOVAzTGw=

Now tell MEM to look for the uid by navigating to Settings > Global Settings > LDAP Authentication:

MEM settings for LDAP password lookup only

MEM settings for LDAP password lookup only

The password is specified in the userPassword attribute and uses standard LDAP supported formats. Technically what MEM does is an LDAP simple bind operation (Authentication Mode = Bind as User) with the given username/password and then attempts to search for the given user using the DN pattern.  If the binding and search succeeds, we assume the password is correct. With this in place, you can now login with user1 without having to specify the password in MEM.

2. Adding MEM Roles from LDAP

If we want to fetch the user’s role from LDAP in addition to the password, we need to enable the Map LDAP Roles to Application Roles check box in the LDAP settings page. We’ll see the two ways of doing this shortly.

The good thing about enabling role mapping is that manual user creation in the Web dashboard is no longer required. If the user does not already exist within MEM, it will be “copied” from LDAP to MEM when that user first logs in. The lookup order is Built-in users first, then LDAP users. If you want user authentication to be exclusively done against LDAP then make LDAP authoritative by ticking the check box:

MEM authoritative LDAP authentication

MEM authoritative LDAP authentication

WARNING: Enabling this prevents Built-in users from being authenticated. Make sure that you test with non authoritative authentication first and make sure role mapping is working. Also, ensure that you have a valid LDAP user who has admin privileges or risk locking yourself out.

Below is an example of how my test user table looked before logging in with user1:

User table before LDAP login

User table before LDAP login

And this is how it looks after a successful login:

User table after LDAP login

User table after LDAP login

Notice how the LDAP user1 entry was created automatically in the MEM user table with values from the LDAP directory.

2.2.1 MEM roles in a LDAP user’s attribute

The first way of achieving role mapping is to simply have an attribute in the user’s entry, whose attribute value will be the role. In our example, we use the ou attribute. The relevant attributes for our sample users are:

uid: user1
ou: admin
....
uid: user2
ou: dba

The relevant settings in MEM are:

MEM settings for LDAP role mapping with user attributes

MEM settings for LDAP role mapping with user attributes

Now when you login with user2 for example, its role will be fetched from LDAP. We can verify this in the User Preferences section as shown by the screenshot below:

A user that has authenticated via LDAP with role mapping

A user that has authenticated via LDAP with role mapping

If we try user1, then we will get an admin (manager) role.

Note that some versions have a bug that prevents this setup from working. Check if you are affected.

2.2.2 MEM roles specified by a LDAP group

There is another way to specify a user’s role. Instead of each user having an attribute representing the role, a roles group is created, which enumerates the users that are members of that role. In our example, this is represented in the LDIF file as:

dn: ou=groups,dc=example,dc=com
objectClass: organizationalUnit
ou: groups

dn: cn=admin,ou=groups,dc=example,dc=com
objectClass: groupOfUniqueNames
cn: admin
uniqueMember: uid=user1,ou=People,dc=example,dc=com

dn: cn=dba,ou=groups,dc=example,dc=com
objectClass: groupOfUniqueNames
cn: dba
uniqueMember: uid=user2,ou=People,dc=example,dc=com

So again, we need to tell MEM how to do role lookups from the LDAP directory.  This is how the LDAP settings in MEM would look like for our particular case:

MEM settings for LDAP role mapping in groups

MEM settings for LDAP role mapping in groups

Note, that due this bug, you might not get this particular setup to work.

2.3 Configuring LDAP lookups to use SSL and STARTTLS

Once plain text authentication is working, you can choose to secure communications between MEM and the LDAP server with either STARTTLS or SSL. Obviously the LDAP server must support it. Note that SSL (ldaps) is deprecated in the latest LDAP v3 and STARTTLS is prefered. MEM supports both types. To use STARTTLS, simply select it in the menu as follows:

MEM with STARTTLS LDAP authentication

MEM with STARTTLS LDAP authentication

There is no need to change the default port. Using SSL (ldaps) however, implies using the ldaps port, which by default is 636, and setting MEM to use SSL(ldaps) :

MEM with SSL (ldaps) LDAP authentication

MEM with SSL (ldaps) LDAP authentication

One thing to note is that the (Primary) Server Hostname should match the SSL certificate of the LDAP server.

If you are using self signed certificates that were not issued by a commercial SSL company, MEM will not have the root CA (Certificate Authority) in it’s repository. You should see a message in the $INSTALL_DIR/monitor/apache-tomcat/logs/catalina.out logfile:

javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed:
sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

To fix this, you will need to import the your root CA certificate that was used to generate the LDAP server’s certificate. This can be achieve with the Java Keytool utility as follows:

shell> cd  /opt/mysql/enterprise/monitor/java
shell> bin/keytool -import -trustcacerts -alias ldapssl \
       -file /etc/openldap/ssl/ca-cert.pem -keystore lib/security/cacerts

This needs to be run under MEM’s Java installation directory. Unless you have previously modified it, the default password is changeit . We saw how to create a CA certificate (ca-cert.pem) in the previous post. Finally, restart the MEM monitor service.

In case you have problems importing your root CA certificate, make sure it is correctly format and acceptable by the Java keytool utility:

keytool can import X.509 v1, v2, and v3 certificates, and PKCS#7 formatted certificate chains consisting of certificates of that type. The data to be imported must be provided either in binary encoding format, or in printable encoding format (also known as Base64 encoding) as defined by the Internet RFC 1421 standard. In the latter case, the encoding must be bounded at the beginning by a string that starts with ‘—–BEGIN’, and bounded at the end by a string that starts with ‘—–END’.”

http://download.oracle.com/docs/cd/E17409_01/javase/6/docs/technotes/tools/windows/keytool.html

To troubleshoot, the best place is to look at the catalina.out logfile. If you need extra debugging information, you can start MEM’s JVM as follows:

JAVA_OPTS="$JAVA_OPTS -Djavax.net.debug=ALL"

In the current version (2.2.1.1721) this should be added at around line 180 of $INSTALL_DIR/monitor/apache-tomcat/bin/catalina.sh. Note that catalina.sh will get overwritten when you next upgrade so if you want JAVA_OPTS changes to be persistent, then use setenv.sh (or setenv.bat for Windows.). A Tomcat restart is needed. Note that a lot of information is logged and you’re log files will grow rather quickly. Please remember to turn this off in regular production mode.

Hopefully this will help when configuring MEM to authenticate it’s users against a LDAP directory.

3 Responses to “Configuring MySQL Enterprise Monitor to authenticate from LDAP”

  1. Simon J Mudd Says:

    I’d been having some problems getting LDAP configured so thought I’d add the -Djavax.net.debug=ALL option. Please warn people that this generates quite a large amount of logging. I didn’t pay too much attention to this and filled up a dev MEM filesystem over the weekend due to the 40GB of logging it generated. That’s on an idle MEM system…

    Nevertheless, I’m still having trouble getting the encrypted LDAP connection to work. It seems to me that it might be quite helpful if there were some sort of debug tool which give more info on what was going on and allowed the testing of the LDAP setup prior to actually configuring it in the dashboard. That would probably make it easier to debug and diagnose.

  2. Simon J Mudd Says:

    The correct place I believe to set the JAVA_OPTS in MEM is: /opt/mysql/enterprise/monitor/apache-tomcat/bin/setenv.sh.

    The reason for using this location is to avoid any custom changes being lost when upgrading the software. This file is sourced from the catalina.sh file, but not overwritten.

    I have had several issues in the past where the dashboard installer has overwritten my custom configuration, necessary due to the size of the installation I am managing, and this had caused some performance problems, until I had realised the cause.

  3. Leandro Morgado Says:

    Simon, thanks for the tips! I have updated the article accordingly.

    As for troubleshooting the LDAP server SSL settings, if it’s openLDAP, I suggest using:

    shell> ldapsearch -x -h localhost -ZZ -b ‘dc=example,dc=com’

    Note that the -h localhost must be the same as the openLDAP server’s certificate Common Name (CN) entry.

    My previous post includes this information:
    http://lmorgado.com/blog/2010/10/11/setting-up-openldap-for-mysql-enterprise-monitor/

Leave a Reply

You must be logged in to post a comment.