Navigation

Connect from the MySQL Client

Authentication

New in version 2.2: The MongoDB Connector for BI now offers MySQL authentication plugins allowing you to connect with authentication or TLS/SSL.

There are two options for authenticating your connection:

  1. Use an authentication plugin with either the SCRAM-SHA-1 or PLAIN authentication mechanism.

    Important

    The use of TLS/SSL is recommended as:

    • The SCRAM-SHA-1 mechanism hashes the passwords in the client plugin. However, all other data is in cleartext.
    • The PLAIN mechanism sends the password in cleartext.

    See C Authentication Plugin or Install the JDBC Authentication Plugin for more information on the plugins.

  2. Provide cleartext credentials for simple username/password authentication against a MongoDB database. This method uses MySQL’s built-in mysql_clear_password plugin.

    Note

    Authentication with mysql_clear_password requires the use of TLS/SSL. See Connect from MySQL with Authentication and TLS/SSL for more information.

The MongoDB Connector for BI requires authentication when running with --auth. When the MongoDB Connector for BI receives a connection with authentication credentials from a client, it passes those credentials through to the underlying MongoDB instance.

You can specify the following authentication options after your username as URI-style query parameters:

Connection Option Description
source

Specify the name of the database which stores the user’s credentials. If you do not specify this option, the MongoDB Connector for BI will default to the current database associated with the MySQL connection.

For authentication mechanisms such as PLAIN (LDAP) that delegate credential storage to other services, set the source value to $external.

mechanism

Specify the mechanism that the MongoDB Connector for BI should use to authenticate the connection. Accepted values include:

LDAP requires MongoDB Enterprise. Set the source to $external when using LDAP.

Note

Neither Kerberos nor x.509 are supported.

Example

To authenticate as user grace with the LDAP authentication mechanism, use the following username:

grace?mechanism=PLAIN&source=$external

You can use the command-line MySQL client to connect to the MongoDB Connector for BI.

Connect from MySQL without Authentication or TLS/SSL

To connect to a mongosqld instance listening on the MySQL default port 3307, run the following command:

macOS and Linux

mysql --protocol tcp --port 3307

32-bit Windows

"C:\Program Files (x86)\MySQL\MySQL Server 5.7\bin\mysql.exe" ^
  --protocol tcp --port 3307

64-bit Windows

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" ^
  --protocol tcp --port 3307

Connect from MySQL with Authentication

Install the C Authentication Plugin as described in Install the C Authentication Plugin.

The following example uses the C authentication plugin to connect to a mongosqld instance listening on port 3307 as user reportsUser. The MySQL shell prompts for the password after the command has been entered.

mysql '--user=reportsUser?source=admin' --default-auth=mongosql_auth -p

Note

This example assumes that the authentication plugin file mongosql_auth.so is located in the default MySQL plugin folder. The location of the plugin folder varies by platform, but you can locate it by running the following command:

mysql_config --plugindir

mysql_config.pl can find the plugin directory only on macOS and Linux hosts.

32-bit Windows

"C:\Program Files (x86)\MySQL\MySQL Server 5.7\bin\mysql.exe" ^
  '--user=reportsUser?source=admin' ^
  --default-auth=mongosql_auth -p

64-bit Windows

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" ^
  '--user=reportsUser?source=admin' ^
  --default-auth=mongosql_auth -p

Connect from MySQL with Authentication and TLS/SSL

Important

The binary distribution of MySQL Community uses the yaSSL SSL library to encrypt connections. MySQL Enterprise uses OpenSSL which is compatible with MongoDB Connector for BI. Use MySQL Enterprise to connect to BI Connector over TLS / SSL.

To connect to a mongosqld instance listening on port 3307, as user grace using authentication mechanism PLAIN, and using specific TLS/SSL CA and x.509 certificates, run the following command:

macOS and Linux

mysql --enable-cleartext-plugin --protocol tcp --port 3307 \
  -u 'grace?mechanism=PLAIN&source=$external' \
  --ssl-ca=/path_to_the_CAcert/ca.crt \
  --ssl-key=/path_to_my_certificate_key/mysql.key \
  --ssl-cert=/path_to_my_client_certificate/mysql.crt \
  -p

32-bit Windows

"C:\Program Files (x86)\MySQL\MySQL Server 5.7\bin\mysql.exe" ^
  --enable-cleartext-plugin --protocol tcp --port 3307 ^
  -u 'grace?mechanism=PLAIN&source=$external' ^
  --ssl-ca=X:\path_to_the_CAcert\ca.crt ^
  --ssl-key=X:\path_to_my_certificate_key\mysql.key ^
  --ssl-cert=X:\path_to_my_client_certificate\mysql.crt ^
  -p

64-bit Windows

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" ^
  --enable-cleartext-plugin --protocol tcp --port 3307 ^
  -u 'grace?mechanism=PLAIN&source=$external' ^
  --ssl-ca=X:\path_to_the_CAcert\ca.crt ^
  --ssl-key=X:\path_to_my_certificate_key\mysql.key ^
  --ssl-cert=X:\path_to_my_client_certificate\mysql.crt ^
  -p

If using the $external authentication source, wrap your username in single quotes or escape the $ character with a backslash to prevent your shell from performing interpolation.

Authentication requires your client to send your password in plain text. To send the password in plain text, either:

  • Use the --enable-cleartext-plugin option shown in the previous example, or
  • Set the environment variable LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1.

See the MySQL documentation for additional details on enabling the MySQL cleartext plugin.