mongosqld

Starting mongosqld with a Schema File

Use the --schema option to specify a schema file when starting mongosqld.

mongosqld --schema /path/to/schema-file.drdl

Use mongodrdl to create a schema file from a MongoDB instance.

Starting mongosqld with a Schema Database

Use the --schemaSource option to specify a database to store schema information.

mongosqld --schemaSource sampleDb

Starting mongosqld with Specified Namespaces

Use the --sampleNamespaces option to specify databases and collections for mongosqld to sample data from to create the schema.

mongosqld --sampleNamespaces contacts.addresses

See additional usage examples below.

Core Options

--help

Returns information on the options and use of mongosqld.

--addr

Default: 127.0.0.1:3307

Specifies the host address to listen on.

--version

Returns the mongosqld release number.

--config <path>

Specifies the path to a configuration file.

--mongo-uri <uri>

Default: mongodb://localhost:27017

Specifies a MongoDB connection string to connect to.

The --mongo-uri option supports the following options within the connection string:

• readPreference

• readPreferenceTags

• replicaSet

• connect

For more information on these URI options see Read Preference Options and Replica Set Option.

For options set in the Mongo URI not included in the list above, use the equivalent mongosqld option. For the complete list of mongosqld options, see Command Line Options.

Note

Instead of specifying a username and password in your connection string, run mongosqld with the --auth option to direct mongosqld to pass the authentication credentials provided by the SQL client to the MongoDB server.

Similarly, instead of enabling ssl in the connection string, run mongosqld with --mongo-ssl.

To disable automatic replica set server discovery logic and force a connection to the specified server, use the connect=direct option.

mongosqld --mongo-uri "mongodb://<hostname>:<port>/?connect=direct"

URI options not in the list above nor in the list of supported mongosqld options are not supported.

--mongo-versionCompatibility <version-number>

Restricts mongosqld to using features that the specified version of MongoDB supports. Only necessary when used with replica sets in which members use different MongoDB versions or when performing a rolling upgrade of MongoDB. Only supports MongoDB version 3.2 or later.

For example, if your replica set contains members running MongoDB 3.2 and other members running MongoDB 3.4, set the following option to restrict mongosqld to only use features supported by MongoDB 3.2:

mongosqld --mongo-versionCompatibility 3.2

--maxVarcharLength <length>

New in version 2.2.

Specifies the maximum length, in characters, for all varchar fields. If mongosqld encounters a string that is longer than the maximum length, mongosqld truncates it to the maximum length and logs a warning.

--mongo-username <username>, -u <username>

New in version 2.3.

Specifies the authentication username to use for schema discovery. Only required if --auth is enabled. The user specified by --mongo-username must be a valid MongoDB user with the listDatabases privilege. See mongosqld User Permissions.

--mongo-password <password>, -p <password>

New in version 2.3.

Specifies the authentication password to use for schema discovery. Only required if --auth is enabled. Use in conjunction with --mongo-username.

--mongo-authenticationSource <auth-db-name>

Default: admin

New in version 2.3.

Specifies the database that holds the credentials for the schema discovery user. Only available if --auth is enabled. Use in conjunction with credential options --mongo-username and --mongo-password.

--mongo-authenticationMechanism <authMechanism>

Default: SCRAM-SHA-1

New in version 2.3.

Specifies the authentication mechanism to use for schema discovery. Only available if --auth is enabled. Use in conjunction with credential options --mongo-username and --mongo-password.

Value	Description
SCRAM-SHA-1	RFC 5802 standard Salted Challenge Response Authentication Mechanism using the SHA1 hash function.
SCRAM-SHA-256	New in version 2.6: RFC 7677 standard Salted Challenge Response Authentication Mechanism using the SHA2 hash function.
PLAIN (LDAP SASL)	External authentication using LDAP. You can also use PLAIN for authenticating in-database users. PLAIN transmits passwords in plain text. This mechanism is available only in MongoDB Enterprise.
GSSAPI (Kerberos)	External authentication using Kerberos. This mechanism is available only in MongoDB Enterprise.

