How to configure MongoDB SSL Connection in SSIS

Introduction

MongoDB comes with SSL support which can secure your end to end communication. However setting up MongoDB SSL Connection may require some configuration on both sides (i.e. Server and Client side). In this post our goal is to secure your MongoDB Integration in SSIS.

Now lets look at steps to configure SSL in MongoDB.

Configure SSL Support in MongoDB Server

To configure MongoDB for SSL connectivity very first step is generate self signed certificate in *.pem format (This must be stored on server)

Download and Install OpenSSL

First step is to download and Install OpenSSL. Assuming you are running 64Bit Windows OS you can get Lite version of 64 Bit OpenSSL (around 3MB). If you install it using default settings then all binaries will be installed under C:\OpenSSL-Win64\bin\

Create Self Signed Certificate and *.pem file using OpenSSL

If you don’t have trust issue and you want to use self signed certificate rather than buying from 3rd party SSL Certificate Authority then you can create self signed certificate using OpenSSL Command line as below. Now lets look at how to create private key, certificate file and then finally *.pem file which is the only required file by MongoDB.

PEM file is the most common file format (container file format) which includes Certificate and Private Key in a single file.

Lets look at how to create PEM file using OpenSSL.

1. First run below command. When prompted enter “passphrase” ..  (in our case enter “test”).
   
   openssl genrsa -des3 -out server.key 2048

   1	openssl genrsa -des3 -out server.key 2048

2. Now run below command to generate private key (*.key file)
   
   openssl rsa -in server.key -out server.key

   1	openssl rsa -in server.key -out server.key

3. After private key is created run below command to create csr file (i.e. “Certificate Signing Request”). This file is needed to generate actual certificate (Self Signed or Signed by 3rd Party such as Veritas, RapidSSL). If you omit -sub argument from below then it may ask you few questions (e.g company name, department etc) but for testing purpose lets make it easy 🙂
   To enter full details or just provide CN=xxxx
   
   openssl req -sha256 -new -key server.key -out server.csr -subj "/C=US/ST=Georgia/L=Atlanta/O=ZappySys LLC/OU=Org/E=testuser@zappysys.com/CN=localhost" -- or -- openssl req -sha256 -new -key server.key -out server.csr -subj "/CN=localhost"

   1 2 3

   openssl req -sha256 -new -key server.key -out server.csr -subj "/C=US/ST=Georgia/L=Atlanta/O=ZappySys LLC/OU=Org/E=testuser@zappysys.com/CN=localhost" -- or -- openssl req -sha256 -new -key server.key -out server.csr -subj "/CN=localhost"

4. Now lets run below command to generate certificate file (*.crt). We are setting expiration to 365 days you can adjust as per your need.
   
   openssl x509 -req -sha256 -days 365 -in server.csr -signkey server.key -out server.crt

   1	openssl x509 -req -sha256 -days 365 -in server.csr -signkey server.key -out server.crt

5. Now at last step is to combine certificate (*.crt) and private key (*.key) files and generate pem file using below command. It will generate desired *.pem file format which can be used by MongoDB server (In Unix OS you can use cat command rather than type)
   
   type server.crt server.key > cert.pem

   1	type server.crt server.key > cert.pem

Once done your cert.pem file will look like below (Open and view in notepad)

Edit MongoDB Server config file (v3.0 or higher)

Now once we have *.pem file we can use it to launch MongoDB server instance. Before we do that one more step needed. We have to specify few parameters in mongodb config file.

1. Navigate to MongoDb installation directory (where mongod.exe found)
2. Create empty text file call it mongo-config.txt and enter following text (assuming cer.pem, mongo-config.txt and all mongod.exe are in the same folder). Password was test when we created pem file if you used different one then change below.
   mongo-config.txt
   
   net: bindIp: localhost ssl: mode: requireSSL PEMKeyFile: cert.pem PEMKeyPassword: test allowInvalidCertificates: true allowInvalidHostnames: true

   1 2 3 4 5 6 7 8

   net:       bindIp: localhost     ssl:         mode: requireSSL         PEMKeyFile: cert.pem         PEMKeyPassword: test         allowInvalidCertificates: true         allowInvalidHostnames: true

Start MongoDB Server using Config file

Once you create config file. We can start Mongodb instance using below command line to use new config file which has SSL mode enabled. That’s it your server is now accepting Secure connections using SSL.

Using SSL option in SSIS MongoDB Connection

Now lets look at steps to secure MongoDB connection in SSIS.

Prerequisites

Before we look into Step-By-Step section to secure MongoDB Connection in SSIS let’s make sure you met following requirements.

1. SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
3. ZappySys SSIS PowerPack installed. Click on the link to download FREE trial.
4. You have basic familiarity with MongoDB.

Using SSL in SSIS MongoDB Connection Manager

To enable SSL in SSIS open MongoDB connection manager and go to SSL Tab. Check Enable SSL Option like below. Once you do this and if your MongoDB server is properly configured to use SSL then your connection Test will be green and any traffic between you and your MongoDB server will be encrypted using SSL.

SSIS MongoDB Connection – SSL Settings

Using X509 Client Authentication in SSIS MongoDB Connection Manager

Now lets look at how to authenticate using X509 Client Certificate (X509) method. For this method you must use PFX file format for certificate (do not use PEM format). To create PFX file from PEM we created earlier you can run below command. When prompted use the same password you used before to generate private key.

To learn more about full process of how to configure MongoDB Server to use Client X509 Certificate Check this Article.

Let’s look at steps to configure SSIS MongoDB Connection manager to use X509 Client Certificate Mode.

1. Open ZappySys MongoDB Connection Manager UI
2. Enter User ID which you created in $external database (This UserID usually matches Subject of your Certificate). For example in our case UserID may appear like below. Leave password blank because its not used in x509 method.
   
   emailAddress=user@myemail.com,CN=127.0.0.1,OU=UNIT,O=DEMO,L=city,ST=test,C=AU

   1	emailAddress=user@myemail.com,CN=127.0.0.1,OU=UNIT,O=DEMO,L=city,ST=test,C=AU

   

   Configure SSIS MongoDB Connection for Client X509 Certificate (Set UserID of $external database)

3.  Now go to SSL Tab. Check SSL Option, You can check Ignore SSL Validation error (If its self signed certificate)
4. Select PFX file path and enter certificate password if needed. If you type Path then Subject Line appears automatically (You can use it as UserID field if you were unsure in Step#1 however we still recommend to make sure UserID is exact match based on original name your System Admin created in $external DB)
   

   Configure SSIS MongoDB Connection for Client X509 Certificate (Select PFX File / Specify Password)

5. Now go to Advanced Tab and enter Below Option. By default MongoDB Connection Manager uses userid/password mode. To use X509 Auth mode you must set option like below on the Advanced Options table.
   
   authMechanism=MONGODB-X509

   1	authMechanism=MONGODB-X509

   

   Configure SSIS MongoDB Connection for Client X509 Certificate (Specify authMechanism=MONGODB-X509)

6. Click Test connection to make sure its successful.