Configuring LDAPS for database access in DSM v2.1

by Posted on

Data Services Manager version 2.1 introduces a much anticipated feature. This is the ability to use LDAPS to give users access to databases. Version 2.0.x already had LDAPS support for user access to the DSM Provider Appliance Portal/UI. Version 2.1 extends that support to the databases which DSM provisions. In this post, we will see how to configure secure LDAPS to connect to Active Directory, and then the steps which are used to grant users access to the databases. We will see how this can be done at database creation time, but also how it can be done after the database has already been provisioned.

Configuring Directory Services on DSM (LDAPS)

The first step is to configure Directory Services on DSM. This can be done from two places. It can be achieved through the vSphere Client or it can be done via the DSM UI. Let’s do it via the vSphere Client in this example. The steps are the same in both cases. First, a Certificate Authority (CA) from your LDAPS server/Active Directory needs to be added to DSM. This is done from the Trusted Root Certificates view in the vSphere Client. In the vSphere Client, select the vCenter Server object in the inventory. Then select Configure. Under the Data Services Manager section, you will find the Trusted Root Certificates. Click ADD to add the CA (PEM format). The CA for your LDAPS should now show in the list of Trusted Root Certificates:

Staying in the same section of the vSphere Client, you will find the Directory Service Settings. This is where we add the LDAPS configuration. Here is the configuration taken from my DSM integration with Active Directory using LDAPS, with the usual fields such as server, domain, Base Dn, etc.

The one thing which might seem a little odd here is the port used for LDAPS, 3269. It seems that certain database clients have issues with Microsoft Active Directory using port 636 (the LDAPS port), and instead have a preference for using port 3269, which is the Active Directory Global Catalog over SSL. This is mentioned in the DSM 2.1 Network Requirements Summary in the official docs.

Also note that the default Search User Attribute is userPincipalName. Admins could also configure Directory Services to use uid or sAMAccountName. This will be important to keep in mind when it comes to granting users access and permissions to the database in the next step.  With LDAPS configured, we can proceed with provisioning a database with LDAPS access.

Provision a Database with LDAPS User Access

Configuring a database with Directory Service (LDAPS) is very simple. There is a single option that needs to be enabled in the Basic Information section of the database provisioning wizard. The screenshot below shows the “Enable Directory Service Authentication” enabled.

And that is the only step from a database provisioning perspective. However, if a database is already provisioned without Directory Services, simply edit the Basic Information of the existing database and enable it there:

The final step is to grant Directory Service/LDAPS users access to the database. We will do that next.

Grant Directory Service/LDAPS User Access to the Database

Once the database has been provisioned, we can access the database as an admin user using the connection string for the database in question. The connection string is available from the database view in the DSM UI. Once connected to the database as an admin user, we can begin grant access and permissions to the LDAPS users. The procedure to create an LDAPS user for accessing a PostgreSQL database is documented here in the official DSM 2.1 docs. However, since we have deployed a MySQL database, we will follow the instructions documented here. The first step is to connect to the database as the mysql-admin user, which is embedded in the connection string retrieved from DSM. I am using MySQLWorkbench for connecting to the database and running SQL commands. The commands I am running as the mysql-admin are:

CREATE USER IF NOT EXISTS `amaury@rainpole.com`@'%' IDENTIFIED WITH authentication_ldap_simple;

GRANT ALL ON `ldaps-db-test`.* TO `amaury@rainpole.com`@'%';

Let’s discuss the choice of username. I am using username amaury@rainpole.com since this is a userPrincipleName, and this is what was chosen as the Search User Attribute when the Directory Service was configured. If I had chosen to set this attribute as perhaps uid or sAMAccountName, I could have used the simpler username of amaury. After creating the user and granting permissions, let’s now check to see if this user can connect to the database.

Connect as LDAPS user

Let’s now try to connect to the database as the LDAPS user amaury@rainpole.com and run some SQL commands.  Once again, I will use the MySQLWorkbench tool. On first connection attempt, I got an error stating that the ‘mysql_clear_password’ cannot be loaded: plugin not enabled, as shown below:

This is an advanced parameter in MySQLWorkbench. Simply select the Advanced view from the New Connection Setup and enable the Cleartext Authentication Plugin, as shown below:

Now if we test the connection once more, we will hopefully be able to connect to the database as an LDAPS user. Let’s first test the connection:

This looks good. Now connect to the database and run a simple SQL command such as ‘show databases;’.

Success! It looks like everything is working as expected. Thanks for reading this far. Hope you found it useful.

Note: If you are upgrading from DSM 2.0.x, you will need to re-add your LDAPS configuration as it will require the addition of the CA (Certificate Authority). Thus, if you were only using LDAPS users in DSM 2.0.x for access, you will not be able to login to the DSM UI/Portal until you have added the CA and reconfigured LDAPS on your upgraded DSM 2.1.

Published by Cormac