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.
Pingback: Let the wrong one in! MySQL AD Authentication with authentication_ldap_simple | jolliffe.hk
Pingback: Adding a little TLS complexity to authentication_ldap_simple | jolliffe.hk