Schema Options

--schema <filename>

Specifies the path to a schema file or the schema directory.

--schemaDirectory <directoryname>

Deprecated since version 2.2.

Use --schema instead.

--sampleNamespaces <db.collection>

New in version 2.5.

--sampleNamespaces specifies a database and collection for either inclusion or exclusion from the data sampling process which creates the schema. It is also possible to specify multiple collections from a single databases, or multiple collections from multiple databases. See examples below.

If you do not use the --sampleNamespaces option or the --schema option, mongosqld samples data from all available MongoDB databases and collections except the admin and local databases.

--schemaMode <[custom|auto]>

Default: custom

New in version 2.11.

Configures the sampling mode of mongosqld. Must be used with the --schemaSource option. The following values determine the sampling behavior:

Value	--schemaMode Behavior
custom	mongosqld reads a stored schema from the MongoDB database specified by the --schemaSource option.
auto	mongosqld samples the schema and writes the schema data to the MongoDB database specified by the --schemaSource option.

For more information on configuring the sample mode, see the Sampling Mode Reference Chart.

Important

If mongosqld has authentication enabled, the authenticated user must have the write privilege on the specified --schemaSource database. See Built-In Roles for more information about the readWrite role.

--schemaSource <db-name>

New in version 2.11.

Required whenever the --schemaMode is set. Specifies the database where the schema information is stored.

Note

If you do not specify any of the --schema, --schemaMode, and --schemaSource options, mongosqld holds its schema in memory.

To learn more about sampling modes, see the Sampling Mode Reference Chart.

--schemaName <db-name>

Default: defaultSchema

New in version 2.11.

Optional. The name of the schema to load from or write to the --schemaSource database. Specifying schema names allows you to store multiple schemas in the --schemaSource database. The behavior depends on the value of --schemaMode:

--schemaMode Value	--schemaName Behavior
custom	The name of the schema to load from the database specified by the --schemaSource option.
auto	The name of the schema to write to the --schemaSource database after the BI Connector samples the schema on startup.

Important

If you upload a custom schema, you must store it with its specified name, using name-schema, and then specify this name to the mongosqld with --schemaName. If you don't store the schema's name when you upload it, the schema name defaults to defaultSchema. If the schema's name doesn't exist, this results in an error from mongosqld similar to the following: MongoDB schema not yet available. Error initializing schema: no schema found for name.

To learn more about sampling modes, see the Sampling Mode Reference Chart.

--sampleSize <number>

Default: 1000

New in version 2.3.

The number of documents per namespace to sample when gathering schema information.

Set --sampleSize to 0 to include all documents in the specified namespace when building the schema. If you do not specify a namespace, setting --sampleSize to 0 causes mongosqld to consider all documents in all databases (excluding local, admin, and system) when building the schema. See an example below.

--schemaRefreshIntervalSecs <number>

Default: 0

Changed in version 2.11: Renamed --sampleRefreshIntervalSecs to --schemaRefreshIntervalSecs

The interval in seconds at which mongosqld re-samples data to create its schema. The default value is 0, which means that after the initial sampling no automatic re-sampling occurs. The specified value must be a positive integer.

To force a one-time update of the schema, use the FLUSH SAMPLE command from your SQL client.

--uuidSubtype3Encoding <old|csharp|java>, -b <old|csharp|java>

Specify the encoding used to generate UUID binary subtype 3. Choose one of the following values:

• old: Old BSON binary subtype representation

• csharp: The C#/.NET legacy UUID representation

• java: The Java legacy UUID representation

--prejoin

New in version 2.6.

Schema option for combining array and non-array data into a single table.

MongoDB documents which contain arrays are normally translated into tabular format with separate tables for array data and non-array data. Consider a MongoDB collection named test with the following document:

{ "_id" : 1, "a" : 3, "b" : [ "orange", "apple", "pear" ] }

