Navigation

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

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.

1

Create a Certificates Directory

  1. 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
    
  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:

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

Generate a Self-Signed Certificate Authority

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

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

    C:\OpenSSL\bin\openssl.exe
    
    Screenshot of OpenSSL command prompt
  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

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
4

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

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.

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

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:

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

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

Screenshot of DSN config screen

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

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.

    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

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.

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

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

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.

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.

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

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

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
11

Create a BI Connector PEM file

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.

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

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