• Reference >
• Configure SSL for BI Connector

Configure SSL for BI Connector

For BI Connector to transmit data securely, you should enable Secure Socket Layer (SSL) encryption on your MongoDB instance, your mongosqld instance, and in your BI tool. A complete description of SSL configuration is outside the scope of this document, but this tutorial outlines the process for creating your own SSL certificates for testing purposes and starting the MongoDB components with SSL enabled.

Important

The procedures described in this tutorial are for testing purposes only. A production environment should use SSL certificates issued by a recognized certificate authority (CA).

Prerequisites

• A MongoDB user with sufficient permissions to run mongosqld. For more information about user permissions and BI Connector, see User Permissions for Cached Sampling.
• A mongod instance which you can start and stop.
• A mongosqld instance which you can start and stop.
• OpenSSL
• The MySQL shell

Note on Cluster Availability

For MongoDB replica sets, including sharded replica sets, use a rolling upgrade procedure to ensure that the cluster can continue to serve read operations while the procedure is ongoing. While the replica set primary is undergoing upgrade procedures, database applications must either hold or retry write operations until after the automatic failover and election cycle completes. See Replica Set Availability for more information.

Create and Test Self-Signed Certificates

This tutorial contains instructions on creating several files which allow a mongosqld process to accept OpenSSL encrypted connections from an SQL client, such as the MySQL shell, and make an encrypted connection with a mongod instance. We create two .pem files, each of which consists of an encryption key and a self-signed SSL certificate.

• Windows
• macOS

1

Create a Certificates Directory

1. Using the Windows cmd shell, create a directory to hold your certificates. This tutorial uses C:\opt\certs.

   copy

   mkdir C:\opt\certs
   cd C:\opt\certs
   

2. This tutorial assumes that your OpenSSL directory is at C:\OpenSSL. If it is located somewhere else on your system, either edit the commands appropriately or move the directory to C:\OpenSSL.

   If your C:\OpenSSL\bin directory does not contain a configuration file named openssl.cfg, create one. An example configuration file is provided here:

   copy

   #
   # OpenSSL configuration file.
   #
   
   # Establish working directory.
   
   dir                            = .
   
   [ ca ]
   default_ca                     = CA_default
   
   [ CA_default ]
   serial                 = $dir/serial
   database                       = $dir/certindex.txt
   new_certs_dir                  = $dir/certs
   certificate                    = $dir/cacert.pem
   private_key                    = $dir/private/cakey.pem
   default_days                   = 365
   default_md                     = md5
   preserve                       = no
   email_in_dn                    = no
   nameopt                        = default_ca
   certopt                        = default_ca
   policy                 = policy_match
   
   [ policy_match ]
   countryName                    = match
   stateOrProvinceName            = match
   organizationName               = match
   organizationalUnitName = optional
   commonName                     = supplied
   emailAddress                   = optional
   
   [ req ]
   default_bits                   = 1024
   default_keyfile                = key.pem
   default_md                     = md5
   string_mask                    = nombstr
   distinguished_name             = req_distinguished_name
   req_extensions         = v3_req
   
   [ req_distinguished_name ]
   # Variable name                        Prompt string
   #-------------------------     ----------------------------------
   0.organizationName             = Organization Name (company)
   organizationalUnitName = Organizational Unit Name (department, division)
   emailAddress                   = Email Address
   emailAddress_max               = 40
   localityName                   = Locality Name (city, district)
   stateOrProvinceName            = State or Province Name (full name)
   countryName                    = Country Name (2 letter code)
   countryName_min                = 2
   countryName_max                = 2
   commonName                     = Common Name (hostname, IP, or your name)
   commonName_max         = 64
   
   # Default values for the above, for consistency and less typing.
   # Variable name                        Value
   #------------------------      ------------------------------
   0.organizationName_default     = My Company
   localityName_default           = My Town
   stateOrProvinceName_default    = State or Providence
   countryName_default            = US
   
   [ v3_ca ]
   basicConstraints               = CA:TRUE
   subjectKeyIdentifier           = hash
   authorityKeyIdentifier = keyid:always,issuer:always
   
   [ v3_req ]
   basicConstraints               = CA:FALSE
   subjectKeyIdentifier           = hash
   

3. Set up an environment variable for later use.

   copy

   set OPENSSL_CONF=C:\OpenSSL\bin\openssl.cfg
   

2

Generate a Self-Signed Certificate Authority

1. Change directory to C:\opt\certs.

   copy

   cd C:\opt\certs
   

2. Start the OpenSSL application.

   copy

   C:\OpenSSL\bin\openssl.exe
   

   

