Adding a little TLS complexity to authentication_ldap_simple

In a previous post I showed that by default when authentication_ldap_simple communicates with a Windows Domain Controller (or any other LDAP service), then the password is transmitted unencrypted during authentication.

This time I’ll demonstrate how to close this loophole.  A pre-requisite is that the Domain Controller needs to be configured to accept secure connections. This is done by installing a certificate, the process is well documented elsewhere so I won’t repeat it here.

There are two different ways to configure secure communication.  The first method is to set authentication_ldap_simple_server_port to 636.

As of MySQL 8.0.14, if the LDAP port number is configured as 636 or 3269, the plugin uses LDAPS (LDAP over SSL) instead of LDAP. (LDAPS differs from startTLS.)

The second method is to set authentication_ldap_simple_tls to ON.

For simple LDAP authentication, whether connections by the plugin to the LDAP server are secure. If this variable is enabled, the plugin uses TLS to connect securely to the LDAP server.

In both cases we have to set authentication_ldap_simple_ca_path to point to the certificate authority file used when securing the domain controller.  (Pro-tip ensure the both the file attributes of this certificate and of the directory it sits in are such that the mysql process is able to access it, you won’t believe how long I wasted due to this).

Of the two methods, I have been informed that the TLS method is optimal so that is what I will demonstrate.  Note I have found that it’s better to load the plugin and set the variables in the mysql configuration file (my.cnf) and restart the service rather than setting them dynamically (it seems the otherwise the values do not correctly propagate to the appropriate processes due to LDAP connection pooling)  so that’s what I’ll show you.

[root@lnx-mysql8 ~]# tail -5 /etc/my.cnf
plugin-load-add=authentication_ldap_simple.so
authentication_ldap_simple_server_host='win-dc.windows.domain'
authentication_ldap_simple_group_search_attr=
authentication_ldap_simple_tls=ON
authentication_ldap_simple_ca_path='/etc/certs/win-dc.crt'
[root@lnx-mysql8 ~]# 

Let’s have a look at the network traffic on port 389 (LDAP) on MySQL service start.  Observe that, after some initial negotiation regards the certificate, all traffic is encrypted.

