how to setup pycharm 2019 on Windows to connect to MySQL with Kerberos authentication

Answered

I want to be able to connect to a MySQL database using PyCharm.

When I attempt to do this, I get User / Password prompt. If I enter my username but omit the password then I get the following error: 

The specified database user/password combination is rejected: com.mysql.cj.exceptions.WrongArgumentException: Unable to load authentication plugin 'auth_gssapi_client'.

I can do this with HeidiSQL, which I believe uses my Windows Kerberos ticket to authenticate my username with the database server when connecting. How can I configure PyCharm to do the same, please?

14 comments
Comment actions Permalink

vasily chernov - you've answered some similar questions to this, which I've read extensively, but I'm still stuck here. Would you have any specific instructions, please?

I have tried the '-Djava.security.krb5.realm=realm -Djava.security.krb5.kdc=kdc' VM options suggested in https://intellij-support.jetbrains.com/hc/en-us/community/posts/115000757404-DataGrip-Mac-Kerberos-Authentication-with-PostgreSQL

The log shows the error: Unable to load authentication plugin 'auth_gssapi_client'

0
Comment actions Permalink

Update: I can see that I do have an auth_gssapi_client.dll file in multiple locations on my Windows machine.

As I understand it, this is what should happen:

  1. Client talks to database server with a username and no password
  2. Server rejects this, and tells client to authenticate using a specific plugin: auth_gssapi_client
  3. Client is then meant to use this particular plugin in order to use Kerberos credentials

At this point, I think that HeidiSQL is able to use the plugin but PyCharm is not. 

I have tried editing my PATH and restarting PyCharm as well as placing a copy of the dll in my existing PATH, but neither of these options seem to work. Does this sound relevant, or am I on completely the wrong track here?

0
Comment actions Permalink

Trying a different approach, some sources say that the MySQL JDBC does not support kerberos authentication but the MariaDb driver does. 

Attempting this, I've configured a MariaDb data source, and now instead get the following error:

[28000][1045] GSS-API authentication exception javax.security.auth.login.LoginException: Unable to obtain Principal Name for authentication.

This sounds ... potentially promising. I've been attempting to pass my Service Principal Name through using the Advanced > servicePrincipalName options, but it doesn't seem to have any effect and I continue to get that exact same error.

0
Comment actions Permalink

>Attempting this, I've configured a MariaDb data source, and now instead get the following error:

As per MariaDB jdbc driver instructions make sure you have set all Java System Properties (in Advanced VM Options field), including java.security.auth.login.config property if the configuration differs from the default which is specified in driver documentation. Also install latest Java JCE packages into the JDK that is used for connection (which is the JDK IDE uses to run under). If it does not work with JDK 11 (which is used by default for running the IDE) try downloading and switching to 1.8 JDK via Choose Runtime plug-in as explained in KB article.

0
Comment actions Permalink

I'd started typing an "other things ruled out" section but hadn't posted it - looks like we're on a similar track: 

