Sequel-ace: LDAP Error: Authentication plugin 'mysql_clear_password' cannot be loaded: plugin not enabled

[rgill-jama]

• Sequel Ace Version: 2.1.8
• macOS Version: 10.15.6
• MySQL Version: 8.0.1

Description
Connecting to our MySQL 8 DB using LDAP. Keep getting this error "MySQL said: Authentication plugin 'mysql_clear_password' cannot be loaded: plugin not enabled", when connecting with our LDAP account. When we connect using the root account, it works fine. We do not see this issue with other tools we use in our company.

We have tried the latest Beta version as well and we are still seeing the same issue. In my profile. I have set:

export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=y

How can we get this tool to work with LDAP so our engineers can use this. They prefer to use this tool over the other ones.

[robinkunde]

Hi there, thank you for reporting this issue. I'm currently researching the history of cleartext password handling in the code base. I'm also trying to figure out how to test any potential fix. It doesn't look like LDAP auth for MySQL is trivial to set up, nor have I found a Docker image yet.

You said you have the env var set in your profile. Do you mean you .bash_profile/.zprofile file? If so, do you also launch Sequel Ace from the command line? If not, could you try launching it like this:

$ export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1
$ open -a "Sequel Ace"

[Jason-Morcos]

I think it may be as simple as adding the cleartext bundle into Sequel Ace. If we add cleartext, we may want to add dialog too (https://github.com/Sequel-Ace/Sequel-Ace/issues/201). The hard part has to do with how we would have to pass passwords properly to those plugins, I'm not quite sure how that works.

There was a decision from Sequel Pro and it looks like they chose not to support the plugin for security purposes (https://github.com/sequelpro/sequelpro/pull/2393).

Additionally, the cleartext plugin may be the cause of our RDS issues (https://github.com/sequelpro/sequelpro/issues/2247#issuecomment-363791300 / https://github.com/Sequel-Ace/Sequel-Ace/issues/202)

And this may be relevant too https://github.com/Sequel-Ace/Sequel-Ace/issues/285

Overall, we'll just have to be _very_ careful and intentional about not exposing any potential security vulnerabilities

[rgill-jama]

You said you have the env var set in your profile. Do you mean you .bash_profile/.zprofile file? If so, do you also launch Sequel Ace from the command line? If not, could you try launching it like this:

$ export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1
$ open -a "Sequel Ace"

Thanks for getting back to me. Yes it does seem like a simple fix. Many tools support this already.

Yes I'm using the bash profile. But I launch it from the applications folder on my mac. We don't open it via command line.
Do you still want me to try the command? And is the value supposed to be 1 or y?

[rgill-jama]

I think it may be as simple as adding the cleartext bundle into Sequel Ace. If we add cleartext, we may want to add dialog too (#201). The hard part has to do with how we would have to pass passwords properly to those plugins, I'm not quite sure how that works.

There was a decision from Sequel Pro and it looks like they chose not to support the plugin for security purposes (sequelpro/sequelpro#2393).

Additionally, the cleartext plugin may be the cause of our RDS issues (sequelpro/sequelpro#2247 (comment) / #202)

And this may be relevant too #285

Overall, we'll just have to be _very_ careful and intentional about not exposing any potential security vulnerabilities

Thanks. I'll look these over in the morning. Hopefully it's something simple that can be added like a checkbox to enable clear text.

[robinkunde]

You said you have the env var set in your profile. Do you mean you .bash_profile/.zprofile file? If so, do you also launch Sequel Ace from the command line? If not, could you try launching it like this:

$ export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1
$ open -a "Sequel Ace"

Thanks for getting back to me. Yes it does seem like a simple fix. Many tools support this already.

Yes I'm using the bash profile. But I launch it from the applications folder on my mac. We don't open it via command line.
Do you still want me to try the command? And is the value supposed to be 1 or y?

Yes, please give it a try with the value "1". I just want to confirm whether this workaround works at all currently, since I don't have the means test it myself right now.

[rgill-jama]

@robinkunde - That worked!!!!!!
So now that it works, how can we make it part of the app? I am not sure I can convince out IT to make the unofficial app part of our library. They would prefer an official released version (from the app store I believe).

Thanks for the tip FYI!!!

[rgill-jama]

Wh

@robinkunde - That worked!!!!!!
So now that it works, how can we make it part of the app? I am not sure I can convince out IT to make the unofficial app part of our library. They would prefer an official released version (from the app store I believe).

Thanks for the tip FYI!!!

When I open the app directly from the applications folder, it does not work. In my .bash_profile, I did set the export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1 variable.

[robinkunde]

Thanks confirming that worked, it helped a lot. Here's what I think is the current state of things:

We use the libmysqlclient library to establish connections to the database server. For security reasons, by default it disables sending password over the network in the clear. Unfortunately, some authentication schemes like LDAP need the cleartext form of the password because they use their own password encryption/hashing scheme. (https://dev.mysql.com/doc/refman/5.6/en/cleartext-pluggable-authentication.html)
One way of explicitly enabling this option is to set the environment variable. The reason adding the export to .bash_profiledoesn't work when launching through the GUI (Finder, etc) is that .bash_profile is only executed when you open a terminal window.

Using the environment variable at all is rather cumbersome, so I think we should do what other tools like MySQLWorkbench do, and provide a per-connection option to explicitly enable cleartext. We would then implement it through a call to libmysqlclient when setting up the connection.

Relatedly, a few years ago the Sequel Pro team decided to disable keychain access if the environment variable is set (https://github.com/sequelpro/sequelpro/issues/2437) (#285). I don't see a good justification for this behavior. The env var doesn't influence how Sequel Ace interacts with the Keychain, and libmysqlclient will only send the password in cleartext if it's actually required. I think we should remove this behavior when we implement the connection setting, unless someone has a good reason why we shouldn't.

[Jason-Morcos]

Relatedly, a few years ago the Sequel Pro team decided to disable keychain access if the environment variable is set (sequelpro/sequelpro#2437) (#285). I don't see a good justification for this behavior. The env var doesn't influence how Sequel Ace interacts with the Keychain, and libmysqlclient will only send the password in cleartext if it's actually required. I think we should remove this behavior when we implement the connection setting, unless someone has a good reason why we shouldn't.

I think the worry is a malicious server could request cleartext and we'd send a password in cleartext when we shouldn't. From the keychain.
However, if we make it a connection setting to allow cleartext, we should allow sending the password from the keychain via cleartext IFF that connection setting is true (in other words, still disable keychain if request is cleartext unless the connection setting for allowing cleartext is true). Does that make sense? Basically keep the security guard implemented as is _except_ for when the user specifically enables cleartext

I've also been thinking we may need to tweak the UI for setting up connections. We've started adding connection settings and just adding more things to the main view. We could still do that again for prefs to allow cleartext and dialog, but at a certain point it may start to get kinda messy

[robinkunde]

The UI is definitely getting crowded. It'd be easier to if we could use NSStackView or NSGridView...

[luzanneweda]

Hey!

Just wondering, any updates about this? We are moving to Azure Databases and we need this option before we can connect. The workaround 'works', but it is not really user friendly right now. Is this somewhere on the schedule to be fixed?

[Jason-Morcos]

No updates yet, happy to review a PR if someone would like to submit a PR optionally enabling the cleartext plugin. Does the suggested workaround from @robinkunde above work for you @SumBeam?

https://github.com/Sequel-Ace/Sequel-Ace/issues/368#issuecomment-698705327

[luzanneweda]

No updates yet, happy to review a PR if someone would like to submit a PR optionally enabling the cleartext plugin. Does the suggested workaround from @robinkunde above work for you @SumBeam?

https://github.com/Sequel-Ace/Sequel-Ace/issues/368#issuecomment-698705327

Yeah the workaround is working for me. I think I can try to implement this and need some help to run this on my machine 😀

10+ colleagues are using this and I don't want them to use this workaround every day.

Do we have an idea how to solve it? Just add the extra checkbox or do you want to do more adjustments?

[Jason-Morcos]

I think the checkbox is fine! Or, we could enable it for everyone if there's not negative security implications there. Really depends on if this can be dangerous for security

Perhaps we add a MySQL Options tab to the app's preferences and allow this setting to be configured globally there

[luzanneweda]

Security wise I would just keep it at connection level, just like MySQL Workbench.

I'll check it out this weekend!

Just a random off-topic question, is there a slack channel somewhere?

[Jason-Morcos]

Security wise I would just keep it at connection level, just like MySQL Workbench.

I'll check it out this weekend!

Just a random off-topic question, is there a slack channel somewhere?

There is a Slack - fill out the contact us form on the moballo website and include your email address and I'll get you an invite tomorrow! https://moballo.com/contact-us/

[Jason-Morcos]

Leaving open until released so people can see that it's already an issue, but fixed and pending release