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

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:

3. Set up an environment variable for later use.

2

### Generate a Self-Signed Certificate Authority¶

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

2. Start the OpenSSL application.

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

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.

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:

4

### Sign the mongodb CSR¶

Enter the following command at the OpenSSL prompt:

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:

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

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.

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

7

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)

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:

10

### Sign the BI Connector certificate¶

Enter the following command at the OpenSSL prompt:

11

### Create a BI Connector PEM file¶

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

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.

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

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.

2

### Generate a Self-Signed Certificate Authority¶

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

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.

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.

4

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:

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

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.

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

7

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)

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.

10

11

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.

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

13

### Test with the MySQL shell¶

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