MariaDb documents (https://mariadb.com/kb/en/library/gssapi-authentication-with-mariadb-connector-j/) say that "you may have to install the Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy File", but I've confirmed that the version of Pycharm I have installed (JDK 11, bundled) contains the Unlimited policy and in the \AppData\Local\JetBrains\PyCharm 2019.2.3\jbr\conf\security file the policy is already set to 'unlimited'. So that shouldn't be it.

I'll have a further look at Java System Properties - though am struggling to (1) find out what they should be set to (where *is* my kerberos conf file?!) and (2) don't understand how HeidiSQL works with no apparent further configuration and PyCharm does not - wish that the configurations were more similar so that a side-by-side comparison were easier.

0
Comment actions Permalink

>don't understand how HeidiSQL works with no apparent further configuration

Does it use the same jdbc drivers?

Check also Windows limitations section.

0
Comment actions Permalink

I'm looking through the log and see: 

2019-12-11 08:38:26,832 [ 762259] INFO - urce.DatabaseConnectionManager - Connecting as: <me>
2019-12-11 08:38:26,832 [ 762259] INFO - urce.DatabaseConnectionManager - Connecting to: jdbc:<correct details>/
2019-12-11 08:38:26,839 [ 762266] INFO - ution.rmi.RemoteProcessSupport - Debug is true storeKey false useTicketCache true useKeyTab false doNotPrompt true ticketCache is null isInitiator true KeyTab is null refreshKrb5Config is false principal is null tryFirstPass is false useFirstPass is false storePass is false clearPass is false
2019-12-11 08:38:26,839 [ 762266] INFO - ution.rmi.RemoteProcessSupport - Acquire TGT from Cache
2019-12-11 08:38:26,841 [ 762268] INFO - ution.rmi.RemoteProcessSupport - Principal is null
2019-12-11 08:38:26,841 [ 762268] INFO - ution.rmi.RemoteProcessSupport - null credentials from Ticket Cache
2019-12-11 08:38:26,841 [ 762268] INFO - ution.rmi.RemoteProcessSupport - [Krb5LoginModule] authentication failed
2019-12-11 08:38:26,841 [ 762268] INFO - ution.rmi.RemoteProcessSupport - Unable to obtain Principal Name for authentication
2019-12-11 08:38:26,845 [ 762272] WARN - urce.DatabaseConnectionManager - Connecting to: jdbc:<correct details>/
2019-12-11 08:38:26,845 [ 762272] WARN - urce.DatabaseConnectionManager - [28000][1045] GSS-API authentication exception

 

So it looks like an issue (the issue?) is the 'Null' principal. From the MariaDb docs there are some known limitations stated around Windows where the registry must be edited in order for the default GSSAPI implementation to work. I don't have the ability to do that, so that option sucks. Alternatively the MariaDb docs suggest using WAFFLE ... which is proving fiddling for me to use as work blocks GitHub, so that's a problem. Also

Andrey Dernov - any suggestions, anything I've obviously missed?

0
Comment actions Permalink

Andrey Dernov - I've got a copy of WAFFLE and can try to use that as an alternate GSSAPI authentication mechanism. To do this, I need to put the .jar files into the CLASSPATH.

Can I do this using PyCharm's Data Sources and Drivers -> Project Data Source -> Advanced -> "VM options" field?

(I've tried -Dclasspath=path\to\file which didn't work). And how can I check that the 'options' that I'm feeding in are having any effect?

0
Comment actions Permalink

>>don't understand how HeidiSQL works with no apparent further configuration

>Does it use the same jdbc drivers?

Not sure: the HeidiSQL logs only say

/* 2019-12-11 08:59:10 [new_uat] */ /* Connecting to <server> via MariaDB (TCP/IP), username <user>, using password: No ... */

Though I do know that it uses a 'dialog.dll' library to do some magic around authentication. 

>Check also Windows limitations section.

I've looked through the Windows limitations sections, and they're sending me down the how-to-set-up-WAFFLE-and-check-that-it-works path at the moment...

0
Comment actions Permalink

vasily chernov / Andrey Dernov

Bottom line: I'd *love* to be able to connect from my Windows desktop using windows credentials to a MySQL / MariaDB database. This seems like a pretty standard workflow, so I'm certain that other people will have this use-case. 

The way that PyCharm works out of the box without requiring Admin access to the host PC is awesome, and it would be great if the steps to get the connection can be similarly set up with regular non-admin access.

This stuff is thoroughly mysterious to me, so I appreciate any and all help here. I've sunk a bunch of time into this already, and it would be superb to get a working result (and a set of steps that could help others afterwards).

0
Comment actions Permalink

Right - got this working!

For posterity:

  1. we got all of the .jar files required for WAFFLE (as specified in the MariaDb documentation: https://mariadb.com/kb/en/library/gssapi-authentication-with-mariadb-connector-j/#Windowsnativejavaimplementation)
  2. we placed them into the PyCharm subdirectory at the level of the MariaDB Connector: C:\Users\<ny username>\.PyCharm2019.2\config\jdbc-drivers\MariaDB Connector J\2.4.1

... and now I can connect!

2
Comment actions Permalink

Sorry for a delayed reply.

>we got all of the .jar files required for WAFFLE (as specified in the MariaDb documentation

Was going to suggest a similar thing: you can add all required libraries from IDE interface in jdbc driver settings -> Driver files section:

0
Comment actions Permalink

Nice - thank you! I'm writing up an internal how-to ('cause my colleagues will probably want *exactly* the same access that I have), so will use your "Add Driver Files" option there. 

Hope that this helps anyone else wrestling with MySQL / MariaDb access also! The PyCharm Database tooling is super-nice and it'll be good to be able to leverage it against these types of database.

0
Comment actions Permalink

Almost one year on... and it looks like PyCharm 2020.x has started to tighten up some security settings.

Problem: I was starting to get the good old "Unable to obtain Principal Name for authentication" message again.

Fix: adding *all* of the WAFFLE Custom JARs to the "Driver Files" section of the "DataSources and Drivers" configuration for MariaDB.

 

 

0

Please sign in to leave a comment.