The above collection translates to the following two tables in tabular format:

mysql> select * from test;
+------+------+
| _id  | a    |
+------+------+
|    1 |    3 |
+------+------+
mysql> select * from test_b;
+------+--------+-------+
| _id  | b      | b_idx |
+------+--------+-------+
|    1 | orange |     0 |
|    1 | apple  |     1 |
|    1 | pear   |     2 |
+------+--------+-------+

The --prejoin flag causes data from the a column to be included in the test_b table:

mysql> select * from test_b;
+------+------+--------+-------+
| _id  | a    | b      | b_idx |
+------+------+--------+-------+
|    1 |    3 | orange |     0 |
|    1 |    3 | apple  |     1 |
|    1 |    3 | pear   |     2 |
+------+------+--------+-------+

Log Options

--logAppend

Appends new logging output to an existing log file specified by --logPath.

Requires --logRotate.

--logPath <filename>

Default: stderr

Specifies a path to a log file for storing logging output.

--logRotate reopen | rename

Default: rename

Specifies that you want to rotate logs and how they should be rotated.

When this option is set, the logs rotate when you issue a FLUSH LOGS command to the MongoDB Connector for BI or when you restart mongosqld.

If you set --logRotate to rename:

The existing log file is closed. An RFC3339-formatted timestamp is appended to the closed log file. A new log file is created.

If you set --logRotate to reopen:

The existing log file is closed and reopened.

Note

On UNIX and macOS platforms, you can issue a SIGUSR1 signal to restart the mongosqld process and rotate the logs.

--usageLogInterval <number>

Default: 60

New in version 2.14.

The interval, in seconds, at which usage statistics are written to the log. Set to 0 to disable usage logging.

Note

Usage logging is not enabled for Windows.

--verbose, -v

Specifies that mongosqld should provide more detailed log output.

The following table describes the information provided at each log level:

Letter Option	Log Level	Message Content
--quiet	None	Log nothing.
No flag	Default	Log messages that notify user of basic mongosqld events and state changes.
-v	Administrator	Log messages that provie information to mongosqld administrators.
-vv	Developer	Log messages useful to MongoDB support and development.

--quiet

Hides all log output.

MongoDB TLS/SSL Options

--mongo-ssl

Default: False

Instructs mongosqld to use TLS/SSL when connecting to a MongoDB instance.

--mongo-sslPEMKeyFile <filename>

Specifies the .pem file containing both the TLS/SSL certificate and key for mongosqld to use when connecting to MongoDB. You can specify the file name of the .pem file using either using a relative or absolute path.

This option is required when using the --mongo-ssl option to connect to a mongod or mongos that has CAFile enabled without net.ssl.allowConnectionsWithoutCertificates.

--mongo-sslPEMKeyPassword <password>

Specifies the path to a file containing the certificate and private key for connecting to MongoDB.

--mongo-sslAllowInvalidHostnames

Permits mongosqld to connect to a MongoDB server whose hostname differs from the hostname on its TLS/SSL certificate.

--mongo-sslAllowInvalidCertificates

Permits the MongoDB instance to present an invalid server SSL/TLS certificate. When using the allowInvalidCertificates setting, MongoDB logs the use of the invalid certificate as a warning.

--mongo-sslCAFile <filename>

Specifies the MongoDB instance's .pem file containing the root certificate chain from the Certificate Authority. Specify the file name of the .pem file using relative or absolute paths.

Warning

For SSL connections (--mongo-ssl) to mongod and mongos, if the mongosqld runs without the --mongo-sslCAFile, mongosqld will not attempt to validate the server certificates. This creates a vulnerability to expired mongod and mongos certificates as well as to foreign processes posing as valid mongod or mongos instances. Ensure that you always specify the CA file to validate the server certificates in cases where intrusion is a possibility.

--mongo-sslCRLFile <filename>

Specifies the MongoDB instance's .pem file containing the certificate revocation list.

--mongo-sslFIPSMode

