Docs Menu
Docs Home
/
BI Connector
/

Configure TLS for BI Connector

On this page

  • Prerequisites
  • Create and Test Self-Signed Certificates

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

Use these procedures for testing purposes only. Your production environment should use TLS certificates that a recognized certificate authority (CA) has issued.

  • A MongoDB user with sufficient permissions to run mongosqld.

  • A mongod instance which you can start and stop.

  • A mongosqld instance which you can start and stop.

  • OpenSSL

  • The MySQL shell

    Tip

    See also:

To ensure read availability for your MongoDB replica sets and sharded clusters while BI Connector enables TLS, use a rolling upgrade procedure. While the replica set primary upgrades, applications must wait until after failover and election cycle completes.

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 file contains an encryption key and a self-signed TLS certificate.

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

    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.

    Example

    This is an example configuration file.

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

    set OPENSSL_CONF=C:\OpenSSL\bin\openssl.cfg
2
  1. Change directory to C:\opt\certs.

    cd C:\opt\certs
  2. Start the OpenSSL application.

    C:\OpenSSL\bin\openssl.exe
    C:\Users\username>cd C:\opt\certs
    C:\Users\username>C:\OpenSSL\bin\openssl.exe
    OpenSSL>
  3. The following command generates and outputs a private key to mdbprivate.key.

    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.

    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

The following command generates a key for the mongod process TLS use and a CSR. For this step, the Common Name response must be the FQDN of the server where your mongod instance TLS such as www.example.com.

Enter the following command at the OpenSSL prompt:

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

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
5

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.

6

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

Option
Value
requireSSL
C:\opt\certs\mdb.pem
C:\opt\certs\mdbca.crt
C:\opt\certs\mdb.pem
x509

Example

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

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.

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

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

Option
Value
none
C:\opt\certs\mdbca.crt (file generated in step 2d)
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.

9

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
10

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
11

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

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

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

Option
Value
true
C:\opt\certs\mdb.pem
C:\opt\certs\mdbca.crt
requireSSL
C:\opt\certs\bi.pem
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
13

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

Screenshot of DSN config screen

Note

You don't need to enter a certificate path into the SSL Certificate field.

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

Screenshot of DSN config screen

Click the Test button to test your ODBC connection.

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

1

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
2
  1. The following command generates and outputs a private key to mdbprivate.key.

    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.

    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

The following command generates a key for the mongod process to use and a Certificate Signing Request (CSR). The Common Name response must be the Fully Qualified Domain Name (FQDN) of the host where your mongod instance runs such as 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.

openssl req -new -nodes -newkey rsa:2048 -keyout /opt/certs/mdb.key \
-out /opt/certs/mdb.csr
4
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

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.

6

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

Option
Value
requireSSL
/opt/certs/mdb.pem
/opt/certs/mdbca.crt
/opt/certs/mdb.pem
security.clusterAuthmode
x509

Example

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

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.

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

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

Option
Value
none
/opt/certs/mdbca.crt (file generated in step 2.2)
/opt/certs/mdb.pem (file generated in step 5)
mongo --tls --host <step3-common-name> \
--tlsCAFile /opt/certs/mdbca.crt \
--tlsPEMKeyFile /opt/certs/mdb.pem

Edit your options appropriately.

9

The Common Name entry for this CSR should match the FQDN of the host where you run mongosqld. To determine the FQDN of your host, 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
10
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
cat /opt/certs/bi.key /opt/certs/bi.crt > /opt/certs/bi.pem
12

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

Option
Value
true
/opt/certs/mdb.pem
/opt/certs/mdbca.crt
requireSSL
/opt/certs/bi.pem
/opt/certs/mdbca.crt

Example

The following 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
13

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