[root@lnx-mysql8 ~]# tcpflow -C port 389
tcpflow: listening on enp0s3
0w1.3.6.1.4.1.1466.20037
0(x
1.3.6.1.4.1.1466.20037
]mK?$(UI0,($
kjih98762.*&=5/+'#g@?>3210EDCB1-)%</A C 0B10UXX10UDefault City10U 0B)JJ:7 }$kwIuI_0"%21-dc.windows.domain00 F#qI925rgCbG?{ O{R?_zm+(cx7Ju&+C0A0U0U%0 d&,(V5zVmBj2ZhZw%m@VX}5A2nDf!a)n[wn:~JTm:!0`jL4yMv"8'LH+BHQ K#F _3f]t'u)5B.^/fEKIb.Tj2?03`g5.0RJaF'pH&i=QSa[m3&j~~10&=kv)S%oiH3RvK'wE-tbJ8Tl#:B9tw;MB!FT]7AA7Gn>adwCR#I:x*#IIk8.g62~hd|N_L%OIBC#V|@)o+O_Afo-At~XHt`<fV'r]"u'}GF@<h}- eBA]vEJAu|=-t7ATiPhz(stn`[9U[_s@" (O,tD/'1RT{g6}I3% 0w1.3.6.1.4.1.1466.20037 0(x 1.3.6.1.4.1.1466.20037 )=?s@9fj& *x"80,($ kjih98762.*&=5/+'#g@?>3210EDCB1-)%</A
C

To save you having to refer to previous post here is how to create the AD authenticated user using the LDAP DN (Distinguished Name):

mysql> CREATE USER 'patrick'@'%'
    ->             IDENTIFIED WITH authentication_ldap_simple
    ->             BY 'CN=patrick,CN=Users,DC=WINDOWS,DC=DOMAIN';
Query OK, 0 rows affected (0.01 sec)

I am now able to connect using my Windows username and password:

[root@lnx-mysql8 ~]# mysql                                  \
>                          --host=lnx-mysql8.windows.domain \
>                          --enable-cleartext-plugin        \
>                          --user=patrick                   \
>                          --password=Password123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.16-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2019, 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>

And let’s check the network traffic to domain controller during the authentication

[root@lnx-mysql8 ~]# tcpflow -C port 389
tcpflow: listening on enp0s3
Whl+E]X=n0ucbDOBt'
.rvY* CKWLS+

No more password in plain sight. It’s still in my bash history, but that’s something I can easily resolve by not passing it as a command line argument 🙂

(MySQL) Logged and Loaded: authentication_ldap_simple_log_status or authentication_ldap_sasl_log_status not effective?

A quick one in case anyone else hits the same problem as I encountered.  The documentation for authentication_ldap_sasl_log_status and  authentication_ldap_simple_log_status states these variables can be set to a value between 1 and 5 to control the types of messages logged.

If you set them and still find nothing is logged then sett log_error_verbosity to it’s maximum value of 3 and you should find the messages are output to the error log as expected.

Thats all!

Let the wrong one in! MySQL AD Authentication with authentication_ldap_simple

I mentioned in my previous blog entry that had I encountered an issue with this plugin, and I think now that it has been fixed in the latest MySQL versions (released on 2019-04-25) it’s reasonable to share my findings.

The following tests are with MySQL Version 8.0.13. I start by installing the plugin, pointing it at my Windows Domain Controller and creating a user associated with the DN of my Windows account.

mysql> INSTALL PLUGIN authentication_ldap_simple SONAME 'authentication_ldap_simple.so';
Query OK, 0 rows affected (0.05 sec)

mysql> SET GLOBAL authentication_ldap_simple_server_host='win-dc.windows.domain';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'patrick'@'%'
    ->        IDENTIFIED WITH authentication_ldap_simple
    ->        BY 'CN=patrick,CN=Users,DC=WINDOWS,DC=DOMAIN';
Query OK, 0 rows affected (0.08 sec)

Next we test that everything works OK by trying to log in with my correct Windows password.

[patrick@WIN-CLIENT] C:\> mysql
>>                              --host=lnx-mysql8.windows.domain `
>>                              --user=patrick                   `
>>                              --password=Password123           `
>>                              --enable-cleartext-plugin
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 63
Server version: 8.0.13-commercial MySQL Enterprise Server - Commercial
...
mysql>

Also providing the wrong password denies access. Everything is as expected so far.

[patrick@WIN-CLIENT] C:\> mysql                                  `
>>                              --host=lnx-mysql8.windows.domain `
>>                              --user=patrick                   `
>>                              --password=WrongPassword         `
>>                              --enable-cleartext-plugin
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'patrick'@'WIN-CLIENT.windows.domain'...

However what would you expect to happen if I try to log in to this account without providing a password?  Let’s see…

[patrick@WIN-CLIENT] C:\> mysql                                  `
>>                              --host=lnx-mysql8.windows.domain `
>>                              --user=patrick                   `
>>                              --enable-cleartext-plugin
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 8.0.13-commercial MySQL Enterprise Server - Commercial
...
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| patrick@%      |
+----------------+
1 row in set (0.00 sec)

Huh? It lets me in!  After double checking everything I raised an SR for this and a bug was created and fixed in 8.0.16. Apparently the 5.7 branch was also affected, and this fix is also in 5.7.26.

Bug #29637712: The authentication_ldap_simple plugin could enforce authentication incorrectly.

Let’s test in 8.0.16. Note one difference here is that I have to set variable authentication_ldap_simple_group_search_attr to ” to disable AD group checking. This behaviour, related to proxy users,  seems to have been introduced in 8.0.14, but on my lab setup this step breaks authentication completely so I disable it.

mysql> INSTALL PLUGIN authentication_ldap_simple SONAME 'authentication_ldap_simple.so';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL authentication_ldap_simple_bind_base_dn='DC=WINDOWS,DC=DOMAIN';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL authentication_ldap_simple_group_search_attr='';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'patrick'@'%'
    ->             IDENTIFIED WITH authentication_ldap_simple
    ->             BY 'CN=patrick,CN=Users,DC=WINDOWS,DC=DOMAIN';
Query OK, 0 rows affected (0.00 sec)

I won’t repeat the tests for correct and incorrect password handling, they still behave as before, but I will show you behaviour without specifying a password.

[patrick@WIN-CLIENT] C:\> mysql                                  `
>>                               --host=lnx-mysql8.windows.domain `
>>                               --user=patrick                   `
>>                               --enable-cleartext-plugin
ERROR 1045 (28000): Access denied for user 'patrick'@'WIN-CLIENT.windows.domain' (using password: YES)
[patrick@WIN-CLIENT] C:\>

I’m not sure if there is anyone using authentication_ldap_simple due to it sending passwords unencrypted between database and domain controller, but if there is, I’d suggest checking whether you are susceptible to this issue and if so applying latest patchset ASAP.

I’m also not sure if this is specific to LDAP authentication with Active Directory or other Directory Services are affected.  I also wonder whether authentication_ldap_sasl is affected, but I don’t have configuration to check that out.