mysql_clear_password & authentication_ldap_simple password encryption

Preparing for my talk at the HK Open Source Conference I wanted to confirm some of the things I had read about these plugins, mostly from Matthias Crauwels excellent article on the subject.

My lab environment consists of Windows 2016 Domain controller and client machines, with MySQL 8 running on Oracle Linux 7.

First I configure the database to use the server-side plugin, configure the plugin to point to the domain controller, and create a database user associated via the plugin with 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 I successfully connect from my Windows client to this database account passing in my Windows credentials.

[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

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>

Checking the network packets between the Windows client and database server, observe that (almost all) the traffic is encrypted with a self-signed certificate.

[root@lnx-mysql8 ~]# tcpflow -c port 3306
tcpflow: listening on enp0s3
192.168.002.004.03306-192.168.002.002.49801: U
<UdYcaching_sha2_passwordLR%
192.168.002.002.49801-192.168.002.004.03306:
192.168.002.002.49801-192.168.002.004.03306: ANi<l[}y~yn+,/0#'$(g@kj
239-.<>%=h&/05612?)i*17
U


0H1212013500Z0@1>0<U5MySQL_Server_8.0.13_Auto_Generated_Server_Certificate0"0
DH<@_Cn%VR9A)[QLMVjAYe1Kju2\)Wk7SqOG02Xl;>n)i&gjV:/,J^f("qBDH8kW:lKQ+B 3;K^!.$5BxJ=0XWD,00U00
^uBey@/e;m4sQ
lJDiU{?s;[72FeLkS
p{WFXdr**yLPp'ij_(z`E"{Lxu|1DX$Jp`w;Ti<}BlLA@?
0H1212013500Z0<1:08U1MySQL_Server_8.0.13_Auto_Generated_CA_Certificate0"0 2 |s?jupq&GG]5`-2$1'$,AT"`OA/^d((~:n0Z'~O?$+az]y(De"5Klwiv(B"ST~rE0'7qIWZc%R$D8v9MzF|\blAK00U00 9()~I?-Nq(#LRACVU>eOWB["IOm$\]fvNa7W\m?
dYt^dT)-Y&UWUBlnmOA}?%YW>D
C*<*f,OD^^G"
'#-F*/=@=]mnDRVQ,RG(a|la^!fH)5"{EbynK4{q:CiV%#(f_hr_/-X~S/:
(@C{jB&{%ddU-F0fG/2t_aPUw\A%&+:{K"t(+}Q~+|#XxJNS\XhDz);=79.o{Q<sp??D^0\v:6[a|$ooBZ(K0Zt}. WjeoWOi4AE]YkQXUH0E2;'US-/Dw0[4@&3]]/c`GCEEzmU@oG2{%Z&`0!A}]A[.:m 7q@w]Rv3-nKZP$N''}yjKssvFvc:O$'rU)f(=@,Wup,>b+xF[Lv6;
192.168.002.004.03306-192.168.002.002.49801: ,
{xsGdN>
XEUyH`?T'd7fI JwN%:eq2#;y'Nh(hm}c$dG'zs
zT@(W$]#Wm4nw2t7(`X-5lK'SXwk0qS3
192.168.002.002.49801-192.168.002.004.03306: =@,Wa{q%)}aH9;.%~k$hoKI+a8\B}@NR@Dp`JFDwK\(1%9 %5XqO f:Pgmvi|>N^&=k/~egl]i@s;p
\8&?4AKg>r63E
192.168.002.004.03306-192.168.002.002.49801: H`XmX2:@~Oq)BY-|<`GS6ew 192.168.002.002.49801-192.168.002.004.03306: 0=@,W.la:QC0,%>G~L6
192.168.002.004.03306-192.168.002.002.49801: #`X-$~v4_L3
|s
192.168.002.002.49801-192.168.002.004.03306: ==@,W.BFu?6'F6|P)EC]?%n)ww
nSHK*+@6FS(9l|Y2>apy;-192.168.002.002.49801: |`X,

Note however it is possible to disable this encryption with the –ssl-mode=disabled flag.

[patrick@WIN-CLIENT] C:\> mysql --host=lnx-mysql8.windows.domain `
>>                              --user=patrick                   `
>>                              --password=Password123           `
>>                              --enable-cleartext-plugin        `
>>                              --ssl-mode=disabled
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 64
Server version: 8.0.13-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>

Observe that the password is now transmitted unencrypted during authentication

[root@lnx-mysql8 ~]# tcpflow -c port 3306
tcpflow: listening on enp0s3
192.168.002.004.03306-192.168.002.002.49899: U
8.0.13-commercialAi95M2)P3(f}3caching_sha2_password
192.168.002.002.49899-192.168.002.004.03306: patrick lv
J5T{W0J-rcaching_sha2_passwordq_pid172program_namemysql_client_namelibmysql_thread2640_client_version8.0.13_osWin64_platformx86_64
192.168.002.004.03306-192.168.002.002.49899: mysql_clear_password
192.168.002.002.49899-192.168.002.004.03306: Password123
192.168.002.004.03306-192.168.002.002.49899:
192.168.002.002.49899-192.168.002.004.03306: !select @@version_comment limit 1
192.168.002.004.03306-192.168.002.002.49899: 'def@@version_comment$%$MySQL Enterprise Server - Commercial

Such connections can be prevented with the require_secure_transport=ON variable

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

In this case connections which disable encryption will be rejected

[patrick@WIN-CLIENT] C:\> mysql --host=lnx-mysql8.windows.domain `
>>                              --user=patrick                   `
>>                              --password=Password123           `
>>                              --enable-cleartext-plugin        `
>>                              --ssl-mode=disabled
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
[patrick@WIN-CLIENT] C:\>

Unfortunately even though the connection is rejected, the password is still transmitted unencrypted during authentication process

[root@lnx-mysql8 ~]# tcpflow -c port 3306
tcpflow: listening on enp0s3
192.168.002.004.03306-192.168.002.002.49867: U
8.0.13-commercial@=l?K/sbq!Qa&sG{{Bcaching_sha2_password
192.168.002.002.49867-192.168.002.004.03306: patrick Au'"?..`%85n]~caching_sha2_passwordr_pid3416program_namemysql_client_namelibmysql_thread4992_client_version8.0.13_osWin64_platformx86_64
192.168.002.004.03306-192.168.002.002.49867: mysql_clear_password
192.168.002.002.49867-192.168.002.004.03306: Password123
192.168.002.004.03306-192.168.002.002.49867: aW#HY000Connections using insecure transport are prohibited while --require_secure_transport=ON.

However if we re-run the original connection attempt (with encrypted traffic between database client and server)  capturing the network traffic between database and domain controller, we can see password is transferred unencyrpted as this point

[root@lnx-mysql8 .passwords]# tcpflow -c port 389
tcpflow: listening on enp0s3
192.168.002.004.43068-192.168.002.001.00389: 0PcK

NtVer0mainWINDOWS.DOMAIN
netlogon
192.168.002.001.00389-192.168.002.004.43070: 0d00znetlogon1jhl)X0K4fWINDOWSDOMAINWIN-DCWINDOWSWIN-DCDefault-First-Site-NameE0e

192.168.002.004.43070-192.168.002.001.00389: 0B
192.168.002.004.42990-192.168.002.001.00389: 0?`:(CN=patrick,CN=Users,DC=WINDOWS,DC=DOMAINPassword123
192.168.002.001.00389-192.168.002.004.42990: 0a

The documentation alludes to some of the restrictions, although to my mind it seems to focus on the encryption between database client and server, but not between database server and Domain Controller (unless I’ve missed something).

The server-side authentication_ldap_simple plugin performs simple LDAP authentication. For connections by accounts that use this plugin, client programs use the client-side mysql_clear_password plugin, which sends the password to the server in clear text. No password hashing or encryption is used, so a secure connection between the MySQL client and server is recommended to prevent password exposure.

Based on the above observations, as I was expecting, this plugin combination is not really suitable for implementation for any environment which takes security seriously.

If that wasn’t enough reason to avoid the plugin, during the course of my investigation I discovered some other surprising behavior.  Based on discussion with Oracle support this should be resolved in the next release of SQL Server, so I’ll wait till that is released before sharing.