Enables FIPS mode in the installed OpenSSL library.

--mongo-minimumTLSVersion <TLS1_0|TLS1_1|TLS1_2>

Default: TLS1_1

Specifies the minimum required TLS version for outgoing connections to a mongod or mongos. instance. The default value TLS1_1 corresponds to TLS version 1.1.

Client TLS/SSL Options

--sslMode <mode>

Default: disabled

New in version 2.3.

Enable or disable TLS/SSL for connections to mongosqld. The argument to the sslMode option can be one of the following:

Value	Description
disabled	mongosqld cannot accept connections secured using TLS/SSL.
allowSSL	mongosqld can accept connections secured using TLS/SSL.
requireSSL	mongosqld can only accept connections secured using TLS/SSL.

--sslPEMKeyFile <filename>

Specifies the .pem file containing both the TLS/SSL certificate and key for MySQL clients. Specify the file name of the .pem file using relative or absolute paths.

--sslPEMKeyPassword <password>

Specifies the password used to decrypt the private key specified by --sslPEMKeyFile.

--sslAllowInvalidCertificates

Permits MySQL clients to present invalid client TLS/SSL certificates.

--sslAllowInvalidHostnames

Permits SQL clients to connect to a mongosqld whose hostname differs from the hostname on its TLS/SSL certificate.

--sslCAFile <filename>

Specifies the mongosqld .pem file containing the root certificate chain from the Certificate Authority. Specify the file name of the .pem file using relative or absolute paths.

--sslCRLFile <filename>

Specifies the mongosqld .pem file containing the certificate revocation list.

--auth

Requires authentication for incoming client requests.

Important

Changed in version 2.4.0.

When authentication is enabled, admin credentials must be provided with the --mongo-username and --mongo-password options or the mongodb.net.auth.username and mongodb.net.auth.password settings in the configuration file.

mongosqld uses the admin credentials to gather metadata on the sampled namespaces and uses the credentials of the connecting client to restrict data to only what the client is authorized to read. For more information on the required permissions for the admin user, see User Permissions for Cached Sampling.

--defaultAuthSource <authSource>

Default: admin

Specifies the default MongoDB authentication source. Set this value to specify a default source that mongosqld uses when authenticating with a MongoDB database. The authentication mechanisms GSSAPI and PLAIN use the $external source, while SCRAM-SHA-1 and SCRAM-SHA-256 use a MongoDB database as its source.

If no value is given for this option it defaults to the the MongoDB admin database.

The $external authentication source stores a reference to system users in a MongoDB database called $external, but the credentials are stored in an external, non-MongoDB system, such as an LDAP server.

Any connection which uses the default value can omit the source parameter from its MySQL or Tableau username.

--defaultAuthMechanism <authMechanism>

Default: SCRAM-SHA-1

Specifies the default authentication mechanism. Set this value to specify a default mechanism for connecting to mongosqld. Any connection which uses this specified default value can omit the mechanism value from its MySQL or Tableau username.

Value	Description
SCRAM-SHA-1	RFC 5802 standard Salted Challenge Response Authentication Mechanism using the SHA1 hash function.
SCRAM-SHA-256	New in version 2.6: RFC 7677 standard Salted Challenge Response Authentication Mechanism using the SHA2 hash function.
PLAIN (LDAP SASL)	External authentication using LDAP. You can also use PLAIN for authenticating in-database users. PLAIN transmits passwords in plain text. This mechanism is available only in MongoDB Enterprise.
GSSAPI (Kerberos)	External authentication using Kerberos. This mechanism is available only in MongoDB Enterprise.

--minimumTLSVersion <TLS1_0|TLS1_1|TLS1_2>

Default: TLS1_1

Specifies the minimum required TLS version for clients to connect to mongosqld. The default value TLS1_1 corresponds to TLS version 1.1.

Service Options

--serviceName <service-name>

Name of the system service which runs mongosqld.

--serviceDisplayName <service-name>

