Configure SSL for BI Connector

On this page

Prerequisites

Create and Test Self-Signed Certificates

This version of the documentation is archived and no longer supported. View the current documentation instead.

For BI Connector to transmit data securely, you should enable Transport Layer Security (TLS) encryption on your MongoDB instance, your mongosqld instance, and in your BI tool. A complete description of TLS configuration is outside the scope of this document, but this tutorial outlines the process for creating your own TLS certificates for testing purposes and starting the MongoDB components with TLS 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.

Create a Certificates Directory

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

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:

#
# 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

Set up an environment variable for later use.
```
set OPENSSL_CONF=C:\OpenSSL\bin\openssl.cfg
```

Generate a Self-Signed Certificate Authority

Change directory to C:\opt\certs.
```
cd C:\opt\certs
```
Start the OpenSSL application.
```
C:\OpenSSL\bin\openssl.exe
```
The following command generates and outputs a private key to mdbprivate.key.
```
genrsa -out C:\opt\certs\mdbprivate.key -aes256 -passout pass:password
```
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.
```
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.

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:

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

Sign the mongodb CSR

Enter the following command at the OpenSSL prompt:

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

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 .\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.

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.

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.

Restart your `mongod` server.

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

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)

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

Edit your options appropriately.

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:

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

Sign the BI Connector certificate

Enter the following command at the OpenSSL prompt:

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

Create a BI Connector PEM file

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

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

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.

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.

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

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.

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.

mkdir /opt/certs
cd /opt/certs

Generate a Self-Signed Certificate Authority

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

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

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.
```
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.

Generate a PEM Certificate for the MongoDB Server

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.

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

Sign the mongodb CSR

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

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:

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.

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.

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.

Restart your `mongod` server.

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

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)

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

Edit your options appropriately.

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.

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

Sign the BI Connector certificate

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

Create a BI Connector PEM file

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

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.

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.

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

Test with the MySQL shell

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

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

Configure SSL for BI Connector.leafygreen-ui-m0pgrr{-webkit-align-self:center;-ms-flex-item-align:center;align-self:center;padding:0 10px;visibility:hidden;}.leafygreen-ui-a30zj9{color:#889397;vertical-align:middle;margin-top:-2px;}.css-fmznk8{margin-top:-85px;position:absolute;padding-bottom:2px;}

Important

Prerequisites

Note on Cluster Availability

Create and Test Self-Signed Certificates

Create a Certificates Directory

Generate a Self-Signed Certificate Authority

Generate a PEM Certificate for the MongoDB Server

Sign the mongodb CSR

Create a PEM file for the MongoDB server

Update mongod configuration file

Note

Restart your mongod server.

Test your connection with the mongo shell.

Create a key and a CSR for BI Connector

Note

Sign the BI Connector certificate

Create a BI Connector PEM file

Start mongosqld

Test with an ODBC Data Source Name (DSN)

Create a Certificates Directory

Generate a Self-Signed Certificate Authority

Generate a PEM Certificate for the MongoDB Server

Sign the mongodb CSR

Create a PEM file for the MongoDB server

Update mongod configuration file

Note

Restart your mongod server.

Test your connection with the mongo shell.

Create a key and a CSR for BI Connector

Note

Sign the BI Connector certificate

Create a BI Connector PEM file

Start mongosqld

Test with the MySQL shell

Configure SSL for BI Connector

Update `mongod` configuration file

Restart your `mongod` server.

Test your connection with the `mongo` shell.

Start `mongosqld`

Update `mongod` configuration file

Restart your `mongod` server.

Test your connection with the `mongo` shell.

Start `mongosqld`