3. The following command generates and outputs a private key to mdbprivate.key.

   copy

   genrsa -out C:\opt\certs\mdbprivate.key -aes256 -passout pass:password
   

4. The following command generates and outputs a certificate authority file to mdbca.crt.

   The following command prompts the user to enter several pieces of information which are incorporated into the certificate request. One of the fields is called Common Name, which is a Fully Qualified Domain Name (FQDN). The Common Name entry for the certificate generated in this step of the tutorial must be different from the Common Name entry for the certificates generated in steps 3 and 9.

   copy

   req -x509 -new -key C:\opt\certs\mdbprivate.key -days 1000 -out C:\opt\certs\mdbca.crt -passin pass:password
   

We now have two files: The mdbca.crt certificate authority file, and the mdbprivate.key key used to sign that request.

3

Generate a PEM Certificate for the MongoDB Server

The following command generates a key for the mongod process to use and a Certificate Signing Request (CSR). For this step, the Common Name response must be the Fully Qualified Domain Name (FQDN) of the server where your mongod instance runs, i.e. www.example.com.

Enter the following command at the OpenSSL prompt:

copy

req -new -nodes -newkey rsa:2048 -keyout .\mdb.key -out .\mdb.csr

4

Sign the mongodb CSR

Enter the following command at the OpenSSL prompt:

copy

x509 -CA .\mdbca.crt -CAkey .\mdbprivate.key -CAcreateserial -req -days 1000 -in .\mdb.csr -out .\mdb.crt -passin pass:password

5

Create a PEM file for the MongoDB server

A .pem file consists of a key and a certificate concatenated together. To create a .pem file for your MongoDB instance to use, exit the OpenSSL prompt and enter the following command at the cmd prompt in the C:\opt\certs directory:

copy

copy .\mdb.key + .\mdb.crt mdb.pem

You should now have the following files in your certificates directory:

mdb.crt
mdb.csr
mdb.key
mdb.pem
mdbca.crt
mdbprivate.key

If any are missing, go back and review the previous steps, checking for errors.

6

Update mongod configuration file

To configure mongod to require SSL for incoming connections, modify your configuration file as follows. Your values may vary, depending on where you created your SSL files.

Option	Value
net.ssl.mode	requireSSL
net.ssl.PEMKeyFile	C:\opt\certs\mdb.pem
net.ssl.CAFile	C:\opt\certs\mdbca.crt
net.ssl.clusterFile	C:\opt\certs\mdb.pem
security.clusterAuthmode	x509

The following example configuration file contains directives for SSL connections and x.509 authentication.

Note

The following is an example mongod configuration file. Your configuration file may require additional or different options.

copy

systemLog:
   destination: file
   path: 'C:\data\mongod.log'
   logAppend: true
net:
   bindIp: <step-3-FQDN>
   port: 27017
   ssl:
      mode: requireSSL
      PEMKeyFile: 'C:\opt\certs\mdb.pem'
      CAFile: 'C:\opt\certs\mdbca.crt'
      clusterFile: 'C:\opt\certs\mdb.pem'
security:
   clusterAuthMode: x509
storage:
   dbPath: 'C:\data\db'

If you prefer to start mongod with command-line options instead of a configuration file, see mongosqld for equivalent options.

7

Restart your mongod server.

copy

mongod.exe --config C:\path\to\mongod.conf

8

Test your connection with the mongo shell.

Connect to your server with the mongo shell to test your SSL connection. Your mongo command needs the following SSL options:

Option	Value
–ssl	none
–sslCAFile	C:\opt\certs\mdbca.crt (file generated in step 2.4)
–sslPEMKeyFile	C:\opt\certs\mdb.pem (file generated in step 5)

copy

.\mongo.exe --ssl --host <step3-common-name> --sslCAFile "C:\opt\certs\mdbca.crt" --sslPEMKeyFile "C:\opt\certs\mdb.pem"

Edit your options appropriately.

9

Create a key and a CSR for BI Connector

The Common Name entry for this CSR should match the FQDN of the server where you run mongosqld.

Note

The Common Name entry for this CSR must be different from the Common Name entry for the first CSR you created, in step 2.

Start the OpenSSL application and enter the following command at the OpenSSL prompt:

copy

req -new -nodes -newkey rsa:2048 -keyout .\bi.key -out .\bi.csr

10

Sign the BI Connector certificate

Enter the following command at the OpenSSL prompt:

copy

x509 -CA .\mdbca.crt -CAkey .\mdbprivate.key -CAcreateserial -req -days 1000 -in .\bi.csr -out .\bi.crt -passin pass:password

11

Create a BI Connector PEM file

Exit the OpenSSL application and enter the following command at the cmd prompt:

copy

copy .\bi.key + .\bi.crt bi.pem

12

Start mongosqld

Your mongosqld configuration file requires several SSL-specific options. Your values may vary, depending on where you created your SSL files.

Option	Value
mongodb.net.ssl.enabled	true
mongodb.net.ssl.PEMKeyFile	C:\opt\certs\mdb.pem
mongodb.net.sslCAFile	C:\opt\certs\mdbca.crt
net.ssl.mode	requireSSL
net.ssl.PEMKeyFile	C:\opt\certs\bi.pem
net.ssl.CAFile	C:\opt\certs\mdbca.crt

The following example configuration file uses files located in the C:\opt\certs directory. It specifies a username and password which correspond to a MongoDB user with sufficient permissions to run mongosqld and read from the test database.

copy

systemLog:
  logAppend: false
  path: 'C:\logs\mongosqld.log'
  verbosity: 2

security:
  enabled: true

mongodb:
  net:
    uri: <step-3-FQDN>
    auth:
      username: <username>
      password: <password>
    ssl:
      enabled: true
      PEMKeyFile: 'C:\opt\certs\mdb.pem'
      CAFile: 'C:\opt\certs\mdbca.crt'

net:
  bindIp: localhost
  port: 3307
  ssl:
    mode: 'requireSSL'
    PEMKeyFile: 'C:\opt\certs\bi.pem'
    CAFile: 'C:\opt\certs\mdbca.crt'

schema:
  sample:
    namespaces: 'test.*'

Start mongosqld with the --config option to use a configuration file.

copy

.\mongosqld --config C:\path\to\mongosqld.conf

14

Test with an ODBC Data Source Name (DSN)

To create an ODBC DSN which connects over SSL, follow the instructions in the DSN tutorial and configure the new DSN with your SSL certificate path information.

On the Connection tab of the DSN configuration screen, check the box labeled Enable Cleartext Authentication.

Click the Test button to test your ODBC connection.

Once your DSN is set up, you can use it to connect to any of several BI tools, such as Power BI or Qlik.

1

Create a Certificates Directory

Create a directory to hold your certificates. This tutorial uses /opt/certs. Your certificate directory must be readable by the system user which runs the mongod and mongosqld programs.

copy

mkdir /opt/certs
cd /opt/certs

2

Generate a Self-Signed Certificate Authority

1. The following command generates and outputs a private key to mdbprivate.key.

   copy

   openssl genrsa -out /opt/certs/mdbprivate.key -aes256 -passout pass:password
   

2. The following command generates and outputs a certificate authority file to mdbca.crt.

   The following command prompts the user to enter several pieces of information which are incorporated into the certificate request. One of the fields is called Common Name, which is a Fully Qualified Domain Name (FQDN). The Common Name entry for the certificate generated in this step of the tutorial must be different from the Common Name entry for the certificates generated in steps 3 and 9.

   copy

   openssl req -x509 -new -key /opt/certs/mdbprivate.key -days 1000 \
   -out /opt/certs/mdbca.crt -passin pass:password
   

We now have two files: The mdbca.crt certificate authority file, and the mdbprivate.key key used to sign that request.

3

Generate a PEM Certificate for the MongoDB Server

The following command generates a key for the mongod process to use and a Certificate Signing Request (CSR). For this step, the Common Name response must be the Fully Qualified Domain Name (FQDN) of the server where your mongod instance runs, i.e. www.example.com. To determine the FQDN of your server, use hostname -f at the command prompt.

You may be prompted to enter a challenge password for the Extra Attribute element. For the purposes of this tutorial, you can leave this element blank.

copy

openssl req -new -nodes -newkey rsa:2048 -keyout /opt/certs/mdb.key \
-out /opt/certs/mdb.csr

4

Sign the mongodb CSR

copy

openssl x509 -CA /opt/certs/mdbca.crt -CAkey /opt/certs/mdbprivate.key \
-CAcreateserial -req -days 1000 -in /opt/certs/mdb.csr -out /opt/certs/mdb.crt \
-passin pass:password

5

Create a PEM file for the MongoDB server

A .pem file consists of a key and a certificate concatenated together. To create a .pem file for your MongoDB instance to use, enter the following command in the /opt/certs/ directory:

copy

cat /opt/certs/mdb.key /opt/certs/mdb.crt > /opt/certs/mdb.pem

You should now have the following files in your certificates directory:

mdb.crt
mdb.csr
mdb.key
mdb.pem
mdbca.crt
mdbprivate.key

If any are missing, go back and review the previous steps, checking for errors.

6

Update mongod configuration file