Display name of the system service which runs mongosqld.

--serviceDescription <service-name>

Description of the system service which runs mongosqld.

Kerberos Options

--gssapiHostname <hostname>

Default: First IP address for net.bindIp.

New in version 2.5.

An FQDN for the purpose of configuring Kerberos authentication. The Kerberos hostname overrides the hostname only for the configuration of Kerberos.

--gssapiServiceName <service-name>

Default: mongosql

New in version 2.5.

Registered name of the service using Kerberos. This option allows you to override the default Kerberos service name component of the Kerberos SPN, on a per-instance basis. If unspecified, the default value is used.

--mongo-gssapiServiceName <service-name>

Default: mongodb

New in version 2.5.

Set the Kerberos SPN when connecting to Kerberized MongoDB instances. This value must match the service name set on MongoDB instances.

--gssapiConstrainedDelegation

Default: False

New in version 2.11.

Use proxy credentials for Kerberos authorization, enabling constrained delegation. Requires mongosqld service credentials to be present in the client keytab as well as the service keytab. See Configure Kerberos for BI Connector for more information about Kerberos configuration.

Socket Options

--filePermissions <mode>

Default: 0700

Specify the permissions for the Unix domain socket file.

--noUnixSocket

Disable listening on Unix domain sockets.

--unixSocketPrefix <path>

Default: /tmp

Specifies an alternative directory for the mongosqld Unix domain socket.

mongosqld will create a socket file called mysql.sock underneath this path. If you do not specify --unixSocketPrefix, the socket will exist at /tmp/mysql.sock.

Set Parameter Option

--setParameter <parameter>

Parameter	Type	Corresponds to
"polymorphic_type_conversion_mode=<value>"	string	Determines how mongosqld evaluates document fields that are specified with multiple data types. Accepted values are off, fast, and safe. To learn more about these values, see System Variables.
"type_conversion_mode=<value>"	string	Specifies which mode mongosqld uses to convert data types. BI Connector uses its own mode (mongosql) by default. Accepted values are mongosql and mysql. To learn more about these values, see System Variables.

The following example starts mongosqld and uses the --setParameter option to specify the type conversion mode:

mongosqld --setParameter "type_conversion_mode=mongosql"

Logging Options

systemLog:
  logAppend: <boolean>
  logRotate: "rename"|"reopen"
  path: <string>
  quiet: <boolean>
  verbosity: <integer>

Schema Options

schema:
  path: <string>
  maxVarcharLength: <integer>

Data Sampling Options

schema:
  sample:
    size: <integer>
    prejoin: <boolean>
    namespaces: <array of strings>
    uuidSubtype3Encoding: <[old|csharp|java]>
  stored:
    mode: <[custom|auto]>
    source: <string>
    name: <db-name>
  refreshIntervalSecs: <integer>

Runtime Options

runtime:
  memory:
    maxPerStage: <integer>
    maxPerServer: <integer>
    maxPerConnection: <integer>

runtime.memory.maxPerStage

Type: integer

Default: unlimited

Specifies the maximum amount of memory in bytes that a query execution stage may use.

runtime.memory.maxPerServer

Type: integer

Default: unlimited

New in version 2.5.

Specifies the maximum amount of memory in bytes that a mongosqld process may use.

runtime.memory.maxPerConnection

Type: integer

Default: unlimited

New in version 2.5.

Specifies the maximum amount of memory in bytes that a mongosqld client connection may use.

Network Options

net:
  bindIp: <string>
  port: <integer>
  unixDomainSocket:
    enabled: <boolean>
    pathPrefix: <string>
    filePermissions: <string>
  ssl:
    mode: <string>
    allowInvalidCertificates: <boolean>
    PEMKeyFile: <string>
    PEMKeyPassword: <string>
    CAFile: <string>

Security Options

security:
  enabled: <boolean>
  defaultMechanism: <string>
  defaultSource: <string>
  gssapi:
    hostname: <string>
    serviceName: <string>

MongoDB Host Options

mongodb:
  versionCompatibility: <string>
  net:
    uri: <string>
    ssl:
      enabled: <boolean>
      allowInvalidCertificates: <boolean>
      allowInvalidHostnames: <boolean>
      PEMKeyFile: <string>
      PEMKeyPassword: <string>
      CAFile: <string>
      CRLFile: <string>
      FIPSMode: <boolean>
    auth:
      username: <username>
      password: <password>
      source: <auth-db-name>
      mechanism: <auth-mechanism>
      gssapiServiceName: <service>

Process Management Options

processManagement:
  service:
    name: <string>
    displayName: <string>
    description: <string>

Set Parameter Options

setParameter:
  polymorphic_type_conversion_mode: <string>
  type_conversion_mode: <string>

These configuration file options are also available as system variables. For more information, see System Variables.

Load External Configuration Values

You may use expansion directives in your configuration file to load externally sourced configuration values. Expansion directives obscure confidential information like security certificates and passwords.

To learn more about using expansion directives, see Externally Sourced Configuration File Values in the MongoDB manual.

Example Configuration File

systemLog:
  logAppend: false
  path: "/var/log/mongosqld/mongosqld.log"
  verbosity: 2
security:
  enabled: true
mongodb:
  net:
    uri: "mongo.example.com:27017"
    auth:
      username: "root"
      password: "changeme"
net:
  bindIp: 192.0.2.14
  port: 3307
  ssl:
    mode: "allowSSL"
    PEMKeyFile: "/vagrant/certificates/mongosqld-server.pem"
    CAFile: "/vagrant/certificates/ca.crt"
schema:
  sample:
    namespaces: "inventory.*"
processManagement:
  service:
    name: mongosqld
    displayName: mongosqld
    description: "BI Connector SQL proxy server"

mongosqld with a Schema File

Use the --schema option to specify a schema file for mongosqld to use.

mongosqld --schema /path/to/schema-file.drdl

Specify a Collection to Sample

Use the --sampleNamespaces option to specify a database and collection for mongosqld to sample data from. If you start mongosqld without the --sampleNamespaces option, it samples data from all available databases except the admin and local databases.

The following example samples data from a collection named addresses in a database named contacts:

mongosqld --sampleNamespaces contacts.addresses

Specify Multiple Collections to Sample

To sample data from all the collections in a database, use the --sampleNamespaces option with a wildcard (*).

The following example samples data from all the collections in a database named inventory:

mongosqld --sampleNamespaces 'inventory.*'

You can repeat the --sampleNamespaces at the command line to sample data from multiple collections within a database, or from multiple collections across several databases.

The following example samples data from all the collections in databases named cars and boats:

mongosqld --sampleNamespaces 'cars.*' \
          --sampleNamespaces 'boats.*' \

You can mix and match wildcards and specific collections with multiple --sampleNamespaces options.

The following example samples data from all collections in a database named orders, as well as the FY2015 and FY2016 collections from the expenses database:

mongosqld --sampleNamespaces 'orders.*' \
          --sampleNamespaces expenses.FY2015 \
          --sampleNamespaces expenses.FY2016 \

Exclude Databases and Collections from Sampling

To exclude specified namespaces from the data sampling process, precede the namespace with a ~ (tilde) character. The following example includes all databases and collections except the apples collection in the fruit database:

mongosqld --sampleNamespaces ~fruit.apples

Use the * wildcard character to indicate all collections in a database. The following example excludes the entire vehicles database from data sampling:

mongosqld --sampleNamespaces '~vehicles.*'

Sample All Documents in a Collection

If you have a collection in which some documents have different fields from others and you want to ensure that the schema takes them all into consideration, set the --sampleSize option to 0. This causes mongosqld to examine all documents in the specified namespace when it builds its schema.

The following example instructs mongosqld to build the schema based on all documents in the inventory.electronics namespace.

mongosqld --sampleNamespaces inventory.electronics \
          --sampleSize 0