To configure mongod to require SSL for incoming connections, modify your configuration file as follows. Your values may vary, depending on where you created your SSL files.

Option	Value
net.ssl.mode	requireSSL
net.ssl.PEMKeyFile	/opt/certs/mdb.pem
net.ssl.CAFile	/opt/certs/mdbca.crt
net.ssl.clusterFile	/opt/certs/mdb.pem
security.clusterAuthmode	x509

The following example configuration file contains directives for SSL connections and x.509 authentication.

Note

The following is an example mongod configuration file. Your configuration file may require additional or different options.

copy

systemLog:
   destination: file
   path: '/data/mongod.log'
   logAppend: true
processManagement:
   fork: true
   pidFilePath: '/data/mongod.pid'
net:
   bindIp: localhost
   port: 27017
   ssl:
      mode: requireSSL
      PEMKeyFile: '/opt/certs/mdb.pem'
      CAFile: '/opt/certs/mdbca.crt'
      clusterFile: '/opt/certs/mdb.pem'
security:
   clusterAuthMode: x509
storage:
   dbPath: '/data'

If you prefer to start mongod with command-line options instead of a configuration file, see mongosqld for equivalent options.

7

Restart your mongod server.

copy

mongod --config /path/to/mongod.conf

8

Test your connection with the mongo shell.

Connect to your server with the mongo shell to test your SSL connection. Your mongo command needs the following SSL options:

Option	Value
–ssl	none
–sslCAFile	/opt/certs/mdbca.crt (file generated in step 2.2)
–sslPEMKeyFile	/opt/certs/mdb.pem (file generated in step 5)

copy

mongo --ssl --host <step3-common-name> \
--sslCAFile /opt/certs/mdbca.crt --sslPEMKeyFile /opt/certs/mdb.pem

Edit your options appropriately.

9

Create a key and a CSR for BI Connector

The Common Name entry for this CSR should match the FQDN of the server where you run mongosqld. To determine the FQDN of your server, use hostname -f at the command prompt.

You may be prompted to enter a challenge password for the Extra Attribute element. For the purposes of this tutorial, you can leave this element blank.

Note

The Common Name entry for this CSR must be different from the Common Name entry for the first CSR you created, in step 2.

copy

openssl req -new -nodes -newkey rsa:2048 -keyout /opt/certs/bi.key \
-out /opt/certs/bi.csr

10

Sign the BI Connector certificate

copy

openssl x509 -CA /opt/certs/mdbca.crt -CAkey /opt/certs/mdbprivate.key \
-CAcreateserial -req -days 1000 -in /opt/certs/bi.csr -out /opt/certs/bi.crt \
-passin pass:password

11

Create a BI Connector PEM file

copy

cat /opt/certs/bi.key /opt/certs/bi.crt > /opt/certs/bi.pem

12

Start mongosqld

Your mongosqld configuration file requires several SSL-specific options. Your values may vary, depending on where you created your SSL files.

Option	Value
mongodb.net.ssl.enabled	true
mongodb.net.ssl.PEMKeyFile	/opt/certs/mdb.pem
mongodb.net.sslCAFile	/opt/certs/mdbca.crt
net.ssl.mode	requireSSL
net.ssl.PEMKeyFile	/opt/certs/bi.pem
net.ssl.CAFile	/opt/certs/mdbca.crt

The following example configuration file uses files located in the /opt/certs/ directory. It specifies a username and password which correspond to a MongoDB user with sufficient permissions to run mongosqld and read from the test database.

copy

systemLog:
  logAppend: false
  path: './logs/mongosqld.log'
  verbosity: 2

security:
  enabled: true

mongodb:
  net:
    uri: <step9-common-name>
    auth:
      username: <username>
      password: <password>
    ssl:
      enabled: true
      PEMKeyFile: '/opt/certs/mdb.pem'
      CAFile: '/opt/certs/mdbca.crt'

net:
  bindIp: localhost
  port: 3307
  ssl:
    mode: 'requireSSL'
    PEMKeyFile: '/opt/certs/bi.pem'
    CAFile: '/opt/certs/mdbca.crt'

schema:
  sample:
    namespaces: 'test.*'

Start mongosqld with the --config option to use a configuration file.

copy

mongosqld --config /path/to/mongosqld.conf

13

Test with the MySQL shell

To connect to your mongosqld instance, start the MySQL shell with the following command line options.

copy

mysql --ssl-mode REQUIRED --ssl-ca=/opt/certs/mdbca.crt \
--enable-cleartext-plugin --port 3307 -u <username> -p

© MongoDB, Inc 2008-present. MongoDB, Mongo, and the leaf logo are registered trademarks of MongoDB, Inc.