Specify a Database to Persist a Schema

Use the --schemaSource option to specify a database in which to store schema information. Use the --schemaMode option to specify whether mongosqld can write to the schema database or only read from it.

The following example command uses a database named sampleDb to store schema information and sets --schemaMode to auto.

mongosqld --schemaSource sampleDb --schemaMode auto

Specify a Data Resampling Interval

Use the --schemaRefreshIntervalSecs option to specify an interval in seconds for mongosqld to resample data and regenerate the schema. The default value for this option is 0, which means that mongosqld never resamples data.

If you set a resampling interval with --schemaRefreshIntervalSecs and you specify a schema database with --schemaSource, you cannot set --schemaMode to custom.

The following example does not specify a schema database or a schema file, so it holds its schema in memory. It uses --schemaRefreshIntervalSecs to specify a data resampling interval of 3600 seconds.

mongosqld --schemaRefreshIntervalSecs 3600

mongosqld with Authentication Enabled

To connect mongosqld to a MongoDB instance running with authentication enabled, use the --auth option and provide MongoDB user credentials.

The following example starts mongosqld with credentials for a user named reportsUser with password reportsPass, who has access to the reports.inventory namespace.

mongosqld --auth \
          --mongo-username reportsUser \
          --mongo-password reportsPass \
          --sampleNamespaces reports.inventory

This example does not specify an authenticating database with the --mongo-authenticationSource option, so it defaults to admin. It does not specify an authentication mechanism with the --mongo-authenticationMechanism option, so it defaults to SCRAM-SHA-1.

mongosqld with a Configuration File

If you wish to specify a configuration file which saves logs to /var/log/mongosqld.log and loads a schema from /var/schema.drdl, you may save a file such as the following to /etc/mongosqld.conf:

systemLog:
  path: /var/log/mongosqld.log
schema:
  path: /var/schema.drdl

You may then start mongosqld with the --config option:

mongosqld --config /etc/mongosqld.conf

For more information on starting mongosqld as a system service, see the Installation Guide for your operating system.

Usage with Atlas

Atlas is a cloud service for running, monitoring, and maintaining MongoDB deployments, including the provisioning of dedicated servers for the MongoDB instances.

Atlas uses TLS/SSL to encrypt connections and enforces authentication by default.

mongosqld can use any valid TLS certificate issued by a certificate authority, or a self-signed certificate. If you use a self-signed certificate, although the communications channel will be encrypted, there will be no validation of server identity. Although such a situation will prevent eavesdropping on the connection, it leaves you vulnerable to a man-in-the-middle attack. Using a certificate signed by a trusted certificate authority will permit your MySQL client to verify the server's identity.

For testing purposes, you can create a .pem key file named test.pem using the openssl tool:

openssl req -nodes -newkey rsa:2048 -keyout test.key -out test.crt -x509 -days 365 -subj "/C=US/ST=test/L=test/O=test Security/OU=IT Department/CN=test.com"
cat test.crt test.key > test.pem

Start mongosqld with the following options:

• --mongo-ssl

• --auth

• --sslPEMKeyFile

• --mongo-uri

• --sslMode

• --mongo-username

• --mongo-password

If you named your key file test.pem as in the previous example, you can run the following, substituting your cluster URI and login credentials:

mongosqld --mongo-ssl \
          --auth \
          --sslPEMKeyFile test.pem \
          --mongo-uri <uri> \
          --sslMode allowSSL \
          --mongo-username <username> \
          --mongo-password <password>

Pass your username, password, and authentication database to your SQL client. For example, using mysql without verifying your mongosqld server certificate:

mysql --host <mongosqld-host> --port <mongosqld-port> -u <username>?source=admin -p --ssl-mode required --enable-cleartext-plugin

Refer to Connect from the MySQL Client for more details on using the mysql client with the BI Connector.

Invalid Configurations

The following mongosqld option configurations are invalid and cause an error at startup.